SORT (UNIQUE STOPKEY)/ SORT GROUP BY STOPKEY
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 使用索引優化StopKey索引優化TopK
- 資料庫的sort group by和hash group by資料庫
- oracle 分頁優化（stopkey）Oracle優化TopK
- 去重函式unique，sort，erase的應用函式
- 關於COUNT STOPKEY的工作機制TopK
- stopkey對索引掃描的影響測試TopK索引
- JavaScript sort()JavaScript
- 排序sort排序
- Sort排序排序
- sort命令
- Queue Sort
- Exceeded memory limit for $group, but didn't allow external sort. Pass allowDiskMIT
- 線性時間的排序 - Decision Tree Model & Counting Sort & Radix Sort & Bucket Sort排序
- golang sort.Sort () 排序演算法學習Golang排序演算法
- 關於stable_sort()和sort()的區別：
- sort_area_retained_size與sort_area_sizeAI
- Sort Array By Parity
- Oracle Sort JoinOracle
- sort 命令使用
- sort()函式函式
- [20230103]COUNT STOPKEY operation.txtTopK
- _sort_elimination_cost_radit和sort排序排序
- Collections sort()排序方法排序
- 堆排序 Heap Sort排序
- Elasticsearch script sort 排序Elasticsearch排序
- Java Arrays.sort()Java
- 桶排序bucket sort排序
- Algorithm in Javascript Bubble SortGoJavaScript
- Algorithm in Javascript Bucket SortGoJavaScript
- 聊聊sort函式函式
- 快速排序(Quick Sort)排序UI
- 【Shell】sort 筆記筆記
- Linux sort命令Linux
- 希爾排序（Shell Sort）排序
- 快速排序 (Quick Sort)排序UI
- 11G的SORT GROUP BY NOSORT導致錯誤執行計劃
- Linux shell工具grep awk cut sort uniq sort 使用小結Linux
- Leetcode Sort ColorsLeetCode