@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講的“用合適的函式索引來避免看似無法避免的全表掃描“。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7192724/viewspace-773276/，如需轉載，請註明出處，否則將追究法律責任。