本文测试用语句来自下面大佬文章《ORACLE CBO 的 SQL 自动转换(Cost Based Transformations)之五》
本文主要介绍由于11.2.0.4.190115中由于bug导致子查询不展开的情况。
https://blog.csdn.net/weixin_50513167/article/details/115694814
1. 基础知识之子查询展开
子查询展开(Subquery Unnesting)是优化器处理带子查询的目标SQL的一种优化手段,它是指优化器不再将目标SQL中的子查询当作一个独立的处理单元来单独执行,而是将该子查询转换为它自身和外部查询之间等价的表连接。
1.1 子查询展开两种情形
- 子查询拆开(即将该子查询中的表、视图从子查询中拿出来,然后和外部查询中的表、视图做表连接)
- 不拆开但是会把该子查询转换为一个内嵌视图(Inline View),然后再和外部查询中的表、视图做表连接,这种情况下,相当于优化器多了一条路径,CBO会考虑cost高低来决定是否将内嵌视图用来与外部查询表做关联,还是用外部表的数据来驱动内嵌视图走filter类型执行计划。
1.2 子查询是否展开判断
- 子查询如果展开,则会看到执行计划中会将子查询中基表拿出来与主查询中基表做关联或将子查询转换为内部视图与主查询中基表做关联。
- 子查询如果未展开,一般会在执行计划中最晚执行filter类型的子查询过滤。
1.3 _optimizer_unnest_corr_set_subq
该参数控制优化器在SQL语句包含子查询时,是否在满足子查询展开条件时,是否可以将子查询作为一个视图与主查询关联查询。
2. Oracle 11.2.0.4.190115 PSU下测试
Oracle版本:Oracle 11.2.0.4.190115 for Linux x86_64
Oracle参数: 优化器相关参数均为默认值,即相关_optimizer_unnest_corr_set_subq等参数默认均为true。
2.1 构建测试环境
测试用例均来自于https://blog.csdn.net/weixin_50513167/article/details/115694814
drop table t1 purge;
drop table t2 purge;
drop table drv purge;
create table t1(key, pad) as
select to_char(rownum*2-1), lpad(' ',50) from dual connect by level <= 100;
create table t2(key, pad) as
select to_char(rownum*2), lpad(' ',50) from dual connect by level <= 100;
create table drv(key, pad) as
select rownum, lpad(' ',500) from dual connect by level <= 5
union all
select -rownum, lpad(' ',500) from dual connect by level <= 1000;
exec dbms_stats.gather_table_stats(user, 'T1');
exec dbms_stats.gather_table_stats(user, 'T2');
exec dbms_stats.gather_table_stats(user, 'DRV');
2.2 执行测试SQL
# 下面参数默认即为true,此处设置为true是为了体现该参数确实为true
SQL > alter session set "_optimizer_unnest_corr_set_subq" = TRUE;
Session altered.
SQL > set autot on
SQL > select key from drv
2 where exists (
3 select key
4 from t1
5 where drv.key = to_number(t1.key)
6 union all
7 select key
8 from t2
9 where drv.key = to_number(t2.key)
10 );
KEY
----------------------------------------
1
2
3
4
5
Execution Plan
----------------------------------------------------------
Plan hash value: 1881039188
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 3040 (1)| 00:00:37 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | DRV | 1005 | 5025 | 23 (0)| 00:00:01 |
| 3 | UNION-ALL | | | | | |
|* 4 | TABLE ACCESS FULL| T1 | 1 | 4 | 3 (0)| 00:00:01 |