Oracle & Starcraft

view merge在outer join的环境下可能会带来不佳的效果，下面这个例子就反映了语句选择了全表扫描而不是index scan。

SQL 10G>create table test as select * from emp;
Table created.

SQL 10G>alter table test add(lastname varchar2(10));
Table altered.

SQL 10G>create or replace view v_test as select empno,ename,job,mgr,hiredate,sal,comm,deptno,lastname,ename||’,'||lastname fullname
from test;
View created.

SQL 10G>update test set lastname=ename;
11 rows updated.

SQL 10G>commit;

Commit complete.

SQL 10G>create index ind_test_deptno on test(deptno);
Index created.

SQL 10G>select t.fullname from v_test t,dept d where t.deptno(+)=d.deptno;
FULLNAME
———————
aaa,aaa
ALLEN,ALLEN
WARD,WARD
JONES,JONES
MARTIN,MARTIN
BLAKE,BLAKE
SCOTT,SCOTT
TURNER,TURNER
ADAMS,ADAMS
JAMES,JAMES
FORD,FORD
FULLNAME
———————

12 rows selected.

Execution Plan
———————————————————-
Plan hash value: 1395862932
——————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————-
| 0 | SELECT STATEMENT | | 11 | 308 | 5 (20)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 11 | 308 | 5 (20)| 00:00:01 |
| 2 | INDEX FULL SCAN | PK_DEPT | 3 | 9 | 1 (0)| 00:00:01 |
| 3 | VIEW | V_TEST | 11 | 275 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| TEST | 11 | 297 | 3 (0)| 00:00:01 |
——————————————————————————-
Predicate Information (identified by operation id):
—————————————————
1 - access(”T”.”DEPTNO”(+)=”D”.”DEPTNO”)
Note
—–
- dynamic sampling used for this statement

Statistics
———————————————————-
19 recursive calls
0 db block gets
19 consistent gets
0 physical reads
0 redo size
625 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
12 rows processed

可以看到这里并没有发生view merge，而是选择了先通过全表扫描实体化视图，然后再做外连接，最后一行值为null。

SQL 10G>select t.ename||’,'||lastname from v_test t,dept d where t.deptno(+)=d.deptno;
T.ENAME||’,'||LASTNAM
———————
七公,七公
JONES,JONES
SCOTT,SCOTT
ADAMS,ADAMS
FORD,FORD
ALLEN,ALLEN
WARD,WARD
MARTIN,MARTIN
BLAKE,BLAKE
TURNER,TURNER
JAMES,JAMES
T.ENAME||’,'||LASTNAM
———————
,
12 rows selected.

Execution Plan
———————————————————-
Plan hash value: 3009078635
————————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————————————
| 0 | SELECT STATEMENT | | 11 | 330 | 2 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 11 | 330 | 2 (0)| 00:00:01 |
| 2 | INDEX FULL SCAN | PK_DEPT | 3 | 9 | 1 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| TEST | 4 | 108 | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IND_TEST_DEPTNO | 6 | | 0 (0)| 00:00:01 |
————————————————————————————————
Predicate Information (identified by operation id):
—————————————————
4 - access(”DEPTNO”(+)=”D”.”DEPTNO”)
Note
—–
- dynamic sampling used for this statement

Statistics
———————————————————-
12 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
640 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
12 rows processed

而我们直接select ename||’,'||lastname的话view merge产生了作用，执行计划表明了这里进行了nest loop outer join，并没有实体化view，所以最后一行的结果是一个逗号。

SQL 10G>select t.fullname from v_test t,dept d where t.deptno=d.deptno;
11 rows selected.

Execution Plan
———————————————————-
Plan hash value: 2511043936
———————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————————–
| 0 | SELECT STATEMENT | | 11 | 198 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 4 | 60 | 1 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 11 | 198 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 3 | 9 | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IND_TEST_DEPTNO | 6 | | 0 (0)| 00:00:01 |
———————————————————————————————–
Predicate Information (identified by operation id):
—————————————————
4 - access(”DEPTNO”=”D”.”DEPTNO”)

Statistics
———————————————————-
8 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
619 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
11 rows processed

如果不使用外连接，那么这个view还是会被merge的。

出现这样的情况是由于一个隐含参数控制的，_COMPLEX_VIEW_MERGING
这个参数控制复杂视图的merge功能，默认是true,在开始这个例子前我把它置为了false。

看看改成true后结果会有变化没

SQL 10G>alter session set “_COMPLEX_VIEW_MERGING”=true;
Session altered.

SQL 10G>select t.fullname from v_test t,dept d where t.deptno(+)=d.deptno;
FULLNAME
———————
七公,七公
JONES,JONES
SCOTT,SCOTT
ADAMS,ADAMS
FORD,FORD
ALLEN,ALLEN
WARD,WARD
MARTIN,MARTIN
BLAKE,BLAKE
TURNER,TURNER
JAMES,JAMES
FULLNAME
———————

12 rows selected.

Execution Plan
———————————————————-
Plan hash value: 3009078635
————————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————————————
| 0 | SELECT STATEMENT | | 11 | 462 | 2 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 11 | 462 | 2 (0)| 00:00:01 |
| 2 | INDEX FULL SCAN | PK_DEPT | 3 | 9 | 1 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| TEST | 4 | 156 | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IND_TEST_DEPTNO | 6 | | 0 (0)| 00:00:01 |
————————————————————————————————
Predicate Information (identified by operation id):
—————————————————
4 - access(”DEPTNO”(+)=”D”.”DEPTNO”)
Note
—–
- dynamic sampling used for this statement

Statistics
———————————————————-
12 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
625 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
12 rows processed

可以看到view merge起作用了，通过这个例子我们可以了解_COMPLEX_VIEW_MERGING参数的作用，但是这个参数不仅仅作用于这种情况，其他情况请参考
http://www.cs.umbc.edu/help/oracle8/server.815/a67781/c20b_ops.htm