Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1329784
  • 博文数量: 169
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 3800
  • 用 户 组: 普通用户
  • 注册时间: 2011-03-30 13:00
个人简介

About me:Oracle ACE pro,optimistic,passionate and harmonious. Focus on ORACLE,MySQL and other database programming,peformance tuning,db design, j2ee,Linux/AIX,Architecture tech,etc

文章分类

全部博文(169)

文章存档

2024年(24)

2023年(28)

2022年(43)

2020年(62)

2014年(3)

2013年(9)

分类: Oracle

2024-04-02 16:51:51

    背景:一般情况下,如果子查询里含有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
         



    
阅读(29048) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~