参考资料:
本系列博客主要参考资料有CUUG冉乃纲老师数据库教学笔记,《SQL优化核心思想》(罗炳森,黄超,钟侥著),《PostgreSQL技术内幕:查询优化深度探索》(张树杰著),排名不分先后。
1 半连接和内连接转换
1.1 环境准备
create table tests1 as select * from dba_objects;
create table tests2 as select * from dba_objects;
1.2 问题SQL
EXPLAIN PLAN FOR SELECT DISTINCT A.OWNER
FROM tests1 A
WHERE A.OWNER IN (SELECT B.OWNER FROM tests2 B);
这条SQL在Oracle 11g环境下,如果没有收集统计信息,在我的电脑上是跑不出来结果的,是一条问题SQL。
1.3 原因分析
SQL很简单,为什么不出结果呢?首先我们看执行计划,
那么问题来了,我们的SQL是in查询,理论上是半连接,但是这里却是hash连接后去重复,优化器为什么这么做,我们稍后讨论,现在,为了解决问题,我们先把执行计划按照我们的想法调整一下。
第一招,我们加hint hash_sj,
EXPLAIN PLAN FOR SELECT DISTINCT A.OWNER
FROM tests1 A
WHERE A.OWNER IN (SELECT /*+ HASH_SJ(B)*/ B.OWNER FROM tests2 B);
结果是,并没有什么用。
第二招,SQL改写
WITH TMP AS
(SELECT A.OWNER
FROM tests1 A