Skip to content

Instantly share code, notes, and snippets.

@xtender

xtender/gist:11281007

Created April 25, 2014 07:42
Show Gist options
  • Save xtender/11281007 to your computer and use it in GitHub Desktop.
Save xtender/11281007 to your computer and use it in GitHub Desktop.
Download ZIP
habr_plans
Raw
gistfile1.sql
SQL> -- 2 max:
SQL> select/*+ findme 1 */ * from
2 (
3 select c.*
4 , max(c.oper_id) over (partition by c.client_id) as m_o/*max_operation*/
5 from
6 (
7 select t.*
8 , max(t.amount) over (partition by t.client_id) as m_a/*max_amount*/
9 from habr_test_table t
10 ) c
11 where c.m_a = c.amount
12 ) where m_o = oper_id;
Plan hash value: 673711813
----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp|
----------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:01.02 | 392 | 1151 | 775 | | | | |
|* 1 | VIEW | | 1 | 99720 | 10 |00:00:01.02 | 392 | 1151 | 775 | | | | |
| 2 | WINDOW BUFFER | | 1 | 99720 | 20 |00:00:01.02 | 392 | 1151 | 775 | 2048 | 2048 | 2048 (0)| |
|* 3 | VIEW | | 1 | 99720 | 20 |00:00:01.02 | 392 | 1151 | 775 | | | | |
| 4 | WINDOW SORT | | 1 | 99720 | 100K|00:00:00.97 | 392 | 1151 | 775 | 3519K| 815K| 1179K (1)| 5120 |
| 5 | TABLE ACCESS FULL| HABR_TEST_TABLE | 1 | 99720 | 100K|00:00:00.08 | 381 | 376 | 0 | | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("M_O"="OPER_ID")
3 - filter("C"."M_A"="C"."AMOUNT")
SQL> -- 2 dense_rank:
SQL> select/*+ findme 2 */ * from
2 (
3 select c.*
4 , dense_rank() over (partition by c.client_id order by c.oper_id desc) as m_o/*max_operation*/
5 from
6 (
7 select t.*
8 , dense_rank() over (partition by t.client_id order by t.amount desc) as m_a/*max_amount*/
9 from habr_test_table t
10 ) c
11 where c.m_a = 1
12 ) where m_o = 1;
Plan hash value: 331359864
---------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp|
---------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.87 | 394 | 387 | 11 | | | | |
|* 1 | VIEW | | 1 | 99720 | 10 |00:00:00.87 | 394 | 387 | 11 | | | | |
|* 2 | WINDOW SORT PUSHED RANK | | 1 | 99720 | 20 |00:00:00.87 | 394 | 387 | 11 | 2048 | 2048 | 2048 (0)| |
|* 3 | VIEW | | 1 | 99720 | 20 |00:00:00.87 | 394 | 387 | 11 | | | | |
|* 4 | WINDOW SORT PUSHED RANK| | 1 | 99720 | 30 |00:00:00.87 | 394 | 387 | 11 | 92160 | 92160 | 1123K (2)| 1024 |
| 5 | TABLE ACCESS FULL | HABR_TEST_TABLE | 1 | 99720 | 100K|00:00:00.09 | 381 | 376 | 0 | | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("M_O"=1)
2 - filter(DENSE_RANK() OVER ( PARTITION BY "C"."CLIENT_ID" ORDER BY INTERNAL_FUNCTION("C"."OPER_ID") DESC )<=1)
3 - filter("C"."M_A"=1)
4 - filter(DENSE_RANK() OVER ( PARTITION BY "T"."CLIENT_ID" ORDER BY INTERNAL_FUNCTION("T"."AMOUNT") DESC )<=1)
SQL> -- 2 max + order by:
SQL> select/*+ findme 3 */ * from
2 (
3 select c.*
4 , max(c.oper_id) over (partition by c.client_id) as m_o/*max_operation*/
5 from
6 (
7 select t.*
8 , max(t.amount) over (partition by t.client_id) as m_a/*max_amount*/
9 from habr_test_table t
10 order by t.client_id
11 ) c
12 where c.m_a = c.amount
13 ) where m_o = oper_id;
Plan hash value: 673711813
----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp|
----------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.93 | 394 | 1284 | 908 | | | | |
|* 1 | VIEW | | 1 | 99720 | 10 |00:00:00.93 | 394 | 1284 | 908 | | | | |
| 2 | WINDOW BUFFER | | 1 | 99720 | 20 |00:00:00.93 | 394 | 1284 | 908 | 2048 | 2048 | 2048 (0)| |
|* 3 | VIEW | | 1 | 99720 | 20 |00:00:00.93 | 394 | 1284 | 908 | | | | |
| 4 | WINDOW SORT | | 1 | 99720 | 100K|00:00:00.88 | 394 | 1284 | 908 | 3519K| 815K| 1123K (2)| 4096 |
| 5 | TABLE ACCESS FULL| HABR_TEST_TABLE | 1 | 99720 | 100K|00:00:00.07 | 381 | 376 | 0 | | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("M_O"="OPER_ID")
3 - filter("C"."M_A"="C"."AMOUNT")
SQL> -- 1 row_number:
SQL> select/*+ findme 4 */ * from
2 (
3 select t.*
4 , row_number() over (partition by t.client_id order by t.amount desc, t.oper_id desc) as rn
5 from habr_test_table t
6 ) where rn = 1;
Plan hash value: 19323224
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp|
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.76 | 394 | 387 | 11 | | | | |
|* 1 | VIEW | | 1 | 99720 | 10 |00:00:00.76 | 394 | 387 | 11 | | | | |
|* 2 | WINDOW SORT PUSHED RANK| | 1 | 99720 | 20 |00:00:00.76 | 394 | 387 | 11 | 92160 | 92160 | 1123K (2)| 1024 |
| 3 | TABLE ACCESS FULL | HABR_TEST_TABLE | 1 | 99720 | 100K|00:00:00.07 | 381 | 376 | 0 | | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN"=1)
2 - filter(ROW_NUMBER() OVER ( PARTITION BY "T"."CLIENT_ID" ORDER BY INTERNAL_FUNCTION("T"."AMOUNT") DESC ,INTERNAL_FUNCTION("T"."OPER_ID") DESC
)<=1)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment