将or子查询改写为join等值查询，执行计划从filter转为hash join，效率起飞-凯发app官方网站

凯发app官方网站-凯发k8官网下载客户端中心 | | 凯发app官方网站-凯发k8官网下载客户端中心

dingjun123的blog

凯发app官方网站首页　| 　博文目录　| 　关于我

dingjun123

  • 博客访问： 1156150
  • 博文数量： 166
  • 博客积分： 0
  • 博客等级： 民兵
  • 技术积分： 3760
  • 用 户 组： 普通用户
  • 注册时间： 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

文章分类

全部博文（166）

文章存档

2024年（21）

2023年（28）

2022年（43）

2020年（62）

2014年（3）

2013年（9）

我的朋友
最近访客
推荐博文
相关博文
  • ·
  • ·
  • ·
  • ·
  • ·
  • ·
  • ·
  • ·
  • ·
  • ·
将or子查询改写为join等值查询，执行计划从filter转为hash join，效率起飞

分类： 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
         

    
阅读(17929) | 评论(0) | 转发(0) |
0

上一篇：自定义函数索引deterministic减少函数调用次数

下一篇：oracle not in:null aware anti join注意点及其改写

给主人留下些什么吧！~~
| | | | |

凯发app官方网站 copyright 2001-2010 chinaunix.net all rights reserved 北京皓辰网域网络信息技术有限公司. 凯发k8官网下载客户端中心的版权所有

感谢所有关心和支持过chinaunix的朋友们
")); function link(t){ var href= $(t).attr('href'); href ="?url=" encodeuricomponent(location.href); $(t).attr('href',href); //setcookie("returnouturl", location.href, 60, "/"); }
网站地图