查询转换
来源:互联网 发布:java 打印成记事本 编辑:程序博客网 时间:2024/05/18 03:51
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 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
- 查询转换
- 查询转换
- SQL 行列转换查询
- SQL 行列转换查询
- LinQ查询-转换
- (7)查询转换
- mysql查询类型转换
- 查询转换-视图合并
- 2.6 查询转换
- Oracle查询转换
- Oracle 查询转换初探
- Oracle查询 行列式转换
- oracle 查询转换
- 查询转换方式
- Mysql 查询时间转换
- 查询转换--子查询解嵌套
- mysql 日期转换,时间转换,时间段查询
- 性别转换的查询语句
- Ubuntu12.04下eclipse提示框黑色背景色的修改方法
- hdu 1050 moving tables
- android背景优化
- CF Round#2
- Unity--如何同时打开多个Unity软件
- 查询转换
- 企业如何确定是否需要BPM(出自:计世网)
- 国内HTML5前端开发框架汇总
- 绑定变量与直方图
- CF Round#5
- 【C/C++】C语言中一些容易被人忽略的东西 之一 【内存的分配与释放】
- MBTI测试
- IsWow64Process实现
- CF Round#8