--通过执行计划分析,你就知道原因了,全表扫描次数不一样 1、成对比较: EXPLAIN PLAN FOR SELECT * FROM Emp WHERE (Sal, Nvl(Comm, 0)) IN (SELECT Sal, Nvl(Comm, 0) FROM Emp WHERE Deptno = 30);SELECT * FROM TABLE(dbms_xplan.display);--执行计划: Plan hash value: 1026997002
Predicate Information (identified by operation id): ---------------------------------------------------
1 - access("SAL"="SAL" AND NVL("COMM",0)=NVL("COMM",0)) 3 - filter("DEPTNO"=30) 2、非成对比较: EXPLAIN PLAN FOR SELECT * FROM Emp WHERE Sal IN (SELECT Sal FROM Emp WHERE Deptno = 30) AND Nvl(Comm, 0) IN (SELECT Nvl(Comm, 0) FROM Emp WHERE Deptno = 30);SELECT * FROM TABLE(dbms_xplan.display);--执行计划: Plan hash value: 4041616129
--是不是数据的问题? --我觉得应该是返回结果一样才对!第二种肯定效率低于第一种,因为少了全表扫描次数 CPU那些也少了 --从执行计划可以看出来--我执行了下两个结果,发现时一样的:SQL*Plus: Release 8.0.6.0.0 - Production on 星期三 11月 3 15:44:23 2010(c) Copyright 1999 Oracle Corporation. All rights reserved. Connected to: Oracle Database 10g Release 10.1.0.2.0 - ProductionSQL> set linesize 1000 SQL> select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----- 7369 SMITH CLERK 7902 17-12月-80 800 20 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 7566 JONES MANAGER 7839 02-4月 -81 2975 20 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 7782 CLARK MANAGER 7839 09-6月 -81 1450 10 7788 SCOTT ANALYST 7566 09-12月-82 3000 20 7839 KING PRESIDENT 17-11月-81 5000 10 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 7876 ADAMS CLERK 7788 12-1月 -83 1100 20 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----- 7900 JAMES CLERK 7698 03-12月-81 950 30 7902 FORD ANALYST 7566 03-12月-81 3000 20 7934 MILLER CLERK 7782 23-1月 -82 1300 10 7955 BLACK SALESMAN 7698 28-3月 -82 1250 1400 30 7999 MAXTON MANAGER 7698 28-3月 -82 11250 1400 3016 rows selected.SQL> --1、成对比较: SQL> SELECT * 2 FROM Emp 3 WHERE (Sal, Nvl(Comm, 0)) IN 4 (SELECT Sal, Nvl(Comm, 0) FROM Emp WHERE Deptno = 30); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----- 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 7955 BLACK SALESMAN 7698 28-3月 -82 1250 1400 30 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 7900 JAMES CLERK 7698 03-12月-81 950 30 7999 MAXTON MANAGER 7698 28-3月 -82 11250 1400 308 rows selected.SQL> --2、非成对比较: SQL> SELECT * 2 FROM Emp 3 WHERE Sal IN (SELECT Sal FROM Emp WHERE Deptno = 30) 4 AND Nvl(Comm, 0) IN (SELECT Nvl(Comm, 0) FROM Emp WHERE Deptno = 30); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----- 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 7955 BLACK SALESMAN 7698 28-3月 -82 1250 1400 30 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 7900 JAMES CLERK 7698 03-12月-81 950 30 7999 MAXTON MANAGER 7698 28-3月 -82 11250 1400 308 rows selected.SQL>
Nvl(Comm, 0) 要写成 Comm,如果Comm的值为 null时 IN 会失效
SELECT * FROM Emp WHERE Sal IN (SELECT Sal FROM Emp WHERE Deptno = 30) AND Nvl(Comm, 0) IN (SELECT Nvl(Comm, 0) FROM Emp WHERE Deptno = 30);中 只要 0 in(SELECT Nvl(Comm, 0) FROM Emp WHERE Deptno = 30); 时,会把所有的数据都显示出来的
--刚才马虎了,没有注意子查询中的where条件。 --这两条语句不等价,比如在表中存在下面的数据: sal comm deptno 1 A 30 2 B 30 1 B 29 2 A 28那么成对查询得到的结果是2条,不成对查询得到的结果是4条。成对查询例句: WITH EMP AS ( SELECT 1 SAL, 'A' COMM, 30 DEPTNO FROM DUAL UNION ALL SELECT 2 , 'B' , 30 FROM DUAL UNION ALL SELECT 1 , 'B' , 29 FROM DUAL UNION ALL SELECT 2 , 'A' , 28 DEPT FROM DUAL ) SELECT * FROM Emp WHERE (Sal, Nvl(Comm, 0)) IN (SELECT Sal, Nvl(Comm, 0) FROM Emp WHERE Deptno = 30);不成对查询例句: WITH EMP AS ( SELECT 1 SAL, 'A' COMM, 30 DEPTNO FROM DUAL UNION ALL SELECT 1 , 'B' , 29 FROM DUAL UNION ALL SELECT 2 , 'A' , 28 DEPT FROM DUAL ) SELECT * FROM Emp WHERE (Sal, Nvl(Comm, 0)) IN (SELECT Sal, Nvl(Comm, 0) FROM Emp WHERE Deptno = 30);
我在17楼的不查询的例句写错了,修正如下:不成对查询例句: WITH EMP AS ( SELECT 1 SAL, 'A' COMM, 30 DEPTNO FROM DUAL UNION ALL SELECT 2 , 'B' , 30 FROM DUAL UNION ALL SELECT 1 , 'B' , 29 FROM DUAL UNION ALL SELECT 2 , 'A' , 28 DEPT FROM DUAL ) SELECT * FROM Emp WHERE (Sal, Nvl(Comm, 0)) IN (SELECT Sal, Nvl(Comm, 0) FROM Emp WHERE Deptno = 30);
改为这样 WITH EMP AS ( SELECT 1 SAL, 'A' COMM, 30 DEPTNO FROM DUAL UNION ALL SELECT 2 , 'B' , 30 FROM DUAL UNION ALL SELECT 1 , 'B' , 29 FROM DUAL UNION ALL SELECT 2 , 'A' , 28 DEPT FROM DUAL ) SELECT * FROM Emp WHERE Sal IN (SELECT Sal FROM Emp WHERE Deptno = 30) AND Nvl(Comm, -1) IN (SELECT Nvl(Comm, -1) FROM Emp WHERE Deptno = 30);
非成对比较: WITH EMP AS ( SELECT 1 SAL, 'A' COMM, 30 DEPTNO FROM DUAL UNION ALL SELECT 2 , 'B' , 30 FROM DUAL UNION ALL SELECT 1 , 'B' , 29 FROM DUAL UNION ALL SELECT 2 , 'A' , 28 DEPT FROM DUAL ) SELECT * FROM Emp WHERE Sal IN (SELECT Sal FROM Emp WHERE Deptno = 30) AND Nvl(Comm, -1) IN (SELECT Nvl(Comm, -1) FROM Emp WHERE Deptno = 30);实际上相当于 WITH EMP AS ( SELECT 1 SAL, 'A' COMM, 30 DEPTNO FROM DUAL UNION ALL SELECT 2 , 'B' , 30 FROM DUAL UNION ALL SELECT 1 , 'B' , 29 FROM DUAL UNION ALL SELECT 2 , 'A' , 28 DEPT FROM DUAL ) SELECT * FROM Emp WHERE Nvl(Comm, -1) IN ('A','B') and Sal in (1,2); 这样就比较好理解了,当 Nvl(Comm, -1)为A的时候Sal可以为1,2 当Nvl(Comm, -1)为B的时候Sal也可以为1,2所以查询出来的结果是4条记录,而成对查询结果为2条记录
这可能就是 (col1,col2)in( value1,value2) 与 col1=value1 and col2=value2的区别今天学到了。呵呵
我改的这段,在看看你上传的代码就知道了!跟nvl(……)没关系,这是我从书上的例子直接copy上去的! WHERE Sal IN (SELECT Sal FROM Emp WHERE Deptno = 30) AND Nvl(Comm, -1) IN (SELECT Nvl(Comm, -1) FROM Emp WHERE Deptno = 30);
--通过执行计划分析,你就知道原因了,全表扫描次数不一样
1、成对比较:
EXPLAIN PLAN FOR
SELECT *
FROM Emp
WHERE (Sal, Nvl(Comm, 0)) IN
(SELECT Sal, Nvl(Comm, 0) FROM Emp WHERE Deptno = 30);SELECT * FROM TABLE(dbms_xplan.display);--执行计划:
Plan hash value: 1026997002
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 47 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN SEMI | | 1 | 47 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 16 | 608 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 8 | 72 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("SAL"="SAL" AND NVL("COMM",0)=NVL("COMM",0))
3 - filter("DEPTNO"=30)
2、非成对比较:
EXPLAIN PLAN FOR
SELECT *
FROM Emp
WHERE Sal IN (SELECT Sal FROM Emp WHERE Deptno = 30)
AND Nvl(Comm, 0) IN (SELECT Nvl(Comm, 0) FROM Emp WHERE Deptno = 30);SELECT * FROM TABLE(dbms_xplan.display);--执行计划:
Plan hash value: 4041616129
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 100 | 10 (10)| 00:00:01 |
|* 1 | HASH JOIN SEMI | | 2 | 100 | 10 (10)| 00:00:01 |
|* 2 | HASH JOIN SEMI | | 2 | 86 | 7 (15)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 16 | 608 | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| EMP | 8 | 40 | 3 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | EMP | 8 | 56 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("SAL"="SAL")
2 - access(NVL("COMM",0)=NVL("COMM",0))
4 - filter("DEPTNO"=30)
5 - filter("DEPTNO"=30)
第二条查询语句返回7条记录
--我觉得应该是返回结果一样才对!第二种肯定效率低于第一种,因为少了全表扫描次数 CPU那些也少了
--从执行计划可以看出来--我执行了下两个结果,发现时一样的:SQL*Plus: Release 8.0.6.0.0 - Production on 星期三 11月 3 15:44:23 2010(c) Copyright 1999 Oracle Corporation. All rights reserved.
Connected to:
Oracle Database 10g Release 10.1.0.2.0 - ProductionSQL> set linesize 1000
SQL> select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- ---------- ---------- ---------- -----
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 1450 10
7788 SCOTT ANALYST 7566 09-12月-82 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 12-1月 -83 1100 20 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- ---------- ---------- ---------- -----
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
7955 BLACK SALESMAN 7698 28-3月 -82 1250 1400 30
7999 MAXTON MANAGER 7698 28-3月 -82 11250 1400 3016 rows selected.SQL> --1、成对比较:
SQL> SELECT *
2 FROM Emp
3 WHERE (Sal, Nvl(Comm, 0)) IN
4 (SELECT Sal, Nvl(Comm, 0) FROM Emp WHERE Deptno = 30); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- ---------- ---------- ---------- -----
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7955 BLACK SALESMAN 7698 28-3月 -82 1250 1400 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7900 JAMES CLERK 7698 03-12月-81 950 30
7999 MAXTON MANAGER 7698 28-3月 -82 11250 1400 308 rows selected.SQL> --2、非成对比较:
SQL> SELECT *
2 FROM Emp
3 WHERE Sal IN (SELECT Sal FROM Emp WHERE Deptno = 30)
4 AND Nvl(Comm, 0) IN (SELECT Nvl(Comm, 0) FROM Emp WHERE Deptno = 30); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- ---------- ---------- ---------- -----
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7955 BLACK SALESMAN 7698 28-3月 -82 1250 1400 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7900 JAMES CLERK 7698 03-12月-81 950 30
7999 MAXTON MANAGER 7698 28-3月 -82 11250 1400 308 rows selected.SQL>
FROM Emp
WHERE Sal IN (SELECT Sal FROM Emp WHERE Deptno = 30)
AND Nvl(Comm, 0) IN (SELECT Nvl(Comm, 0) FROM Emp WHERE Deptno = 30);中
只要 0 in(SELECT Nvl(Comm, 0) FROM Emp WHERE Deptno = 30); 时,会把所有的数据都显示出来的
--刚才马虎了,没有注意子查询中的where条件。
--这两条语句不等价,比如在表中存在下面的数据:
sal comm deptno
1 A 30
2 B 30
1 B 29
2 A 28那么成对查询得到的结果是2条,不成对查询得到的结果是4条。成对查询例句:
WITH EMP AS
(
SELECT 1 SAL, 'A' COMM, 30 DEPTNO FROM DUAL UNION ALL
SELECT 2 , 'B' , 30 FROM DUAL UNION ALL
SELECT 1 , 'B' , 29 FROM DUAL UNION ALL
SELECT 2 , 'A' , 28 DEPT FROM DUAL
)
SELECT *
FROM Emp
WHERE (Sal, Nvl(Comm, 0)) IN
(SELECT Sal, Nvl(Comm, 0) FROM Emp WHERE Deptno = 30);不成对查询例句:
WITH EMP AS
(
SELECT 1 SAL, 'A' COMM, 30 DEPTNO FROM DUAL UNION ALL
SELECT 1 , 'B' , 29 FROM DUAL UNION ALL
SELECT 2 , 'A' , 28 DEPT FROM DUAL
)
SELECT *
FROM Emp
WHERE (Sal, Nvl(Comm, 0)) IN
(SELECT Sal, Nvl(Comm, 0) FROM Emp WHERE Deptno = 30);
我在我的资料库中根据两个SQL查询出来是一样的结果,从楼主给的书上的图片来看,第二个SQL多了的那条记录是EMPNO=7844的记录,comm为0,但是我的资料库中也存在这样的数据,可是我查询出来就是都是一样的结果!
从根本来说,两个SQL我认为是一样的!
详细的测试如我1楼和5楼的分析。
WITH EMP AS
(
SELECT 1 SAL, 'A' COMM, 30 DEPTNO FROM DUAL UNION ALL
SELECT 2 , 'B' , 30 FROM DUAL UNION ALL
SELECT 1 , 'B' , 29 FROM DUAL UNION ALL
SELECT 2 , 'A' , 28 DEPT FROM DUAL
)
SELECT *
FROM Emp
WHERE (Sal, Nvl(Comm, 0)) IN
(SELECT Sal, Nvl(Comm, 0) FROM Emp WHERE Deptno = 30);
改为这样
WITH EMP AS
(
SELECT 1 SAL, 'A' COMM, 30 DEPTNO FROM DUAL UNION ALL
SELECT 2 , 'B' , 30 FROM DUAL UNION ALL
SELECT 1 , 'B' , 29 FROM DUAL UNION ALL
SELECT 2 , 'A' , 28 DEPT FROM DUAL
)
SELECT *
FROM Emp
WHERE Sal IN (SELECT Sal FROM Emp WHERE Deptno = 30)
AND Nvl(Comm, -1) IN (SELECT Nvl(Comm, -1) FROM Emp WHERE Deptno = 30);
非成对比较:
WITH EMP AS
(
SELECT 1 SAL, 'A' COMM, 30 DEPTNO FROM DUAL UNION ALL
SELECT 2 , 'B' , 30 FROM DUAL UNION ALL
SELECT 1 , 'B' , 29 FROM DUAL UNION ALL
SELECT 2 , 'A' , 28 DEPT FROM DUAL
)
SELECT *
FROM Emp
WHERE Sal IN (SELECT Sal FROM Emp WHERE Deptno = 30)
AND Nvl(Comm, -1) IN (SELECT Nvl(Comm, -1) FROM Emp WHERE Deptno = 30);实际上相当于
WITH EMP AS
(
SELECT 1 SAL, 'A' COMM, 30 DEPTNO FROM DUAL UNION ALL
SELECT 2 , 'B' , 30 FROM DUAL UNION ALL
SELECT 1 , 'B' , 29 FROM DUAL UNION ALL
SELECT 2 , 'A' , 28 DEPT FROM DUAL
)
SELECT *
FROM Emp
WHERE Nvl(Comm, -1) IN ('A','B')
and Sal in (1,2);
这样就比较好理解了,当 Nvl(Comm, -1)为A的时候Sal可以为1,2
当Nvl(Comm, -1)为B的时候Sal也可以为1,2所以查询出来的结果是4条记录,而成对查询结果为2条记录
我改的这段,在看看你上传的代码就知道了!跟nvl(……)没关系,这是我从书上的例子直接copy上去的! WHERE Sal IN (SELECT Sal FROM Emp WHERE Deptno = 30)
AND Nvl(Comm, -1) IN (SELECT Nvl(Comm, -1) FROM Emp WHERE Deptno = 30);