Description
Removes duplicates from sorted list of rows. Number of rows returned is restricted using the ROWNUM pseudo-column
SQL> CREATE TABLE t1 (c1 NUMBER);
Table created.
SQL> set autot traceonly
SQL> SELECT * FROM
2 (
3 SELECT DISTINCT c1 FROM t1
4 )
5 WHERE ROWNUM < 10;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 2221909342
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 3 (34)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 1 | 13 | 3 (34)| 00:00:01 |
|* 3 | SORT GROUP BY STOPKEY| | 1 | 13 | 3 (34)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T1 | 1 | 13 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<10)
3 - filter(ROWNUM<10)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
319 bytes sent via SQL*Net to client
481 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> ALTER SESSION SET "_gby_hash_aggregation_enabled" = FALSE;
Session altered.
SQL> SELECT * FROM
2 (
3 SELECT DISTINCT c1 FROM t1
4 )
5 WHERE ROWNUM < 10;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3413338077
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 3 (34)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 1 | 13 | 3 (34)| 00:00:01 |
|* 3 | SORT UNIQUE STOPKEY| | 1 | 13 | 3 (34)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T1 | 1 | 13 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<10)
3 - filter(ROWNUM<10)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
319 bytes sent via SQL*Net to client
481 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> ALTER SESSION SET "_gby_hash_aggregation_enabled" = true;
Session altered.
SQL> SELECT * FROM
2 (
3 SELECT DISTINCT c1 FROM t1
4 )
5 WHERE ROWNUM < 10;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 2221909342
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 3 (34)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 1 | 13 | 3 (34)| 00:00:01 |
|* 3 | SORT GROUP BY STOPKEY| | 1 | 13 | 3 (34)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T1 | 1 | 13 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<10)
3 - filter(ROWNUM<10)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
319 bytes sent via SQL*Net to client
481 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> create table test_stopkey as
SQL> select * from dba_objects;
54828 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2960989055
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 52295 | 9039K| 160 (5)| 00:00:02 |
| 1 | VIEW | DBA_OBJECTS | 52295 | 9039K| 160 (5)| 00:00:02 |
| 2 | UNION-ALL | | | | | |
|* 3 | FILTER | | | | | |
|* 4 | HASH JOIN | | 55438 | 5089K| 157 (6)| 00:00:02 |
| 5 | TABLE ACCESS FULL | USER$ | 66 | 990 | 3 (0)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | OBJ$ | 55438 | 4276K| 152 (4)| 00:00:02 |
|* 7 | TABLE ACCESS BY INDEX ROWID| IND$ | 1 | 8 | 1 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | I_IND1 | 1 | | 1 (0)| 00:00:01 |
| 9 | NESTED LOOPS | | 2 | 76 | 3 (0)| 00:00:01 |
| 10 | TABLE ACCESS FULL | LINK$ | 2 | 46 | 2 (0)| 00:00:01 |
| 11 | TABLE ACCESS CLUSTER | USER$ | 1 | 15 | 1 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | I_USER# | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("O"."TYPE#"<>1 AND "O"."TYPE#"<>10 OR "O"."TYPE#"=1 AND (SELECT 1 FROM
"SYS"."IND$" "I" WHERE "I"."OBJ#"=:B1 AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR
"I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))=1)
4 - access("O"."OWNER#"="U"."USER#")
6 - filter("O"."NAME"<>'_NEXT_OBJECT' AND "O"."NAME"<>'_default_auditing_options_'
AND "O"."LINKNAME" IS NULL AND BITAND("O"."FLAGS",128)=0)
7 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR
"I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9)
8 - access("I"."OBJ#"=:B1)
12 - access("L"."OWNER#"="U"."USER#")
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
10516 consistent gets
0 physical reads
0 redo size
2777255 bytes sent via SQL*Net to client
40697 bytes received via SQL*Net from client
3657 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
54828 rows processed
SQL> create table test_stopkey as select * from dba_objects;
Table created.
SQL> insert into test_stopkey select * from dba_objects;
commit
54829 rows created.
Execution Plan
----------------------------------------------------------
Plan hash value: 2960989055
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 52295 | 9039K| 160 (5)| 00:00:02 |
| 1 | VIEW | DBA_OBJECTS | 52295 | 9039K| 160 (5)| 00:00:02 |
| 2 | UNION-ALL | | | | | |
|* 3 | FILTER | | | | | |
|* 4 | HASH JOIN | | 55438 | 5089K| 157 (6)| 00:00:02 |
| 5 | TABLE ACCESS FULL | USER$ | 66 | 990 | 3 (0)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | OBJ$ | 55438 | 4276K| 152 (4)| 00:00:02 |
|* 7 | TABLE ACCESS BY INDEX ROWID| IND$ | 1 | 8 | 1 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | I_IND1 | 1 | | 1 (0)| 00:00:01 |
| 9 | NESTED LOOPS | | 2 | 76 | 3 (0)| 00:00:01 |
| 10 | TABLE ACCESS FULL | LINK$ | 2 | 46 | 2 (0)| 00:00:01 |
| 11 | TABLE ACCESS CLUSTER | USER$ | 1 | 15 | 1 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | I_USER# | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("O"."TYPE#"<>1 AND "O"."TYPE#"<>10 OR "O"."TYPE#"=1 AND (SELECT 1 FROM
"SYS"."IND$" "I" WHERE "I"."OBJ#"=:B1 AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR
"I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))=1)
4 - access("O"."OWNER#"="U"."USER#")
6 - filter("O"."NAME"<>'_NEXT_OBJECT' AND "O"."NAME"<>'_default_auditing_options_'
AND "O"."LINKNAME" IS NULL AND BITAND("O"."FLAGS",128)=0)
7 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR
"I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9)
8 - access("I"."OBJ#"=:B1)
12 - access("L"."OWNER#"="U"."USER#")
Statistics
----------------------------------------------------------
681 recursive calls
4308 db block gets
8215 consistent gets
0 physical reads
6205852 redo size
828 bytes sent via SQL*Net to client
743 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
54829 rows processed
SQL> SQL> 2
SQL>
SQL>
SQL> select count(*) from test_stopkey;
Execution Plan
----------------------------------------------------------
Plan hash value: 184875565
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 337 (1)| 00:00:05 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TEST_STOPKEY | 108K| 337 (1)| 00:00:05 |
---------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
28 recursive calls
0 db block gets
1592 consistent gets
759 physical reads
0 redo size
517 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select * from (select rownum rn,t.* from test_stopkey t) where rn > 1000 and rn <= 2000;
1000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 594950436
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 108K| 19M| 340 (2)| 00:00:05 |
|* 1 | VIEW | | 108K| 19M| 340 (2)| 00:00:05 |
| 2 | COUNT | | | | | |
| 3 | TABLE ACCESS FULL| TEST_STOPKEY | 108K| 18M| 340 (2)| 00:00:05 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN"<=2000 AND "RN">1000)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
1725 consistent gets
0 physical reads
0 redo size
57379 bytes sent via SQL*Net to client
1218 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1000 rows processed
SQL> select * from
2 (select rownum rn,t.* from test_stopkey t
3 where rownum <= 2000)
4 where rn > 1000;
1000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3382644055
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2000 | 371K| 11 (28)| 00:00:01 |
|* 1 | VIEW | | 2000 | 371K| 11 (28)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | TABLE ACCESS FULL| TEST_STOPKEY | 108K| 18M| 11 (28)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">1000)
2 - filter(ROWNUM<=2000)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
231 consistent gets
0 physical reads
0 redo size
57379 bytes sent via SQL*Net to client
1218 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1000 rows processed
SQL> select * from test_stopkey t where rownum < 5;
Execution Plan
----------------------------------------------------------
Plan hash value: 4076471010
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 708 | 5 (60)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS FULL| TEST_STOPKEY | 108K| 18M| 5 (60)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<5)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
141 consistent gets
0 physical reads
0 redo size
1578 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
SQL> select * from
2 (select rownum rn,t.* from test_stopkey t
3 where rownum <= 150000)
4 where rn > 149000;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3382644055
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 108K| 19M| 340 (2)| 00:00:05 |
|* 1 | VIEW | | 108K| 19M| 340 (2)| 00:00:05 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | TABLE ACCESS FULL| TEST_STOPKEY | 108K| 18M| 340 (2)| 00:00:05 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">149000)
2 - filter(ROWNUM<=150000)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
1658 consistent gets
0 physical reads
0 redo size
1199 bytes sent via SQL*Net to client
481 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
----end----
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/13750068/viewspace-734522/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/13750068/viewspace-734522/