Oracle效能優化之“少做事”（rebuild index）

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。