由于主键产生的默认索引会增大索引的竞争，此前系统也出现了大量的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