oracle view rebuild,Oracle 优化之SQL性能优化之“少做事”（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。