将filter操作的sql改写为可以走hash join的一则例子-凯发app官方网站

博客访问： 1028355
博文数量： 145
博客积分： 0
博客等级：民兵
技术积分： 3510
用户组：普通用户
注册时间： 2011-03-30 13:00

个人简介

about me:oracle ace,optimistic,passionate and harmonious. focus on oracle programming,peformance tuning,db design, j2ee,linux/aix,web2.0 tech,etc

文章分类

全部博文（145）

cbo（40）
tuning&performan（31）
concepts&archite（4）
troubleshooting（5）
new feature（6）
sql（48）
pl/sql（8）
miscellaneous（3）
未分配的博文（0）

文章存档

2023年（28）

2022年（43）

2020年（62）

2014年（3）

2013年（9）

我的朋友

相关博文

将filter操作的sql改写为可以走hash join的一则例子

分类： oracle

2023-05-04 17:14:44

如下所示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优化器限制的语句，必须想办法绕过这个限制，可以从业务设计、语句改写等方面着手解决。

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

上一篇：filter operation中子查询的执行次数问题分析

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

| | | | |

感谢所有关心和支持过chinaunix的朋友们