查询转换

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';

**********本博客所有内容均为原创，如有转载请注明作者和出处!!!**********
Name:    guoyJoe

QQ:        252803295

Email:    oracledba_cn@hotmail.com

Blog:      http://blog.csdn.net/guoyJoe

ITPUB:   http://www.itpub.net/space-uid-28460966.html

OCM:     http://education.oracle.com/education/otn/YGuo.HTM
 _____________________________________________________________
加群验证问题：哪些SGA结构是必需的，哪些是可选的？否则拒绝申请!!!

答案在：http://blog.csdn.net/guoyjoe/article/details/8624392

Oracle@Paradise 　总群:127149411

Oracle@Paradise No.1群:177089463(已满)

Oracle@Paradise No.2群:121341761

Oracle@Paradise No.3群:140856036