马上注册,结交更多数据大咖,获取更多知识干货,轻松玩转大数据
您需要 登录 才可以下载或查看,没有帐号?立即注册
x
Oracle性能优化之“少做事”(rebuild index)
导语:
“少做事”是《收获,不止ORACLE》中的优化思想的精髓,本文来将一下如何用“少做事”的思想来优化(rebuild index)
目的:
优化rebuild index
环境:
oracle 11.2.0.4 rac on redhat 6.3
SQL> desc doudou
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NUMBER
EDITION_NAME VARCHAR2(30)
SQL> select count(*) from doudou;
COUNT(*)
----------
262144
Elapsed: 00:00:00.02
SQL> select index_name,column_name,table_name from user_ind_columns where table_name='DOUDOU';
INDEX_NAME COLUMN_NAME TABLE_NAME
-------------------- ------------------------------ --------------------
DOU_IDX OBJECT_ID DOUDOU
Elapsed: 00:00:00.00
SQL> alter index dou_idx rebuild ;
Index altered.
Elapsed: 00:00:05.60
SQL> alter index dou_idx rebuild nologging;
Index altered.
Elapsed: 00:00:03.74
SQL> alter index dou_idx rebuild online nologging;
Index altered.
Elapsed: 00:00:02.69
正常rebuild index =>Elapsed: 00:00:05.60
nologging rebuild index =>Elapsed: 00:00:03.74 (减少产生的redolog,“少做事” )
online rebuild index =>Elapsed: 00:00:02.69(使用现有的索引资源,“少做事”)
总结:
少做事确实是优化一种思想。本实验就说明了“少做事”来优化 rebuild index。
|