<pre name="code" class="html">SQL> select * from t1 where id>=1 and id<=20;
ID A1 A2 A3
---------- ---------- ---------- ----------
1 1 1 a1
2 2 2 a2
3 3 3 a3
4 4 4 a4
5 5 5 a5
6 6 6 a6
7 7 7 a7
8 8 8 a8
9 9 9 a9
10 10 10 a10
11 11 11 a11
12 12 12 a12
13 13 13 a13
14 14 14 a14
15 15 15 a15
16 16 16 a16
17 17 17 a17
18 18 18 a18
19 19 19 a19
20 20 20 a20
已选择20行。
SQL> explain plan for select id from t1 where rownum<20;
已解释。
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3581814200
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19 | 209 | 1 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | INDEX FULL SCAN| SYS_C0022200 | 19 | 209 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<20)
已选择14行。
INDEX FULL SCAN 返回数据有序:
SQL> select id from t1 where rownum<20;
ID
----------
1
10
100
1000
10000
1001
1002
1003
1004
1005
1006
1007
1008
1009
101
1010
1011
1012
1013
已选择19行。
不走索引,随机读:
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3836375644
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19 | 836 | 2 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS FULL| T1 | 19 | 836 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------
1 - filter(ROWNUM<20)
已选择14行。
SQL> select * from t1 where rownum<20;
ID A1 A2 A3
---------- ---------- ---------- ----------
495 495 495 a495
496 496 496 a496
497 497 497 a497
498 498 498 a498
499 499 499 a499
500 500 500 a500
501 501 501 a501
502 502 502 a502
503 503 503 a503
504 504 504 a504
505 505 505 a505
ID A1 A2 A3
---------- ---------- ---------- ----------
506 506 506 a506
507 507 507 a507
508 508 508 a508
509 509 509 a509
510 510 510 a510
511 511 511 a511
512 512 512 a512
513 513 513 a513
已选择19行
排序规则:
SQL> select id from t1 where rownum<20;
ID
----------
1
10
100
1000
10000
1001
1002
1003
1004
1005
1006
1007
1008
1009
101
1010
1011
1012
1013
已选择19行。
SQL> select * from ( select id from t1 where rownum<20) order by id;
ID
----------
1
10
100
1000
10000
1001
1002
1003
1004
1005
1006
1007
1008
1009
101
1010
1011
1012
1013
已选择19行。
SQL> select * from (select * from t1 order by id ) where rownum<20;
ID A1 A2 A3
---------- ---------- ---------- ----------
1 1 1 a1
10 10 10 a10
100 100 100 a100
1000 1000 1000 a1000
10000 10000 10000 a10000
1001 1001 1001 a1001
1002 1002 1002 a1002
1003 1003 1003 a1003
1004 1004 1004 a1004
1005 1005 1005 a1005
1006 1006 1006 a1006
1007 1007 1007 a1007
1008 1008 1008 a1008
1009 1009 1009 a1009
101 101 101 a101
1010 1010 1010 a1010
1011 1011 1011 a1011
1012 1012 1012 a1012
1013 1013 1013 a1013
已选择19行。
SQL> select * from (select * from t1 where id<100 order by id) where rownum<20;
ID A1 A2 A3
---------- ---------- ---------- ----------
1 1 1 a1
10 10 10 a10
11 11 11 a11
12 12 12 a12
13 13 13 a13
14 14 14 a14
15 15 15 a15
16 16 16 a16
17 17 17 a17
18 18 18 a18
19 19 19 a19
2 2 2 a2
20 20 20 a20
21 21 21 a21
22 22 22 a22
23 23 23 a23
24 24 24 a24
25 25 25 a25
26 26 26 a26
已选择19行。
