将FILTER操作的SQL改写为可以走HASH JOIN的一则例子

如下所示t1,t2两张表通过phone_no条件关联，t1和t2均有千万条。

DROP TABLE t1;
create table t1 as
select
to_char(trunc(dbms_random.value(10000000000, 20000000000 ))) phone_no,
trunc(dbms_random.value(0, 30 )) ext,
lpad(rownum,10) v1,
rpad('x',100) padding
from
dual
connect by level <= 1000000;


DROP TABLE t2;
create table t2 as
select
to_char(trunc(dbms_random.value(10000000, 20000000000 ))) phone_no,
trunc(dbms_random.value(0, 30 )) ext,
lpad(rownum,10) v1,
rpad('x',100) padding
from
dual
connect by level <= 1000000;


exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'t1',no_invalidate=>false);
exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'t2',no_invalidate=>false);

 
原始语句如下：

SELECT phone_no,ext,v1,padding
FROM t1
WHERE SUBSTR(t1.phone_no,1,8) IN
(SELECT t2.phone_no FROM t2 WHERE LENGTH(t2.phone_no)=8)
OR 
SUBSTR(t1.phone_no,1,9) IN
(SELECT t2.phone_no FROM t2 WHERE LENGTH(t2.phone_no)=9)
OR
SUBSTR(t1.phone_no,1,10) IN
(SELECT t2.phone_no FROM t2 WHERE LENGTH(t2.phone_no)=10)
OR
SUBSTR(t1.phone_no,1,11) IN
(SELECT t2.phone_no FROM t2 WHERE LENGTH(t2.phone_no)=11);

 
这种SQL有一堆OR和子查询，CBO无法进行查询转换，只能走FILTER,显然很慢，通过执行计划可以看出：

Execution Plan
----------------------------------------------------------
Plan hash value: 2055931425


---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   127 |  4970M  (1)|999:59:59 |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| T1   |  1000K|   121M|  5086   (1)| 00:01:02 |
|*  3 |   TABLE ACCESS FULL| T2   |     1 |    12 |  4995   (1)| 00:01:00 |
|*  4 |   TABLE ACCESS FULL| T2   |     1 |    12 |  4995   (1)| 00:01:00 |
|*  5 |   TABLE ACCESS FULL| T2   |     1 |    12 |  4995   (1)| 00:01:00 |
|*  6 |   TABLE ACCESS FULL| T2   |     1 |    12 |  4995   (1)| 00:01:00 |
---------------------------------------------------------------------------


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


   1 - filter( EXISTS (SELECT 0 FROM "T2" "T2" WHERE
              LENGTH("T2"."PHONE_NO")=8 AND "T2"."PHONE_NO"=SUBSTR(:B1,1,8)) OR
              EXISTS (SELECT 0 FROM "T2" "T2" WHERE LENGTH("T2"."PHONE_NO")=9 AND
              "T2"."PHONE_NO"=SUBSTR(:B2,1,9)) OR  EXISTS (SELECT 0 FROM "T2" "T2"
              WHERE LENGTH("T2"."PHONE_NO")=10 AND "T2"."PHONE_NO"=SUBSTR(:B3,1,10))
              OR  EXISTS (SELECT 0 FROM "T2" "T2" WHERE LENGTH("T2"."PHONE_NO")=11
              AND "T2"."PHONE_NO"=SUBSTR(:B4,1,11)))
   3 - filter(LENGTH("T2"."PHONE_NO")=8 AND
              "T2"."PHONE_NO"=SUBSTR(:B1,1,8))
   4 - filter(LENGTH("T2"."PHONE_NO")=9 AND
              "T2"."PHONE_NO"=SUBSTR(:B1,1,9))
   5 - filter(LENGTH("T2"."PHONE_NO")=10 AND
              "T2"."PHONE_NO"=SUBSTR(:B1,1,10))
   6 - filter(LENGTH("T2"."PHONE_NO")=11 AND
              "T2"."PHONE_NO"=SUBSTR(:B1,1,11))

 
针对这样的语句，只能通过改写成语义等价的SQL，绕过CBO限制，让其不走FILTER,可以走NESTED LOOPS,HASH JOIN,SORT MERGE JOIN等，要改写首先要了解这条语句的特点和含义：
通过子查询条件判断{BANNED}最佳短关联条件是前8位，并且t1.phone_no包含t2.phone_no
{BANNED}最佳终改写如下：

SELECT t1.phone_no,t1.ext,t1.v1,t1.padding
FROM t1,(SELECT DISTINCT phone_no FROM t2 WHERE LENGTH(t2.phone_no) BETWEEN 8 AND 11) t22
WHERE t1.phone_no LIKE t22.phone_no||'%'
AND SUBSTR(t1.phone_no,1,8)=SUBSTR(t22.phone_no,1,8);

 
执行计划可以走HASH JOIN，效率得到巨大提升：

Execution Plan
----------------------------------------------------------
Plan hash value: 1674805607


----------------------------------------------------------------------------------
| Id  | Operation            | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           |    50 |  4950 | 10198   (2)| 00:02:03 |
|   1 |  VIEW                | VM_NWVW_1 |    50 |  4950 | 10198   (2)| 00:02:03 |
|   2 |   HASH UNIQUE        |           |    50 |  6950 | 10198   (2)| 00:02:03 |
|*  3 |    HASH JOIN         |           |    50 |  6950 | 10197   (2)| 00:02:03 |
|*  4 |     TABLE ACCESS FULL| T2        |  2500 | 30000 |  4995   (1)| 00:01:00 |
|   5 |     TABLE ACCESS FULL| T1        |  1000K|   121M|  5082   (1)| 00:01:01 |
----------------------------------------------------------------------------------


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

   3 - access(SUBSTR("T1"."PHONE_NO",1,8)=SUBSTR("PHONE_NO",1,8))
       filter("T1"."PHONE_NO" LIKE "PHONE_NO"||'%')
   4 - filter(LENGTH("T2"."PHONE_NO")>=8 AND LENGTH("T2"."PHONE_NO")<=11)

 
总结：对于遇到CBO优化器限制的语句，必须想办法绕过这个限制，可以从业务设计、语句改写等方面着手解决。