reverse index的like測試和dbms_stats具體引數意義

由於主鍵產生的預設索引會增大索引的競爭，此前系統也出現了大量的db file sequential read等待事件，不過此事件大量出現並不能說明系統存在效能瓶頸，很有可能是高效利用索引的情況，而是否應該在建立主鍵索引時利用反轉索引來平衡索引的IO競爭了，這不是能很簡單的說明的，利用反轉索引雖然能打散資料分佈不過無疑增加了IO消耗，不過下面測試的是關於反轉索引是否能利用like ‘TEST002%’這種謂詞查詢的。

SQL> create table test002 as select distinct object_name,object_id,object_type,created,status from dba_objects;

Table created

SQL> create index ind_test002_reveser_objname on test002(object_name) reverse;

Index created

SQL> insert into test002 select * from test002;

49750 rows inserted

SQL> insert into test002 select * from test002;

SQL>

99500 rows inserted

SQL> commit;

Commit complete

SQL> execute dbms_stats.gather_table_stats(ownname=>'XIAOYU',tabname=>'TEST002',estimate_percent=>30,degree=>2);

PL/SQL procedure successfully completed

SQL> explain plan for select * from test002 where object_name like '%TEST002';

Explained

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 2585740653

-----------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

-----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 9994 | 663K| 469 (3)| 00:00:06 |

|* 1 | TABLE ACCESS FULL| TEST002 | 9994 | 663K| 469 (3)| 00:00:06 |

-----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("OBJECT_NAME" LIKE '%TEST002')

13 rows selected

SQL> explain plan for select /*+index(test002)*/* from test002 where object_name like '%TEST002';

Explained

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 1431897033

--------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Byte

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 9994 | 66

| 1 | TABLE ACCESS BY INDEX ROWID| TEST002 | 9994 | 66

|* 2 | INDEX FULL SCAN | IND_TEST002_REVESER_OBJNAME | 9950 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("OBJECT_NAME" LIKE '%TEST002')

14 rows selected

SQL> explain plan for select * from test002 where object_name like 'TEST002%';

Explained

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 2585740653

-----------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

-----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 9 | 612 | 469 (3)| 00:00:06 |

|* 1 | TABLE ACCESS FULL| TEST002 | 9 | 612 | 469 (3)| 00:00:06 |

-----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("OBJECT_NAME" LIKE 'TEST002%')

13 rows selected

SQL> explain plan for select * from test002 where object_name like 'TEST002';

Explained

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 8204046

--------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Byte

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 9 | 61

| 1 | TABLE ACCESS BY INDEX ROWID| TEST002 | 9 | 61

|* 2 | INDEX RANGE SCAN | IND_TEST002_REVESER_OBJNAME | 9 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("OBJECT_NAME" LIKE 'TEST002')

14 rows selected

正如所說反轉索引並不能很好的利用like欄位，甚至普通的like ‘TEST002%’都不能走索引，而如果真有的辦法就是利用函式索引建立一個反轉的函式，然後其實還是要用like ‘002TEXT%’形式，大家可以測試看看執行計劃。

Create index ind_test002_objname on test002(reverse(object_name));

select * from test002 where reverse(object_name) like '200TXET%'

一般都會用到dbms_stats.gather_table_stats收集統計資訊，不過其中各項的含義還是需要注意的。

SQL> desc dbms_stats.create_stat_table;

Parameter Type Mode Default?

---------------- -------- ---- --------

OWNNAME VARCHAR2 IN

STATTAB VARCHAR2 IN

TBLSPACE VARCHAR2 IN Y

GLOBAL_TEMPORARY BOOLEAN IN Y

SQL> execute dbms_stats.create_stat_table('XIAOYU','STAT_TABLE');

PL/SQL procedure successfully completed

建立一個統計資訊的收集表，可以把後續的統計資訊利用dbms_stats.export_table_stats傳遞到這張表中，也就是stattab制定的表名

SQL> execute dbms_stats.gather_table_stats(ownname=>'XIAOYU',tabname=>'TEST002',estimate_percent=>30,degree=>2,cascade=>TRUE,method_opt=>'for all indexed columns');

PL/SQL procedure successfully completed

Cascade預設為false，true是級聯索引一起統計分析，estimate_percent是取樣的百分比，degree是並行度，method_opt:決定histograms資訊是怎樣被統計的，有for all columns包含所有列的histograms，for all indexed columns統計所有indexed列histograms，for all hidden columns統計看不見列的histograms

SQL> desc dbms_stats.export_table_stats;

Parameter Type Mode Default?

--------- -------- ---- --------

OWNNAME VARCHAR2 IN

TABNAME VARCHAR2 IN

PARTNAME VARCHAR2 IN Y

STATTAB VARCHAR2 IN

STATID VARCHAR2 IN Y

CASCADE BOOLEAN IN Y

STATOWN VARCHAR2 IN Y

SQL> execute dbms_stats.export_table_stats(ownname=>'XIAOYU',TABNAME=>'TEST002',STATTAB=>'STAT_TABLE');

PL/SQL procedure successfully completed

SQL> execute dbms_stats.delete_table_stats(ownname=>'XIAOYU',tabname=>'TEST002');

PL/SQL procedure successfully completed

[@more@]