将OR子查询改写为JOIN等值查询，执行计划从FILTER转为HASH JOIN，效率起飞

    背景：一般情况下，如果子查询里含有OR或外面的WHERE条件含有OR，如果走不了disjunctive subquery unnest,则会走FILTER,效率较低，
对符合条件的disjunctive subquery,ORACLE CBO能够进行unnest,由隐含参数_optimizer_unnest_disjunctive_subq控制。
本次讨论的就是将OR子查询改写为JOIN，构造等值条件，让原来走FILTER的执行计划走HASH JOIN，大幅度提高效率。


示例建表语句如下：

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(level,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(10000000000, 20000000000 ))) phone_no,
trunc(dbms_random.value(0, 30 )) ext,
lpad(level,10) v1,
rpad('x',100) padding
from
dual
connect by level <= 1000000;

--创建索引
create index idx_t2 on t2(phone_no);

--收集统计信息
exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user,tabname=>'t1',estimate_percent=>10,method_opt=>'for all columns size auto',no_invalidate=>false,cascade=>true,degree => 10);
exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user,tabname=>'t2',estimate_percent=>10,method_opt=>'for all columns size auto',no_invalidate=>false,cascade=>true,degree => 10);

数据量：
select count(*) from t1;


  COUNT(*)
----------
   1000000
   
select count(*) from t2;


  COUNT(*)
----------
   1000000


统计信息：
TABLE_NAME                       NUM_ROWS SAMPLE_SIZE LAST_ANALYZED
------------------------------ ---------- ----------- -------------------
T1                                1003550      100355 2023-08-18 17:13:11
T2                                 996380       99638 2023-08-18 17:13:11

原SQL：
主表t1,然后t2是多个or子查询连用，这种情况下是走不了subquery unnest的，执行计划只能走FILTER：

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

执行计划如下，返回95行，耗时10.46s，可以可以看到执行计划走FILTER,类似NESTED LOOPS，主表T1估算返回100w行+，所以子查询被驱动百万次，性能差：

执行计划如下所示：

95 rows selected.


Elapsed: 00:00:10.46


Execution Plan
----------------------------------------------------------
Plan hash value: 1650104623


-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     1 |   127 |  3001K  (1)| 10:00:20 |
|*  1 |  FILTER            |        |       |       |            |          |
|   2 |   TABLE ACCESS FULL| T1     |  1003K|   121M|  5086   (1)| 00:01:02 |
|*  3 |   INDEX RANGE SCAN | IDX_T2 |     1 |    12 |     3   (0)| 00:00:01 |
|*  4 |   INDEX RANGE SCAN | IDX_T2 |     1 |    12 |     3   (0)| 00:00:01 |
|*  5 |   INDEX RANGE SCAN | IDX_T2 |     1 |    12 |     3   (0)| 00:00:01 |
|*  6 |   INDEX RANGE SCAN | IDX_T2 |     1 |    12 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------


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


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




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
   12027453  consistent gets
      21732  physical reads
          0  redo size
       5001  bytes sent via SQL*Net to client
        586  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         95  rows processed
         

    

         
分析与优化：


主要条件用OR子查询导致不能UNNEST，按照条件的结构特点，子查询的条件有一定规律，
比如：
WHERE SUBSTR(t1.phone_no,1,8) IN
(SELECT t2.phone_no FROM t2 WHERE LENGTH(t2.phone_no)=8)
t1.phone截取前8个字符与t2.phone一样。

利用这个规律可以构造hash join，注意构造HASH JOIN条件注意{BANNED}最佳好重复值较少，否则碰到hash碰撞，性能低。SQL改写如下：

SELECT distinct 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||'%'
--构造HASH JOIN关键，{BANNED}最佳好重复值小，不然有HASH碰撞
AND SUBSTR(t1.phone_no,1,8)=SUBSTR(t22.phone_no,1,8) 
--下面的是防止其他不满足9-11的条件过滤，这个类似原来OR条件
AND SUBSTR(t1.phone_no,1,length(t22.phone_no))=t22.phone_no;

去掉子查询后，走HASH JOIN，执行时间从10.46s降低到0.88s,提升十倍以上：

95 rows selected.


Elapsed: 00:00:00.88


Execution Plan
----------------------------------------------------------
Plan hash value: 555290710


---------------------------------------------------------------------------------
| Id  | Operation              | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |        |     1 |   139 |  6085   (3)| 00:01:14 |
|   1 |  HASH UNIQUE           |        |     1 |   139 |  6085   (3)| 00:01:14 |
|*  2 |   HASH JOIN            |        |     1 |   139 |  6084   (3)| 00:01:14 |
|*  3 |    INDEX FAST FULL SCAN| IDX_T2 |  2491 | 29892 |   882   (2)| 00:00:11 |
|   4 |    TABLE ACCESS FULL   | T1     |  1003K|   121M|  5082   (1)| 00:01:01 |
---------------------------------------------------------------------------------


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


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




Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      21748  consistent gets
      18519  physical reads
          0  redo size
       4993  bytes sent via SQL*Net to client
        586  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         95  rows processed