oracle – 在没有ROWNUM的情况下选择前N行？

我希望你能帮我做作业:)

我们需要构建一个输出前N名最佳薪酬员工的查询.

我的版本完美无缺.
例如前三名：

SELECT name,salary
FROM staff
WHERE salary IN ( SELECT * 
                  FROM ( SELECT salary
                         FROM staff 
                         ORDER BY salary DESC ) 
                  WHERE ROWNUM <= 3 )
ORDER BY salary DESC
;

请注意,这将输出位于前3名且薪水相同的员工.

1: Mike,4080
2: Steve,2800
2: Susan,2800
2: Jack,2800
3: Chloe,1400

但现在我们的老师不允许我们使用ROWNUM.
我搜索得很远,没有找到任何可用的东西.

贾斯汀洞穴的暗示,这是我的第二个解决方案.

首先我试过这个：

SELECT name,salary,( rank() OVER ( ORDER BY salary DESC ) ) as myorder
FROM staff
WHERE myorder <= 3
;

错误消息是：“myorder：invalid identifier”

感谢DCookie,现在很清楚：

“[…] Analytics are applied AFTER
the where clause is evaluated,which
is why you get the error that myorder
is an invalid identifier.”

包装SELECT解决了这个问题：

SELECT *
FROM ( SELECT name,rank() OVER ( ORDER BY salary DESC ) as myorder FROM staff )
WHERE myorder <= 3
;

我的老师再次罢工,不允许这种奇特的分析功能.

来自@Justin Caves的第三个解决方案.

“If analytic functions are also
disallowed,the other option I could
imagine– one that you would never,
ever,ever actually write in practice,
would be something like”

SELECT name,salary
  FROM staff s1
 WHERE (SELECT COUNT(*)
          FROM staff s2
         WHERE s1.salary < s2.salary) <= 3

因为这是作业,提示而不是答案.您将需要使用分析函数. ROW_NUMBER,RANK或DENSE_RANK可以根据您想要处理关系的方式工作.

如果也不允许分析函数,我可以想象的另一个选项 – 你永远不会,实际上在实践中写的那个,就像是

SELECT name,salary
  FROM staff s1
 WHERE (SELECT COUNT(*)
          FROM staff s2
         WHERE s1.salary < s2.salary) <= 3

关于性能,我不会依赖查询计划中的COST编号 – 这只是一个估计,通常不可能比较不同sql语句的计划之间的成本.你可以更好地查看查询实际执行的一致获取次数,并考虑查询性能随着表中行数的增加而缩放的情况.第三个选项的效率远远低于其他两个选项,因为它需要两次扫描STAFF表.

我没有你的STAFF表,所以我将使用SCOTT模式中的EMP表

分析函数解决方案实际上与ROWNUM解决方案一样进行7次一致性获取

Wrote file afiedt.buf
 
  1  select ename,sal
  2    from( select ename,3                 sal,4                 rank() over (order by sal) rnk
  5            from emp )
  6*  where rnk <= 3
sql> /
 
ENAME             SAL
---------- ----------
smith             800
SM0               950
ADAMS            1110
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3291446077
 
--------------------------------------------------------------------------------
-
| Id  | Operation                | Name | Rows  | Bytes | Cost (%cpu)| Time
|
--------------------------------------------------------------------------------
-
|   0 | SELECT STATEMENT         |      |    14 |   672 |     4  (25)| 00:00:01
|*  1 |  VIEW                    |      |    14 |   672 |     4  (25)| 00:00:01
|*  2 |   WINDOW SORT PUSHED RANK|      |    14 |   140 |     4  (25)| 00:00:01
|   3 |    TABLE ACCESS FULL     | EMP  |    14 |   140 |     3   (0)| 00:00:01
--------------------------------------------------------------------------------
-
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("RNK"<=3)
   2 - filter(RANK() OVER ( ORDER BY "SAL")<=3)
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        668  bytes sent via sql*Net to client
        524  bytes received via sql*Net from client
          2  sql*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          3  rows processed
 
sql> select ename,sal
  3            from emp
  4           order by sal )
  5   where rownum <= 3;
 
ENAME             SAL
---------- ----------
smith             800
SM0               950
ADAMS            1110
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1744961472
 
--------------------------------------------------------------------------------
| Id  | Operation               | Name | Rows  | Bytes | Cost (%cpu)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |     3 |   105 |     4  (25)| 00:00:01 |
|*  1 |  COUNT STOPKEY          |      |       |       |            |          |
|   2 |   VIEW                  |      |    14 |   490 |     4  (25)| 00:00:01 |
|*  3 |    SORT ORDER BY STOPKEY|      |    14 |   140 |     4  (25)| 00:00:01 |
|   4 |     TABLE ACCESS FULL   | EMP  |    14 |   140 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------
 
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(ROWNUM<=3)
   3 - filter(ROWNUM<=3)
 
 
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        668  bytes sent via sql*Net to client
        524  bytes received via sql*Net from client
          2  sql*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          3  rows processed

但是,COUNT(*)解决方案实际上会执行99次一致性获取,并且必须对表进行两次完整扫描,因此效率低10倍以上.随着表中行数的增加,它的扩展性会更差

sql> select ename,sal
  2    from emp e1
  3   where (select count(*) from emp e2 where e1.sal < e2.sal) <= 3;
 
ENAME             SAL
---------- ----------
JONES            2975
SCOTT            3000
KING             5000
FORD             3000
FOO
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2649664444
 
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%cpu)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    14 |   140 |    24   (0)| 00:00:01 |
|*  1 |  FILTER             |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL | EMP  |    14 |   140 |     3   (0)| 00:00:01 |
|   3 |   SORT AGGREGATE    |      |     1 |     4 |            |          |
|*  4 |    TABLE ACCESS FULL| EMP  |     1 |     4 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter( (SELECT COUNT(*) FROM "EMP" "E2" WHERE
              "E2"."SAL">:B1)<=3)
   4 - filter("E2"."SAL">:B1)
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         99  consistent gets
          0  physical reads
          0  redo size
        691  bytes sent via sql*Net to client
        524  bytes received via sql*Net from client
          2  sql*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed

oracle – 在没有ROWNUM的情况下选择前N行？

猜你在找的Oracle相关文章