Oracle 排序规则

转载

mob604756fadec0 2016-07-01 19:00:00

<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行。