Oracle SQL Like 的最佳化

這個問題首先是在TAOBAO DBA的BLOG上看到丹臣寫的關於Like和INSTR的效能問題。不過他只是給出了結果。我對這個函式效能感到有趣，之前一直沒有關注過，遂自己詳細測試了下。

[@more@]

Oracle 9208:

SQL> select count(*) from item;

COUNT(*)

----------

2781806

SQL> select count(*) from item where item like '%A0';

COUNT(*)

----------

9036

Elapsed: 00:00:04.03

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1134 Card=1 Bytes=16

)

1 0 SORT (AGGREGATE)

2 1 INDEX (FAST FULL SCAN) OF 'PK_ITEM' (UNIQUE) (Cost=1134

Card=138698 Bytes=2219168)

Statistics

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

0 recursive calls

0 db block gets

10369 consistent gets

0 physical reads

0 redo size

519 bytes sent via SQL*Net to client

656 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

可以看到走了FFS, 10396 Consistent Gets, Elapsed tail=4.03

再看SUBSTR

SQL> select count(*) from item where substr(item,-2)='A0';

COUNT(*)

----------

9036

Elapsed: 00:00:00.84

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1134 Card=1 Bytes=16

)

1 0 SORT (AGGREGATE)

2 1 INDEX (FAST FULL SCAN) OF 'PK_ITEM' (UNIQUE) (Cost=1134

Card=27740 Bytes=443840)

Statistics

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

0 recursive calls

0 db block gets

10369 consistent gets

0 physical reads

0 redo size

519 bytes sent via SQL*Net to client

656 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

可以看到PLAN一樣，Consistent Gets一樣，Elapsed tail=0.84

SUBSTR的耗時只有LIKE的21%左右，鑑於PLAN和Consistent Gets一致，所以可以認為是SUBSTR的CPU Cost要小於LIKE。

NOT LIKE的情況和LIKE相似。

INSTR的情況也如SUBSTR

確實如丹臣所言，Oracle的函式有相當的最佳化。

但9i的Optimizer在計算Cost的時候是以IO為準，那麼在以CPU為準的10G上呢，Cost會有什麼差別？

下面在10203上測試

SQL> select count(*) from item where item like '%A0';

COUNT(*)

----------

9104

Elapsed: 00:00:03.03

Execution Plan

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

Plan hash value: 642095792

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

-

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

|

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

-

| 0 | SELECT STATEMENT | | 1 | 15 | 2211 (3)| 00:00:27

|

| 1 | SORT AGGREGATE | | 1 | 15 | |

|

|* 2 | INDEX FAST FULL SCAN| PK_ITEM | 139K| 2037K| 2211 (3)| 00:00:27

|

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

-

Predicate Information (identified by operation id):

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

2 - filter("ITEM" LIKE '%A0')

Statistics

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

0 recursive calls

0 db block gets

10117 consistent gets

0 physical reads

0 redo size

516 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)

1 rows processed

SQL> select count(*) from item where substr(item,-2)='A0';

COUNT(*)

----------

9104

Elapsed: 00:00:01.24

Execution Plan

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

Plan hash value: 642095792

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

-

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

|

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

-

| 0 | SELECT STATEMENT | | 1 | 15 | 2220 (3)| 00:00:27

|

| 1 | SORT AGGREGATE | | 1 | 15 | |

|

|* 2 | INDEX FAST FULL SCAN| PK_ITEM | 27819 | 407K| 2220 (3)| 00:00:27

|

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

-

Predicate Information (identified by operation id):

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

2 - filter(SUBSTR("ITEM",-2)='A0')

Statistics

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

0 recursive calls

0 db block gets

10117 consistent gets

0 physical reads

0 redo size

516 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)

1 rows processed

結果是SUBSTR-2220-elapsed 1.24，Like-2211-elapsed 3.03。

顯然SUBSTR在CPU佔用上要高那麼一點， 但是其相對於Like僅 41% 的耗時，使得它完全可以取代Like在查詢以XX結尾的SQL中的地位。

BTW，我在一臺Idle Server上的測試表明，使用SUBSTR不僅在耗時上縮短，而且CPU使用率也較LIKE低(9.5%/12.5%)

類似的測試表明INSTR相對LIKE ‘%XX%’的優勢。

（函式取代LIKE ‘XX%’就別想了，一個Index Range Scan相對FFS的優勢太大了）