@dbsnake-用合适的函数索引来避免看似无法避免的全表扫描

昨天听了@dbsnake的SQL方法论，感觉比第一次要更有感觉，希望对实际工作能有帮助。

昨天讲到一处利用reverse函数建立索引，避免全表扫描的case，颇有感触，拿出来试一下。

SQL> create table rev (id number, name varchar2(5)); Table created.

SQL> select * from rev;         ID NAME ---------- -----          1 abc          2 bc          3 c

SQL> create index rev_idx0 on rev(name); Index created.

SQL> set autot on SQL> select id, name from rev where name like '%bc';         ID NAME ---------- -----          1 abc          2 bc Execution Plan ---------------------------------------------------------- Plan hash value: 3205185662

-------------------------------------------------------------------------- | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      |     2 |    34 |     3   (0)| 00:00:01 | |*  1 |  TABLE ACCESS FULL| REV  |     2 |    34 |     3   (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("NAME" LIKE '%bc') Note -----    - dynamic sampling used for this statement Statistics ----------------------------------------------------------           5  recursive calls           0  db block gets          16  consistent gets           0  physical reads           0  redo size         633  bytes sent via SQL*Net to client         492  bytes received via SQL*Net from client           2  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)           2  rows processed

这里建立了name的B树索引，但由于使用了%bc为条件，所以不会用索引，这里用了全表扫描。

如何能让%bc条件使用索引呢？这里讲到%bc不能用索引的原因是因为索引键值按照索引二进制的顺序排序，%在前就无法精确定位，因此无法使用索引。既然%在后面可以使用索引，那就想办法将%的条件放在后面组织。

SQL> create index rev_idx on rev(reverse(name)); Index created.

SQL> select id, name from rev where reverse(name) like reverse('%bc');         ID NAME ---------- -----          2 bc          1 abc Execution Plan ---------------------------------------------------------- Plan hash value: 2418054352 --------------------------------------------------------------------------------------- | Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     | --------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |         |     2 |    34 |     2   (0)| 00:00:01 | |   1 |  TABLE ACCESS BY INDEX ROWID| REV     |     2 |    34 |     2   (0)| 00:00:01 | |*  2 |   INDEX RANGE SCAN          | REV_IDX |     1 |       |     1   (0)| 00:00:01 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    2 - access(REVERSE("NAME") LIKE 'cb%')        filter(REVERSE("NAME") LIKE 'cb%') Note -----    - dynamic sampling used for this statement Statistics ----------------------------------------------------------          28  recursive calls           0  db block gets          14  consistent gets           0  physical reads           0  redo size         633  bytes sent via SQL*Net to client         492  bytes received via SQL*Net from client           2  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)           2  rows processed

这里用了reverse函数，相当于方向匹配字符串bc，这样就将前面的%放到了后面。从执行计划看cost从3变为2。

注：

这里可以看到无论哪次执行，物理读都是0，原因我觉得就是第一次执行过一个select * from rev;，因为数据量比较小，第一次select之后，记录就从data file缓存到buffer cache，即使根据LRU算法，负载不是太大的DB，很快的时间内这些数据还可能在其中，没被age out，所以再次执行SQL时，就可能物理读是0。

总结：

以上的示例就是@dbsnake讲的“用合适的函数索引来避免看似无法避免的全表扫描“。