Oracle 排序规则

来源：互联网发布：软件开发资质等级编辑：程序博客网时间：2024/06/04 23:19
<pre name="code" class="html">SQL> select * from t1 where id>=1 and id<=20;ID   A1      A2 A3---------- ---------- ---------- ----------1   1      1  a12   2      2  a23   3      3  a34   4      4  a45   5      5  a56   6      6  a67   7      7  a78   8      8  a89   9      9  a910   10      10 a1011   11      11 a1112   12      12 a1213   13      13 a1314   14      14 a1415   15      15 a1516   16      16 a1617   17      17 a1718   18      18 a1819   19      19 a1920   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----------1101001000100001001100210031004100510061007100810091011010101110121013已选择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 a495496   496      496 a496497   497      497 a497498   498      498 a498499   499      499 a499500   500      500 a500501   501      501 a501502   502      502 a502503   503      503 a503504   504      504 a504505   505      505 a505ID   A1      A2 A3---------- ---------- ---------- ----------506   506      506 a506507   507      507 a507508   508      508 a508509   509      509 a509510   510      510 a510511   511      511 a511512   512      512 a512513   513      513 a513已选择19行排序规则:SQL> select id from t1 where rownum<20;ID----------1101001000100001001100210031004100510061007100810091011010101110121013已选择19行。SQL> select * from ( select id from t1 where rownum<20) order by id;ID----------1101001000100001001100210031004100510061007100810091011010101110121013已选择19行。SQL> select * from (select * from t1 order by id ) where rownum<20;ID   A1      A2 A3---------- ---------- ---------- ----------1   1      1  a110   10      10 a10100   100      100 a1001000   1000       1000 a100010000   10000      10000 a100001001   1001       1001 a10011002   1002       1002 a10021003   1003       1003 a10031004   1004       1004 a10041005   1005       1005 a10051006   1006       1006 a10061007   1007       1007 a10071008   1008       1008 a10081009   1009       1009 a1009101   101      101 a1011010   1010       1010 a10101011   1011       1011 a10111012   1012       1012 a10121013   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  a110   10      10 a1011   11      11 a1112   12      12 a1213   13      13 a1314   14      14 a1415   15      15 a1516   16      16 a1617   17      17 a1718   18      18 a1819   19      19 a192   2      2  a220   20      20 a2021   21      21 a2122   22      22 a2223   23      23 a2324   24      24 a2425   25      25 a2526   26      26 a26已选择19行。
0 0