semijoin链接进行subquery unnesting.
来源:互联网 发布:马化腾第一个软件 编辑:程序博客网 时间:2024/06/07 06:51
drop table emp1;
drop table dept1;
create table emp1 as select * from emp;
create table dept1 as select * from dept;
SQL> select e.empno, e.deptno
from emp1 e
where e.deptno in (select d.deptno from dept1 d where d.loc = 'CHICAGO'); 2 3
EMPNO DEPTNO
---------- ----------
7900 30
7844 30
7698 30
7654 30
7521 30
7499 30
6 rows selected.
SQL> select e.empno, e.deptno
from emp1 e, dept1 d
where e.deptno = d.deptno
and d.loc = 'CHICAGO'; 2 3 4
EMPNO DEPTNO
---------- ----------
7499 30
7521 30
7654 30
7698 30
7844 30
7900 30
6 rows selected.
此时子查询被改写成关联,结果完全等价,是因为d.deptno上是Unique索引
那如果d.deptno不唯一呢?
SQL> select * from dept1;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
30 SALES CHICAGO
SQL> select e.empno, e.deptno
from emp1 e
where e.deptno in (select d.deptno from dept1 d where d.loc = 'CHICAGO'); 2 3
EMPNO DEPTNO
---------- ----------
7900 30
7844 30
7698 30
7654 30
7521 30
7499 30
6 rows selected.
SQL> select e.empno, e.deptno
from emp1 e, dept1 d
where e.deptno = d.deptno
and d.loc = 'CHICAGO'; 2 3 4
EMPNO DEPTNO
---------- ----------
7499 30
7499 30
7521 30
7521 30
7654 30
7654 30
7698 30
7698 30
7844 30
7844 30
7900 30
EMPNO DEPTNO
---------- ----------
7900 30
12 rows selected.
改写成关联后结果就翻倍了得去从
SQL> select e.empno, e.deptno
from emp1 e, dept1 d
where e.deptno = d.deptno
and d.loc = 'CHICAGO'
group by e.empno, e.deptno 2 3 4 5 ;
EMPNO DEPTNO
---------- ----------
7844 30
7521 30
7698 30
7900 30
7654 30
7499 30
6 rows selected.
- semijoin链接进行subquery unnesting.
- Optimizer Transformations: Subquery Unnesting part 1
- Oracle 子查询展开(subquery unnesting)
- 20 查询变换(subquery unnesting) --优化主题系列
- semiJoin mapreduce
- Subquery
- subquery 框架
- SQL Subquery
- jpa2 subquery
- MapReduce表连接之半连接SemiJoin
- correlated subquery and non-correlated subquery
- correlated subquery and non-correlated subquery
- SQL Subquery的用法
- 什么是scalar subquery
- Using A Subquery
- Subquery and Wrapping query
- SubQuery or Join?
- What is SUBQUERY?
- 屏蔽优酷黑屏广告
- AMS1117的输入电压范围
- 回调函数(转)
- 图像处理知识总结(1)
- oracle中常用命令汇总(一)
- semijoin链接进行subquery unnesting.
- ORACLE CURSOR 游标详解
- 转:java二维码工具类,中间带LOGO的,很强大
- 去除数组中重复的元素
- Java制作圆形按钮
- ios uiwebview 长按图片,保存到本地相册
- Android之Camera拍照插值算法
- js数组的扩展方法
- ArcGIS三维建模(二)