1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107
| SQL> create table t (
2 id, c,
3 CONSTRAINT PK PRIMARY KEY (ID) ENABLE
4 ) as
5 select level, dbms_random.string('u',10)
6 from dual
7 connect by level <= 100000
8 /
Table created.
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade=>true);
PL/SQL procedure successfully completed.
SQL>
SQL> set autot trace
SQL>
SQL> select *
2 from (select t.*, rownum as rn
3 from (select t.* from t order by id desc) t
4 where rownum < 101
5 )
6 where rn >= 50
7 /
51 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1486502395
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 198K| 3 (0)| 00:00:01 |
|* 1 | VIEW | | 100 | 198K| 3 (0)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 100 | 196K| 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 100K| 1562K| 3 (0)| 00:00:01 |
| 5 | INDEX FULL SCAN DESCENDING| PK | 100 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=50)
2 - filter(ROWNUM<101)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
11 consistent gets
0 physical reads
0 redo size
2327 bytes sent via SQL*Net to client
553 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
51 rows processed
SQL>
SQL> select t.*
2 from (select t.*, rownum as rn from t order by id desc) t
3 where rn between 50 and 100
4 /
51 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 501813784
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 193M| 500 (1)| 00:00:07 |
|* 1 | VIEW | | 100K| 193M| 500 (1)| 00:00:07 |
| 2 | COUNT | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| T | 100K| 1562K| 500 (1)| 00:00:07 |
| 4 | INDEX FULL SCAN DESCENDING| PK | 100K| | 210 (1)| 00:00:03 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN"<=100 AND "RN">=50)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
507 consistent gets
0 physical reads
0 redo size
2327 bytes sent via SQL*Net to client
553 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
51 rows processed
SQL> |
Partager