Query Transformation
在继续研究SQL的其他操作（比如Join）对CBO的影响之前，我们来讨论一下Oracle优化器的Query Transformation特性。我们都习惯于根据我们的SQL结构来判断SQL的效率，但是我们必须要知道，对于我们写好的SQL，Oracle的优化器会进行改写，或者说是进行转化。转化的目的是是要把我们的SQL转化成oracle认为更简单，更有效率的结构，需要注意的是，有些时候（主要取决的版本），CBO的的某些转化似乎不是基于cost的，而是基于rule的，用官方文档的用词就是heuristical的。因此有时候CBO做一个特定的Query Transformation的时候，并不比较Transformation之前的plan和Transformation之后的plan到底那个cost小，而是仅仅是因为“根据xx规则，Transformation之后性能会好”。
对于Query Transformation的内部代码的变更，似乎Oracle总是follow这样的步骤：
• Beta-like state: The internal code exists, and you can make it happen with a (possibly) hidden parameter or undocumented hint.
• First official publication: The internal code is enabled by default, but not costed, so the transformation always happens.
• Final state: The optimizer works out the cost of the original and the transformed SQL and takes the cheaper option. The hint is deprecated (as, for example, hash_aj has been in 10g).
因此，对于那些"enabled by default, but not costed, so the transformation always happens." 的情况，可能会出现转化后的性能还比不上转换前的性能的情况，（否则也就不需要一个final state来做cost比较了），在这种情况下，我们需要手工给SQL加'hine'来保证SQL可以以合理的方式执行。
1）Filtering
尽管是讲Query Transformation，但这一部分将要花很多时间介绍Oracle的filter操作，所以这部分的title就叫filtering吧。
大多数的Query Transformation都是发生在SQL里面出现子查询的时候，我们先来看一个例子：
首先我们来创建一个20000行的表emp，假设这是用来记录员工信息的，dept_no这个列用来代表员工的部门，这里我们一共有6个部门，sal代表员工的薪水：
create table emp(
dept_no not null,
sal,
emp_no not null,
padding,
constraint e_pk primary key(emp_no)
)
as
with generator as (
select --+ materialize
rownum id
from all_objects
where rownum <= 1000
)
select
mod(rownum,6),
rownum,
rownum,
rpad('x',60)
from
generator v1,
generator v2
where
rownum <= 20000
;
begin
dbms_stats.gather_table_stats(
user,
'EMP',
cascade => true,
estimate_percent => null,
method_opt => 'for all columns size 1'
);
end;
/
现在我们要查询每个部门里薪水高于这个部门的平均工资的员工信息：
select
outer.*
from emp outer
where outer.sal > (
select
avg(inner.sal)
from emp inner
where inner.dept_no = outer.dept_no
)
;
Execution Plan (oracle 9.2.0.4)
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=101 Card=1000 Bytes=98000)
1 0 HASH JOIN (Cost=101 Card=1000 Bytes=98000)
2 1 VIEW OF 'VW_SQ_1' (Cost=65 Card=6 Bytes=156)
3 2 SORT (GROUP BY) (Cost=65 Card=6 Bytes=48)
4 3 TABLE ACCESS (FULL) OF 'EMP' (Cost=35 Card=20000 Bytes=160000)
5 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=35 Card=20000 Bytes=1440000)
这里我们看到在Oracle9i里的默认情况是：子查询
(
select
avg(inner.sal)
from emp inner
where inner.dept_no = outer.dept_no
)
被展开了，我们以后会发现plan里面出现VIEW OF 'VW_SQ_1' 在这里代表了一个展开子查询的行为，表明这里出现了一个Query Transformation，Oracle官方把我们这里的展开子查询的操作成为unnest。
那么如果我们不做Query Transformation，这个SQL的执行计划是怎么样的呢？我们可以在子查询里面使用hint no_unnnest来看不做Query Transformation的执行计划：
select
outer.*
from emp outer
where outer.sal > (
select /*+ no_unnest */
avg(inner.sal)
from emp inner
where inner.dept_no = outer.dept_no
)
;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=35035 Card=1000 Bytes=72000)
1 0 FILTER
2 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=35 Card=1000 Bytes=72000)
3 1 SORT (AGGREGATE)
4 3 TABLE ACCESS (FULL) OF 'EMP' (Cost=35 Card=3333 Bytes=26664)
在这里，首先VIEW OF 'VW_SQ_1' 消失了，同时hash join也被一个叫做FILTER的操作所取代，这就是不做Query Transformation的计划，也就是不展开子查询的执行计划。
我们看到第二个计划的cost是35035，远远大于第一个执行计划的cost，那是不是按照第二个计划一定会比第一个计划要慢呢？答案是不一定。很多时候Oracle Query Transformation之后的计划确实是更有效率，否则就不会在内部代码里指定这个转化，但这种转化并不能保证性能一定是更佳的，否则就不会在后续的版本加上转化前后的cost对比验证。
另外我们也必须知道的是explain plan告诉我们的cost有时候是不准确的，很多时候explain plan确实是尝试给我们提供尽量准确的信息，但有的时候explain plan的计算会离真正的cost相差甚远。这往往是由于某一个版本的CBO代码考虑不够周详所导致的。
在我们这个例子里，Cost=35035到底从何而来是很容易猜测的，
首先为了得到子查询的各部门平均工资的信息，我们需要对emp做一个全表扫描，这就是计划的第4行，这里的card=3333是因为条件里面有一个inner.dept_no = outer.dept_no，相当于inner.dept_no = 0（或者1，2，3，4，5），因为共有6个部门，所以card = 20000/6 =3333。
第3行是一个有名无实的sort，这个sort根本就不是真正的排序，他的作用是每次从第4行的全表扫描获取一条信息就计算一下现在的平均值。
第2行是另一个全表扫描，这个全表扫描的目的是获取每个员工的工资然后和该部门的平均工资作比较，注意这里的card是1000，这个1000是哪里来的，这个来自于条件outer.sal > (子查询)，子查询的结果是个还不确定的值，所以这里的处理相当于outer.sal > :bind1，当作了绑定变量。我们讲过绑定变量的这种范围查询的selectivity是5%，所以这里的card = 20000 * 5% = 1000
第1行是filter操作，很显然，Oracle9i认为通过filter操作会导致的的cost是：35*1000 + 35 =35035。也就是第二行的全表扫描cost(35) * 第二行的扫描次数(1000) + 第四行的全表扫描cost(35)。
这里的这个计算是错误的，而且是大错特错的。且不说card是个明显的估计，肯定不准，但这是一个很难估计的东西，因为确实是因公司而异，有的公司工资多余平均值的员工可能占一半，有的公司可能不到一半，因为工资差距太大，比如X想集团的CEO年薪700W$，不知道可以顶多少个DBA的工资了。
最重要的是计算filter的cost的计算公式完全就是错误的，为了说明这一点，有必要解释一下filter的工作机制，jonathan lewis用if-else的方式解释filter的工作机制如下：
if this is the first row selected from the driving table
execute the subquery with this driving value
retain the driving (input) and return (output) values as 'current values'
set the 'current values' status to 'not yet stored'.
else
if the driving value matches the input value from the 'current values'
return the output value from the 'current values'
else
if the status of the 'current values' is 'not yet stored'
attempt to store the 'current values' in an in-memory hash-table
if a hash collision occurs
discard the 'current values'
end if
end if
probe the hash table using the new driving value
if the new driving value is in the hash table
retrieve the stored return value from the in-memory hash table
retain these values as the 'current values'
set the 'current values' status to 'previously stored'
else
execute the subquery with the new driving value
retain the driving and return (output) values as 'current values'
set the 'current values' status to 'not yet stored'.
end if
return the output value from the 'current values'
end if
end if
与其费口舌把这个过程解释一遍，不如直接根据我们的例子解释一下来的方便，在我们的例子里，有6个部门，每个部门的平均工资如下：
SQL> select dept_no, avg(sal) from emp group by dept_no order by 1;
DEPT_NO AVG(SAL)
---------- ----------
0 10002
1 10000
2 10001
3 9999
4 10000
5 10001
6 rows selected.
根据上面介绍的规则，我们的这个filter的过程里需要在内存里维护一个hash table。根据计划：
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=35035 Card=1000 Bytes=72000)
1 0 FILTER
2 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=35 Card=1000 Bytes=72000)
3 1 SORT (AGGREGATE)
4 3 TABLE ACCESS (FULL) OF 'EMP' (Cost=35 Card=3333 Bytes=26664)
我们的步骤是
1, 先做步骤2的一次全表扫描，假如取得的第一行是(dept_no, sal)= (0, 9000)，然后做子查询的全表扫描，获得(dept_no, sal)= (0, 10002)，如果9000 > 10002那么这一行就作为最后输出的一部分，否则排除这一行(这里显然是排除)，但最后我们要做一个比较重要的操作就是把获得的dept_no=0的平均工资是10002这个信息，放到内存里的hash table里。
2，我们接下来处理步骤2里的全表扫描获得的第二行，假设之(:dept_no, :sal2)，这时候有要看具体情况了：
如果获得的是(0，10300），我们现在已经在hash table里记录了dept_no=0的平均工资，所以我们只要在内存里面做个比对就可以了，不需要做子查询的全表扫描。
如果获得的是(0，9999)，这一次获取的信息是和我们上一次获取的信息完全一样的，所以我们完全没有访问hash table的必要，直接排除这一行就可以了。
如果获取的是(1, 30000)，这一次是个新的部门，所以我们必须再对子查询做一次全表扫描，再比较，再把部门1的平均工资记到hash table里。
后面的操作就都是一样的了。
当我们比较了一些数据之后，hash table里会反应所有部门的平均工资，所以后面的filter就完全不需要再对子查询做全表扫描了。
的大致的情况可以用这个图表示：
从这里我们可以看到，其实我们真正需要做的全表扫描的次数只有7次，一次步骤2的扫描，6次步骤4的扫描---针对6个部门。如果我们升级到10g，我们会发现同样的sql的执行计划是：
Execution Plan (10.1.0.4)
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=245 Card=167 Bytes=12024)
1 0 FILTER
2 1 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=35 Card=20000 Bytes=1440000)
3 1 SORT (AGGREGATE)
4 3 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=35 Card=3333 Bytes=26664)
这里的cost明显是7次全表扫描的cost。这里card=167看上去应该是20000*5%/6，显然这里的5%是和9i一样把sal>()看做绑定变量的条件得来的。
但这就是准确的了么？就IO的角度来说，就我们这个特殊的case来说，是准确的了，但是关于filter还有两个需要考虑的地方。
关于CPU，大家可能已经意思到了，filter要对大量的数据做对比，这肯定是一个CPU密集型操作，而在filter的过程中，我们发现我们有一个机会是可以减少一些CPU的使用的，那就是每当我们从outer table中获取一条信息的时候，我们会比较这个信息是不是很上一条处理过的信息完全一样，比如我们连续得到两个（0,8000)，这时候我们完全不需要访问内存中的hash table，因此可以减少CPU的使用，特别是如果我们的emp表是按照dept_no,sal进行排序的话，会节省很多的CPU。
另外一点，关于IO，因为我们有6个部分，我们总是只需要全表扫描6+1次，那如果我们有300个部分，6+1次也是够的么？其实不是的。在oracle8i和oracle9i里面，hash table的大小是有限制的，大约是256个entry，而10g扩大到了1024个entry，有限的hash table大小就意味着有的dept_no通过hash会指到同一个entry上，这时候会在hash table上产生一个冲突，也就是jonathan lewis所说的hash collision occurs，当发生冲突的时候，比如原来这个entry里记录的是dept_no=0，和0冲突的是dept_no=67，那么对不起，hash table对67无法接纳，这时候我们需要为了dept_no=67去做子查询的全表扫描，而且以后所有的dept_no=67的数据都会产生全表扫描，如果dept_no=67的用户有10000人，dept_no=0只有1人，但恰恰这个dept_no=0的1人在表里处于靠前的位置，大家可以想象我们需要多做多少的IO! 由于对这里的hash算法不太清楚，所以不知道什么时候会冲突，但是当我们发现一个使用filter的SQL导致了过多的IO，这有可能是因为我们的表里的数据排列的不够幸运造成的。
因此，filtering可以是很慢的，也可以是很快的，这取决于我们的数据的具体情况。当filtering很快的时候，Query Transformation有可能是我们不需要的。
我们至今介绍了我们可以使用no_unnest这个hint来告诉oracle我们不想做unnest，因为我们有时候会想要避免这种Query Transformation。现在我们需要补充一个常用的hint，这个hint往往使用在CBO没有做unnest的时候：/*+ push_subq */
performance tuning guide关于push_subq的描述是：
The PUSH_SUBQ hint causes non-merged subqueries to be evaluated at the earliest possible step in the execution plan. Generally, subqueries that are not merged are executed as the last step in the execution plan. If the subquery is relatively inexpensive and reduces the number of rows significantly, then it improves performance to evaluate the subquery earlier.
我们考虑这个SQL：
select
par.small_vc1,
chi.small_vc1
from
parent par,
child chi
where
par.id1 between 100 and 200
and chi.id1 = par.id1
and exists (
select
null
from subtest sub
where
sub.small_vc1 = par.small_vc1
and sub.id1 = par.id1
and sub.small_vc2 >= '2'
)
;
这里包括子查询里的subtest，我们有三个表是在这个SQL里面有两组关系，parent和child的chi.id1 = par.id1, parent和subtest的sub.small_vc1 = par.small_vc1，sub.id1 = par.id1。
如果我们的CBO把子查询拆开，我们很明显有两种选择，一是先parent和child做join，然后join的结果和subtest再join，二是先parent和subtest做join，然后join的结果和child再join。具体用哪一个取决于哪个join可以返回更少的card。
但是如果我们使用no_unnnested来强迫CBO不要做unnest，我们就只剩下一个选择了，因为“Generally, subqueries that are not merged are executed as the last step in the execution plan.”这时候push_subq就可以派上用场了。
我们看看这个例子：
create table parent(
id1 number not null,
small_vc1 varchar2(10),
small_vc2 varchar2(10),
padding varchar2(200),
constraint par_pk primary key(id1)
);
create table child(
id1 number not null,
id2 number not null,
small_vc1 varchar2(10),
small_vc2 varchar2(10),
padding varchar2(200),
constraint chi_pk primary key (id1,id2)
)
;
create table subtest (
id1 number not null,
small_vc1 varchar2(10),
small_vc2 varchar2(10),
padding varchar2(200),
constraint sub_pk primary key(id1)
)
;
insert into parent
select
rownum,
to_char(rownum),
to_char(rownum),
rpad(to_char(rownum),100)
from
all_objects
where rownum <= 3000
;
commit;
begin
for i in 1..8 loop
insert into child
select
rownum,
i,
to_char(rownum),
to_char(rownum),
rpad(to_char(rownum),100)
from
parent;
commit;
end loop;
end;
/
insert into subtest
select * from parent;
commit;
begin
dbms_stats.gather_table_stats(
user,
'parent',
cascade => true,
estimate_percent => null,
method_opt => 'for all columns size 1'
);
end;
/
begin
dbms_stats.gather_table_stats(
user,
'child',
cascade => true,
estimate_percent => null,
method_opt => 'for all columns size 1'
);
end;
/
begin
dbms_stats.gather_table_stats(
user,
'subtest',
cascade => true,
estimate_percent => null,
method_opt => 'for all columns size 1'
);
end;
/
set autotrace traceonly
select
par.small_vc1,
chi.small_vc1
from
parent par,
child chi
where
par.id1 between 100 and 200
and chi.id1 = par.id1
and exists (
select
/*+ no_unnest */
null
from subtest sub
where
sub.small_vc1 = par.small_vc1
and sub.id1 = par.id1
and sub.small_vc2 >= '2'
)
;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=26 Card=6 Bytes=108)
1 0 FILTER
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'CHILD' (Cost=2 Card=1 Bytes=9)
3 2 NESTED LOOPS (Cost=14 Card=6 Bytes=108)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'PARENT' (Cost=4 Card=5 Bytes=45)
5 4 INDEX (RANGE SCAN) OF 'PAR_PK' (UNIQUE) (Cost=2 Card=102)
6 3 INDEX (RANGE SCAN) OF 'CHI_PK' (UNIQUE) (Cost=1 Card=1)
7 1 TABLE ACCESS (BY INDEX ROWID) OF 'SUBTEST' (Cost=2 Card=1 Bytes=14)
8 7 INDEX (UNIQUE SCAN) OF 'SUB_PK' (UNIQUE) (Cost=1 Card=3000)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1224 consistent gets
我们看到没有加push_subq的时候consistent gets是1224，cost就不考虑了，反正我们知道是不准的。
select
/*+ push_subq */
par.small_vc1,
chi.small_vc1
from
parent par,
child chi
where
par.id1 between 100 and 200
and chi.id1 = par.id1
and exists (
select
/*+ no_unnest */
null
from subtest sub
where
sub.small_vc1 = par.small_vc1
and sub.id1 = par.id1
and sub.small_vc2 >= '2'
)
;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=14 Card=6 Bytes=108)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'CHILD' (Cost=2 Card=1 Bytes=9)
2 1 NESTED LOOPS (Cost=14 Card=6 Bytes=108)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'PARENT' (Cost=4 Card=5 Bytes=45)
4 3 INDEX (RANGE SCAN) OF 'PAR_PK' (UNIQUE) (Cost=2 Card=102)
5 3 TABLE ACCESS (BY INDEX ROWID) OF 'SUBTEST' (Cost=2 Card=1 Bytes=14)
6 5 INDEX (UNIQUE SCAN) OF 'SUB_PK' (UNIQUE) (Cost=1 Card=3000)
7 2 INDEX (RANGE SCAN) OF 'CHI_PK' (UNIQUE) (Cost=1 Card=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
320 consistent gets
加了hint之后的consistent gets就是320了。
对于第二个查询大家可能会奇怪，既然是没有做子查询展开，为什么看不到filter这个关键字呢？实际上这里explain把filter字段隐藏起来了，完整的计划应该看上去类似与：
可能是Oracle认为index scan（第五行）和table scan（第三行）之间有一个filter是很怪异的，所以就把filter隐藏了，但实际上在这个情况里隐藏掉filter才会让人感到怪异。
2）Scalar Subqueries
我们简单的看一看如果我们把子查询放在select list里面，Query Transformation会做出什么样的操作：
select
count(av_sal)
from (
select /*+ no_merge */
outer.dept_no,
outer.sal,
outer.emp_no,
outer.padding,
(
select avg(inner.sal)
from emp inner
where inner.dept_no = outer.dept_no
) av_sal
from emp outer
)
where
sal > av_sal
;
在这个SQL里，首先注意一下我使用的hint: /*+ no_merge */，这是一个比较容易让人混淆的hint，有的人以为这是禁止Oracle使用merge join，这是被它的名字骗了，我们看看Oracle的performance guide里面是怎么介绍这个hint的：
我们应该看merge这个hint：
The MERGE hint lets you merge a view for each query.
If a view’s query contains a GROUP BY clause or DISTINCT operator in the SELECT list, then the optimizer can merge the view’s query into the accessing statement only if complex view merging is enabled. Complex merging can also be used to merge an IN subquery into the accessing statement if the subquery is uncorrelated.
The NO_MERGE hint causes Oracle not to merge mergeable views.
首先，merge hint可以把query里面的视图拆看，就像拆看子查询一样，Oracle文档还特意提到如果view里面包括了group by和distinct，这个view就是一个复杂的view了，必须把complex view merging功能打开-----这个在9i是默认打开的，有一个隐含参数控制的：
select i.ksppinm name , v.ksppstvl value
from x$ksppi i, x$ksppcv v
where i.indx = v.indx
and i.ksppinm like '¶';
Enter value for para: %merging
old 4: and i.ksppinm like '¶'
new 4: and i.ksppinm like '%merging'
NAME VALUE
---------------------- ----------
_complex_view_merging TRUE
complex view merging另一个作用是可以查分in的子查询，条件是这个子查询是uncorrelated，这里所说的uncorrelated是说In的子查询的查询结果是不受outer query的影响的。
弄清楚merge之后，no_merge就是保证CBO不会做merge。
我们现在来看看前面的查询的执行计划：
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=35035 Card=1 Bytes=26)
1 0 SORT (AGGREGATE)
2 1 VIEW (Cost=35035 Card=1000 Bytes=26000)
3 2 FILTER
4 3 TABLE ACCESS (FULL) OF 'EMP' (Cost=35 Card=1000 Bytes=8000)
5 3 SORT (AGGREGATE)
6 5 TABLE ACCESS (FULL) OF 'EMP' (Cost=35 Card=3333 Bytes=26664)
因为
(
select avg(inner.sal)
from emp inner
where inner.dept_no = outer.dept_no
) av_sal
是作为select list里面的一个列，我们可能以为他会在每次返回一行的时候运行一次，但结果是得到了我们前面用no_unnest hint所得到的执行计划，也就是说，CBO把select list上的子查询转化到where后面的子查询，但是没有进一步对这个子查询做unnest。当然这也是受到no_merge的影响，在一些其他的例子里，如果我们不佳no_merge这个hint，我们是可以看到CBO把子查询完全打开，所有的表做join的情况，但是在我的这个SQL里面如果去掉no_merge的话我的session会出乎意料的crash掉，这可能是Oracle的一个bug：
select
count(av_sal)
from (
select
outer.dept_no,
outer.sal,
outer.emp_no,
outer.padding,
(
select avg(inner.sal)
from emp inner
where inner.dept_no = outer.dept_no
) av_sal
from emp outer
)
where
sal > av_sal
;
ERROR at line 1:
ORA-03113: end-of-file on communication channel
3）Complex View Merging
我们前面已经介绍了有关view merge的hint，现在我们看看complex view merging是如何工作的。
按照Oracle的官方说法，
Mergeable and Nonmergeable Views
The optimizer can merge a view into a referencing query block when the view has one or more base tables,
provided the view does not contain any of the following:
Set operators (UNION, UNION ALL, INTERSECT, MINUS)
A CONNECT BY clause
A ROWNUM pseudocolumn
Aggregate functions (AVG, COUNT, MAX, MIN, SUM) in the select list
When a view contains one of the following structures, it can be merged into a referencing query block only if Complex View Merging is enabled:
A GROUP BY clause
A DISTINCT operator in the select list
现在我们就对complex view merging进行测试：
建表：
create table t1 (
id_par number(6) not null,
vc1 varchar2(32) not null,
vc2 varchar2(32) not null,
padding varchar2(100)
);
alter table t1 add constraint t1_pk primary key (id_par);
create table t2 (
id_ch number(6) not null,
id_par number(6) not null,
val number(6,2),
padding varchar2(100)
);
alter table t2 add constraint t2_pk primary key (id_ch);
alter table t2 add constraint t2_fk_t1 foreign key (id_par) references t1;
insert into t1
select
rownum,
vc1,
vc2,
rpad('x',100)
from
(
select
lpad(trunc(sqrt(rownum)),32) vc1,
lpad(rownum,32) vc2
from all_objects
where rownum <= 32
)
;
commit;
insert into t2
select
rownum,
d1.id_par,
rownum,
rpad('x',100)
from
t1 d1,
t1 d2
;
commit;
begin
dbms_stats.gather_table_stats(
user,
't1',
cascade => true,
estimate_percent => null,
method_opt => 'for all columns size 1'
);
end;
/
begin
dbms_stats.gather_table_stats(
user,
't2',
cascade => true,
estimate_percent => null,
method_opt => 'for all columns size 1'
);
end;
/
创建视图，注意是要带group by的：
create or replace view avg_val_view AS
select
id_par, avg(val) avg_val_t1
from t2
group by
id_par
;
set autotrace traceonly explain
select
t1.vc1, avg_val_t1
from
t1, avg_val_view
where
t1.vc2 = lpad(18,32)
and avg_val_view.id_par = t1.id_par
;
执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=9 Card=23 Bytes=1909)
1 0 SORT (GROUP BY) (Cost=9 Card=23 Bytes=1909)
2 1 HASH JOIN (Cost=7 Card=32 Bytes=2656)
3 2 TABLE ACCESS (FULL) OF 'T1' (Cost=2 Card=1 Bytes=76)
4 2 TABLE ACCESS (FULL) OF 'T2' (Cost=4 Card=1024 Bytes=7168)
默认的情况，我们看到视图被并入了整个SQL，也就是做了merge。
##############################################################
alter session set "_complex_view_merging"=true;
select
t1.vc1, avg_val_t1
from
t1, avg_val_view
where
t1.vc2 = lpad(18,32)
and avg_val_view.id_par = t1.id_par
;
执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=9 Card=23 Bytes=1909)
1 0 SORT (GROUP BY) (Cost=9 Card=23 Bytes=1909)
2 1 HASH JOIN (Cost=7 Card=32 Bytes=2656)
3 2 TABLE ACCESS (FULL) OF 'T1' (Cost=2 Card=1 Bytes=76)
4 2 TABLE ACCESS (FULL) OF 'T2' (Cost=4 Card=1024 Bytes=7168)
这个结果肯定是和上一个一样的，因为_complex_view_merging默认就是true
################################################################
select
/*+ no_merge (avg_val_view) */
t1.vc1, avg_val_t1
from
t1, avg_val_view
where
t1.vc2 = lpad(18,32)
and avg_val_view.id_par = t1.id_par
;
执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=10 Card=1 Bytes=95)
1 0 HASH JOIN (Cost=10 Card=1 Bytes=95)
2 1 TABLE ACCESS (FULL) OF 'T1' (Cost=2 Card=1 Bytes=69)
3 1 VIEW OF 'AVG_VAL_VIEW' (Cost=7 Card=32 Bytes=832)
4 3 SORT (GROUP BY) (Cost=7 Card=32 Bytes=224)
5 4 TABLE ACCESS (FULL) OF 'T2' (Cost=4 Card=1024 Bytes=7168)
我们看到如果用hint禁止使用merge，那么视图会被单独使用。最后t1和AVG_VAL_VIEW结果做hash join。
#####################################################################
alter session set "_complex_view_merging"=false;
select
t1.vc1, avg_val_t1
from
t1, avg_val_view
where
t1.vc2 = lpad(18,32)
and avg_val_view.id_par = t1.id_par
;
执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=10 Card=1 Bytes=95)
1 0 HASH JOIN (Cost=10 Card=1 Bytes=95)
2 1 TABLE ACCESS (FULL) OF 'T1' (Cost=2 Card=1 Bytes=69)
3 1 VIEW OF 'AVG_VAL_VIEW' (Cost=7 Card=32 Bytes=832)
4 3 SORT (GROUP BY) (Cost=7 Card=32 Bytes=224)
5 4 TABLE ACCESS (FULL) OF 'T2' (Cost=4 Card=1024 Bytes=7168)
设置"_complex_view_merging"=false;和加no_merge hint的效果在我们这个测试里是一样的，但可以想象如果我们的视图不是一个complex的视图，影响就不同了。
#######################################################################
select
/*+ merge(avg_val_view) */
t1.vc1, avg_val_t1
from
t1, avg_val_view
where
t1.vc2 = lpad(18,32)
and avg_val_view.id_par = t1.id_par
;
执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=10 Card=1 Bytes=95)
1 0 HASH JOIN (Cost=10 Card=1 Bytes=95)
2 1 TABLE ACCESS (FULL) OF 'T1' (Cost=2 Card=1 Bytes=69)
3 1 VIEW OF 'AVG_VAL_VIEW' (Cost=7 Card=32 Bytes=832)
4 3 SORT (GROUP BY) (Cost=7 Card=32 Bytes=224)
5 4 TABLE ACCESS (FULL) OF 'T2' (Cost=4 Card=1024 Bytes=7168)
我们看到，在"_complex_view_merging"=false使用merge hint是无效的。
这部分比较简单，唯一提示一下的是在9i里，做merge view的时候是不会比较merge前后的执行计划哪个好的（即不基于cost），但是10g里面是会做比较的（基于cost）。
4）Pushing Predicates
我们已经知道，当我们的view不能做merge的时候，view会单独处理，但是我们会发现在做nested loops的时候，我们会把view外面的join条件引入到view里面去。这样能够带来好处是可以现在view里面过滤一部分数据，然后view的结果集再和外面的表做join，对于nested loops这是很有帮助的。这种转变叫做：Pushing Predicates。
实际上这种情况发生的不是很频繁，我也只是在nested loops下，表和view的join条件是外连接的时候发现这种效果：
代码如下：
建表：
create table t1 as
select
rownum - 1 id1,
trunc((rownum - 1)/10) n1,
lpad(rownum,10,'0') small_vc,
rpad('x',100) padding
from
all_objects
where
rownum <= 5000
;
alter table t1 add constraint t1_pk primary key(id1);
create table t2 as
select
trunc((rownum-1)/5) id1,
rownum id2,
lpad(rownum,10,'0') small_vc,
rpad('x',100) padding
from
all_objects
where
rownum <= 25000
;
alter table t2 add constraint t2_pk primary key(id1, id2);
create table t3 as
select
trunc((rownum-1)/5) id1,
rownum id2,
lpad(rownum,10,'0') small_vc,
rpad('x',100) padding
from
all_objects
where
rownum <= 25000
;
alter table t3 add constraint t3_pk primary key(id1, id2);
begin
dbms_stats.gather_table_stats(
ownname => user,
tabname =>'T1',
cascade => true,
estimate_percent => null,
method_opt => 'for all columns size 1'
);
end;
/
begin
dbms_stats.gather_table_stats(
ownname => user,
tabname =>'T2',
cascade => true,
estimate_percent => null,
method_opt => 'for all columns size 1'
);
end;
/
begin
dbms_stats.gather_table_stats(
ownname => user,
tabname =>'T3',
cascade => true,
estimate_percent => null,
method_opt => 'for all columns size 1'
);
end;
/
建立视图，因为视图里面包含了连接条件，所以无法做view merge。
create or replace view v1 as
select
t2.id1,
t2.id2,
t3.small_vc,
t3.padding
from
t2, t3
where
t3.id1 = t2.id1
and t3.id2 = t2.id2
;
现在我们看看这个SQL的执行计划：
SQL> explain plan for
2 select
3 t1.*,
4 v1.*
5 from
6 t1,
7 v1
8 where
9 t1.n1 = 5
10 and t1.id1 between 10 and 50
11 and v1.id1(+) = t1.id1
12 ;
已解释。
SQL> SET LINESIZE 130
SQL> SET PAGESIZE 0
SQL> SELECT *
2 FROM TABLE(DBMS_XPLAN.DISPLAY);
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 239 | 5 |
| 1 | NESTED LOOPS OUTER | | 1 | 239 | 5 |
|* 2 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 119 | 3 |
|* 3 | INDEX RANGE SCAN | T1_PK | 42 | | 2 |
| 4 | VIEW PUSHED PREDICATE | V1 | 1 | 120 | |
|* 5 | FILTER | | | | |
| 6 | NESTED LOOPS | | 1 | 127 | 3 |
|* 7 | INDEX RANGE SCAN | T2_PK | 1 | 8 | 2 |
| 8 | TABLE ACCESS BY INDEX ROWID| T3 | 1 | 119 | 1 |
|* 9 | INDEX UNIQUE SCAN | T3_PK | 1 | | |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T1"."N1"=5)
3 - access("T1"."ID1">=10 AND "T1"."ID1"<=50)
5 - filter("T1"."ID1"<=50 AND "T1"."ID1">=10)
7 - access("T2"."ID1"="T1"."ID1")
filter("T2"."ID1">=10 AND "T2"."ID1"<=50)
9 - access("T3"."ID1"="T2"."ID1" AND "T3"."ID2"="T2"."ID2")
filter("T3"."ID1">=10 AND "T3"."ID1"<=50)
Note: cpu costing is off
已选择28行。
注意第4行：VIEW PUSHED PREDICATE，这说明Pushing Predicates发生了，我们可以进一步看到的转变是access("T2"."ID1"="T1"."ID1")，access("T3"."ID1"="T2"."ID1" AND "T3"."ID2"="T2"."ID2")，而这两个join条件进一步转化成了t2_pk和t3_pk上的range scan。
最后，我们可以通过在session级别修改alter session set "_push_join_predicate"=false;来禁止这种转变。