大家好，
下面这两个sql语句的目的是一样的，他们得工作原理是不是也一样呢？？？谢谢啦
SELECT Empno, Ename, Job, Mgr, Hiredate, Sal
FROM
(SELECT Empno, Ename, Job, Mgr, Hiredate, Sal,
RANK() OVER
(ORDER BY SAL Desc NULLS LAST) AS Emp_Rank
FROM Emp
ORDER BY SAL Desc NULLS LAST)
WHERE Emp_Rankselect empno,ename,job,mgr,hiredate,sal
2from
3(select empno,ename,job,mgr,hiredate,sal
4 from
5 emp
6 order by nvl(sal,0) desc)
7where rownum create index emp_sal on emp(nvl(sal,0) desc);
Execution Plan
----------------------------------------------------------
Plan hash value: 1116118347
-----------------------------------------------------------------------------------------
| Id | Operation
| Name
| Rows
| Bytes | Cost (%CPU)| Time
|
-----------------------------------------------------------------------------------------
| 0| SELECT STATEMENT
|
| 5
| 305 | 2 (0) | 00:00:01 |
|*1 |COUNT STOPKEY
|
|
|
|
|
|
| 2| VIEW
|
| 5
| 305| 2 (0)| 00:00:01 |
| 3|TABLE ACCESS BY INDEX ROWID | EMP
|14 | 476 | 2 (0) | 00:00:01 |
| 4| INDEX FULL SCAN
| EMP_SAL | 5
|
| 1 (0) | 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<6)
Statistics
----------------------------------------------------------
1recursive calls
0db block gets
4consistent gets
0physical reads
0redo size
895bytes sent via SQL*Net to client
420bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
5rows processed
|
