性能优化之查询转换 - 子查询类

子查询，是SQL中常见的一种写法。对于优化器来说，子查询是较难优化的部分。Oracle提供了多种方式，对子查询进行查询转换。

一、子查询推进

子查询推进（又称子查询推入）是指优化器将子查询提前进行评估，使得优化器可以更早地介入优化以获得更优质的执行计划。这个技术可以通过提示PUSH_SUBQ/NO_PUSH_SUBQ控制。下面通过一个示例看看结果。

SQL> create table t_users as select * from dba_users;
//表已创建
SQL> create index idx_user_created on t_users(created);
//索引已创建
SQL> create table t_objects as select * from dba_objects;
//表已创建

执行以下语句：

SQL> select /*+ no_push_subq(@inv)*/ /*hf1*/ *
from t_objects u
where created >
(select /*+ qb_name(inv)*/ max(created)
from t_users );

执行计划如下：

注：在这个语句中，我们通过提示强制不使用子查询推进技术。由执行计划可见，执行是按照T_OBJECTS和T_USRES进行的一个索引的嵌套循环。

使用子查询推进：

SQL> select /*hf2*/ *
from t_objects u
where created >
( select /*+ qb_name(inv)*/ max(created)
 from t_users );

执行计划如下：

二、子查询嵌套、展开

子查询解嵌套是指优化器将子查询展开，和外部的查询进行关联、合并，从而得到更优的执行计划。可以通过UNNEST/NO_UNNEST提示控制是否进行解嵌套。采用这种技术通常可以提高执行效率，原因是如果不解嵌套，子查询往往是最后执行的，作为FILTER条件来过滤外部查询；而一旦展开，优化器就可以选择表关联等更高效的执行方式，以提高效率。下面通过几个示例说明各种解嵌套的形式。

先看第一个示例：

SQL> create table t_tables as select * from dba_tables;
Table created.
SQL> select * from t_objects o where exists(select /*+ qb_name(inv)*/ 1 from t_tables t where t.owner=o.owner and t.table_name=o.object_name);

执行计划如下：

在这个示例中，对EXISTS的子查询进行了解嵌套，然后选择了半连接（SEMI JOIN）的关联方式。

再来看一个示例。

SQL> select * from t_objects o where not exists (select /*+ qb_name(inv)*/ 1 from t_tables t where t.owner=o.owner and t.table_name=o.object_name);

执行计划如下：

在这个示例中，对NOT EXISTS的子查询进行了解嵌套，然后选择了反连接（ANTI JOIN）的关联方式。

三、子查询分解

子查询分解是由WITH创建的复杂查询语句并存储在临时表中，可按照与一般表相同的方式使用该临时表的功能。这种方式可以把一个复杂的查询分成很多简单的部分，并让优化器去决定是产生中间数据集还是构建该查询复杂的扩展形式并对其进行优化。这种方式的优点在于，使用WITH子句的子查询在复杂查询语句中只需要执行一次，但结果可以在同一个查询语句中被多次使用。缺点在于，这种方式不允许语句变形，所以无效的情况较多。

下面看一个示例。

SQL> with user_obj as 
    (select owner,count(*) cnt
        from t_objects group by owner)
select u.user_id,u.username,o.cnt
from t_users u,user_obj o
where u.username=o.owner;

上述过程并没有生成临时表，可通过一个提示materialize强制优化器创建临时表。

SQL> with user_obj as 
(select --+ materialize 
owner,count(*) cnt
from t_objects group by owner )
select u.user_id,u.username,o.cnt
from t_users u,user_obj o
where u.username=o.owner;

执行计划如下：

引入了materialize提示后，由ID=2步骤可见，系统生成了一个临时表SYS_TEMP_XXX，并由这个表在后面与T_USERS进行了关联查询。

四、子查询合并

在语义等价的前提下，如果多个子查询产生的结果集相同，则优化器可以使用这种技术将多个子查询合并为一个子查询。这样的好处在于减少多次扫描产生的开销。可以通过NO_COALESCE_SQ/COALESCE_SQ提示来控制。下面看个示例：

select /*+ qb_name(mn)*/ t.* 
from t_tables t  where exists 
(select /*+ qb_name(sub1)*/ 1 
from t_tablespaces ts 
where t.tablespace_name=ts.tablespace_name and ts.block_size=8) 
and exists 
(select /*+ qb_name(sub2)*/ 1 
from t_tablespaces ts 
where t.tablespace_name=ts.tablespace_name);

执行计划如下：

在这个查询中，外部对T_TABLES表的查询要同时满足SUB1和SUB2两个子查询，而SUB1在语义上又是SUB2的子集，因此优化器将两个子查询进行了合并(只进行一次对T_TABLESPACES表的扫描)，然后与外部表T_TABLES进行半连接。

那么如果语义不等价又会怎么样呢？

select /*+ qb_name(mn)*/ t.* 
from t_tables t 
where exists 
(select /*+ qb_name(sub1)*/ 1 
from t_tablespaces ts 
where t.tablespace_name=ts.tablespace_name and ts.block_size=8) 
and exists 
(select /*+ qb_name(sub2)*/ 1 
from t_tablespaces ts 
where t.tablespace_name=ts.tablespace_name and ts.block_size=16);

执行计划如下：

在这个查询语句中，外部查询要满足两个子查询—SUB1和SUB2，但两者条件不同，不能简单合并。因此在执行计划中，分别对两者进行了扫描（直观感觉就是对T_TABLESPACES进行了两次扫描），然后再做关联查询。

五、子查询优化

子查询实体化是指在上面WITH定义的查询中，将查询结果写入一张临时表中，后续的查询直接利用临时表中的数据。可以通过MATERIALIZE提示来控制。下面看个示例。

SQL> with v as 
(select /*+ MATERIALIZE */ * from t_users where username='SYS') 
select count(*) from v;

执行计划如下：

在ID=2的步骤中生成了一张临时表SYS_TEMP_xxx，并且这个临时表在后面会被直接使用。如果去掉提示会怎样呢？

SQL> with v as 
(select * from t_users where username='SYS') 
select count(*) from v;

执行计划如下：

此时不再生成临时表，直接解嵌套执行。

-----the end

本文来自云栖社区合作伙伴“DBGEEK”