Please review following SQL and it's execution plan. Why am I seeing 2 WINDOW SORT operations even though in sql . analytic function "row_number" has been used only once?
Also, In step 3 of the plan, why "bytes" goes from 35 GB(4th step) to 88GB when row count remains the same. In fact , since I'm selecting just 1st row , both row count as well as "bytes" should have gone down. Shouldn't it?
SELECT orddtl.ord_dtl_key, orddtl.ld_nbr, orddtl.actv_flg,
orddtl.ord_nbr
FROM (SELECT /*+ parallel(od, 8) parallel(sc,8) */ od.ord_dtl_key, od.ld_nbr, od.actv_flg,
od.ord_nbr,
ROW_NUMBER () OVER (PARTITION BY od.ord_dtl_key, od.START_TS ORDER BY sc.START_TS DESC)
rownbr
FROM edw.order_detail od LEFT OUTER JOIN edw.srvc_code sc
ON ( sc.srvc_cd_key = od.srvc_cd_key
AND od.part_nbr = sc.part_nbr
AND od.item_cre_dt >= sc.START_TS
AND od.item_cre_dt < sc.END_TS
)
WHERE od.part_nbr = 11 ) orddtl
WHERE orddtl.rownbr = 1;
Execution Plan
---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 88M| 121G| | 2353K (65)| 00:33:07 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 88M| 121G| | 2353K (65)| 00:33:07 | | | Q1,02 | P->S | QC (RAND) |
|* 3 | VIEW | | 88M| 121G| | 2353K (65)| 00:33:07 | | | Q1,02 | PCWP | |
|* 4 | WINDOW SORT PUSHED RANK | | 88M| 35G| 75G| 2353K (65)| 00:33:07 | | | Q1,02 | PCWP | |
| 5 | PX RECEIVE | | 88M| 35G| | 2353K (65)| 00:33:07 | | | Q1,02 | PCWP | |
| 6 | PX SEND HASH | :TQ10001 | 88M| 35G| | 2353K (65)| 00:33:07 | | | Q1,01 | P->P | HASH |
|* 7 | WINDOW CHILD PUSHED RANK| | 88M| 35G| | 2353K (65)| 00:33:07 | | | Q1,01 | PCWP | |
|* 8 | HASH JOIN RIGHT OUTER | | 88M| 35G| | 1610K (92)| 00:22:39 | | | Q1,01 | PCWP | |
| 9 | PX RECEIVE | | 1133K| 32M| | 1197 (20)| 00:00:02 | | | Q1,01 | PCWP | |
| 10 | PX SEND BROADCAST | :TQ10000 | 1133K| 32M| | 1197 (20)| 00:00:02 | | | Q1,00 | P->P | BROADCAST |
| 11 | PX BLOCK ITERATOR | | 1133K| 32M| | 1197 (20)| 00:00:02 | KEY | KEY | Q1,00 | PCWC | |
| 12 | TABLE ACCESS FULL | SRVC_CODE | 1133K| 32M| | 1197 (20)| 00:00:02 | 1 | 1 | Q1,00 | PCWP | |
| 13 | PX BLOCK ITERATOR | | 88M| 32G| | 188K (27)| 00:02:39 | KEY | KEY | Q1,01 | PCWC | |
| 14 | TABLE ACCESS FULL | ORDER_DETAIL | 88M| 32G| | 188K (27)| 00:02:39 | 1 | 1 | Q1,01 | PCWP | |
---------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("orddtl"."rownbr"=1)
4 - filter(ROW_NUMBER() OVER ( PARTITION BY "od"."ORD_DTL_KEY","od"."START_TS" ORDER BY INTERNAL_FUNCTION("SC"."START_TS"(+))
DESC )<=1)
7 - filter(ROW_NUMBER() OVER ( PARTITION BY "od"."ORD_DTL_KEY","od"."START_TS" ORDER BY INTERNAL_FUNCTION("SC"."START_TS"(+))
DESC )<=1)
8 - access("od"."part_nbr"="SC"."part_nbr"(+) AND "SC"."SRVC_CD_KEY"(+)="od"."SRVC_CD_KEY")
filter("od"."ITEM_CRE_DT"<"SC"."END_TS"(+) AND "od"."ITEM_CRE_DT">="SC"."START_TS"(+))