使用反转索引提高SQL的执行效率 ? 半瓶

最近优化SQL的执行计划，使用到了反转索引，记录一下。以下是在测试环境中的结果，生产环境建立索引等操作要小心点。先看一个常规的索引：

一般我们先看看这个表的数据量：

SQL> select bytes/1024/1024 from dba_segments whereowner='ERP' and segment_name=upper('invoice');

BYTES/1024/1024
---------------
456

执行SQL查看执行计划：

SQL> set linesize 120
SQL> set autotrace on
SQL> select sbcode from invoice where paicode = '000000995430';

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 2742044924

-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)|Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |     1 |     8 | 12980   (2)|00:02:36 |
|*  1 |  TABLE ACCESS FULL| INVOICE |     1 |     8 | 12980   (2)|00:02:36 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("PAICODE"='000000995430')

Statistics
----------------------------------------------------------
1989  recursive calls
0  db block gets
58447  consistent gets
58039  physical reads
0  redo size
323  bytes sent via SQL*Net to client
481  bytes received via SQL*Net from client
1  SQL*Net roundtrips to/from client
49  sorts (memory)
0  sorts (disk)
0  rows processed

很明显走了全表扫描，下面建立索引：

SQL> create index idx_invoice_paicode oninvoice(paicode) tablespace erp_index;

Index created.

然后再看执行计划：

SQL> select sbcode from invoice where paicode ='000000995430';

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 3585586676

---------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                | Rows  |Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                     |     1 |     8|     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| INVOICE             |     1 |     8|     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_INVOICE_PAICODE |     1|       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("PAICODE"='000000995430')

Statistics
----------------------------------------------------------
24  recursive calls
0  db block gets
5  consistent gets
1  physical reads
0  redo size
323  bytes sent via SQL*Net to client
481  bytes received via SQL*Net from client
1  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
0  rows processed

效果不言而喻，除了时间因素，逻辑读和物理读下降都很明显。

下面看一个反转索引的例子，一般查询条件是like '%XXX'的时候我们考虑使用反转索引，先看下数据量：

SQL> select bytes/1024/1024 from dba_segments whereowner='ERP' and segment_name=upper('ratifytask');

BYTES/1024/1024
---------------
4768

执行SQL，查看执行计划：

SQL> select rtask from ratifytask where rtask like'%gtrafsheet.0000000000125193';

RTASK
----------------------------------------------------------------
203041:gtrafsheet.0000000000125193
203041:gtrafsheet.0000000000125193
203041:gtrafsheet.0000000000125193
203041:gtrafsheet.0000000000125193
203041:gtrafsheet.0000000000125193
203041:gtrafsheet.0000000000125193
203041:gtrafsheet.0000000000125193

7 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3455136816

-----------------------------------------------------------------------------------------
| Id  | Operation            | Name             | Rows  | Bytes | Cost(%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                  |   202K|  6929K| 5778   (2)| 00:01:10 |
|*  1 |  INDEX FAST FULL SCAN| RATIFYTASK_RTASK |   202K|  6929K| 5778   (2)| 00:01:10 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("RTASK" LIKE '%gtrafsheet.0000000000125193')

Statistics
----------------------------------------------------------
524  recursive calls
0  db block gets
26179  consistent gets
25966  physical reads
0  redo size
831  bytes sent via SQL*Net to client
492  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
10  sorts (memory)
0  sorts (disk)
7  rows processed

建立反转索引：

SQL> create index idx_ratifytask_rtask_reverse onratifytask(reverse(rtask)) tablespace erp_index;

Index created.

然后再查会发现执行计划并没有改变，这是因为我们首先要告诉CBO更新统计信息：

SQL> execdbms_stats.gather_table_stats('erp','RATIFYTASK',cascade=>true);

PL/SQL procedure successfully completed.

这是个费时的操作，生产环境繁忙时段千万别这么干，然后再看执行计划：

SQL> select rtask from ratifytask where reverse(rtask)like reverse('%gtrafsheet.0000000000125193');

RTASK
----------------------------------------------------------------
203041:gtrafsheet.0000000000125193
203041:gtrafsheet.0000000000125193
203041:gtrafsheet.0000000000125193
203041:gtrafsheet.0000000000125193
203041:gtrafsheet.0000000000125193
203041:gtrafsheet.0000000000125193
203041:gtrafsheet.0000000000125193

7 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1377131512

------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                         |Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                              |    7 |   245 |    10   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| RATIFYTASK                   |    7 |   245 |    10   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_RATIFYTASK_RTASK_REVERSE |    7 |       |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access(REVERSE("RTASK") LIKE '3915210000000000.teehsfartg%')
filter(REVERSE("RTASK") LIKE '3915210000000000.teehsfartg%')

Statistics
----------------------------------------------------------
281  recursive calls
0  db block gets
121  consistent gets
11  physical reads
0  redo size
831  bytes sent via SQL*Net to client
492  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
26  sorts (memory)
0  sorts (disk)
7  rows processed

效果十分明显，达到目的。