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的优势太大了）

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10856805/viewspace-1016364/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10856805/viewspace-1016364/