【Oracle】-【ROWNUM与索引】-索引对ROWNUM检索的影响
【Oracle】-【ROWNUM与索引】-索引对ROWNUM检索的影响
select ename, sal from emp where rownum<=10 order by sal desc; 和 select ename, sal from ( select ename, sal from emp order by sal desc) where rownum<=10;
是否相同?
第一个SQL是先找到ROWNUM<10的记录,然后排序。
第二个SQL是先ORDER BY排序,再找ROWNUM<10的记录。
因此两种查询得到的答案不同,当然有时也会碰巧相同。
另外,如果表有索引,那么对于第二个SQL,可以从后面的记录开始读,避免排序。对于这个问题我做了实验:
create table t as select * from dba_objects; create table t2 as select * from dba_objects; create index t2_i on t2(object_id); SQL> select * from (select owner, object_name, object_id from t order by object_id desc) where rownum<10; 9 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3299198703 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 9 | 864 | | 1189 (1)| 00:00:15 | |* 1 | COUNT STOPKEY | | | | | | | | 2 | VIEW | | 47308 | 4435K| | 1189 (1)| 00:00:15 | |* 3 | SORT ORDER BY STOPKEY| | 47308 | 4435K| 9M| 1189 (1)| 00:00:15 | | 4 | TABLE ACCESS FULL | T | 47308 | 4435K| | 150 (1)| 00:00:02 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<10) 3 - filter(ROWNUM<10) Note ----- - dynamic sampling used for this statement Statistics ---------------------------------------------------------- 7 recursive calls 0 db block gets 793 consistent gets 0 physical reads 0 redo size 878 bytes sent via SQL*Net to client 492 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 9 rows processed SQL> select * from ( select owner, object_name, object_id from t2 order by object_id desc) where rownum < 10; 9 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 98068844 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 9 | 864 | | 1164 (1)| 00:00:14 | |* 1 | COUNT STOPKEY | | | | | | | | 2 | VIEW | | 46110 | 4322K| | 1164 (1)| 00 :00:14 | |* 3 | SORT ORDER BY STOPKEY| | 46110 | 4322K| 9848K| 1164 (1)| 00:00:14 | | 4 | TABLE ACCESS FULL | T2 | 46110 | 4322K| | 150 (1)| 00:00:02 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<10) 3 - filter(ROWNUM<10) Note ----- - dynamic sampling used for this statement Statistics ---------------------------------------------------------- 7 recursive calls 0 db block gets 791 consistent gets 0 physical reads 0 redo size 878 bytes sent via SQL*Net to client 492 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 9 rows processed