1、视图合并(view merge)
SELECT to_char(wmsys.wm_concat(a.TABLE_NAME))
FROM user_tables a, dba_objects b
WHERE a.TABLE_NAME = b.OBJECT_NAME
AND b.OWNER = 'GYJ'
AND B.OBJECT_TYPE = 'TABLE';
SELECT /*+ no_merge(a) */ to_char(wmsys.wm_concat(a.TABLE_NAME))
FROM user_tables a, dba_objects b
WHERE a.TABLE_NAME = b.OBJECT_NAME
AND b.OWNER = 'GYJ'
AND B.OBJECT_TYPE = 'TABLE';
2、谓词推进(Predicate Pushing)
SQL语句中包含有不能合并的视图,并且视图有谓词过滤,CBO会将where过滤条件推入视图中，这个就叫做谓词推入
create or replace view emp12 as select /*+ NO_MERGE */ ename,job,deptno from emp where sal>(select avg(sal) from emp);
select * from emp12 where job='ANALYST';
create or replace view emp11 as select ename,job,deptno from emp where sal>(select avg(sal) from emp) and rownum>=1;
select * from emp11 where job='ANALYST';
3、子查询非嵌套化(Subquery Unnesting)
explain plan for select * from emp where deptno in(select deptno from dept where dname='SALES');
explain plan for select * from emp where deptno in(select /*+ NO_UNNEST */ deptno from dept where dname='SALES');
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'ADVANCED -PROJECTION'));
4、OR条件展开
select sal from emp where job='CLERK' or deptno=10;
select sal from emp where job='CLERK'
UNION ALL
select sal from emp where deptno=10 and job<>'CLERK';
