1 Attachment(s)
Partition pruning not occuring
Hi Folks
Currently i have a query that runs for long
Code:
CREATE TABLE HRISHY1_943_690 PARALLEL 4 NOLOGGING AS
SELECT
SUM(POS_TXN_DTL_CUR.SCAN_QTY) a,XTRA_CARD.XTRA_CARD_NBR
FROM XTRA.XTRA_CARD XTRA_CARD,COMMON.V_POS_TXN_DTL_CUR POS_TXN_DTL_CUR, PRODUCT.SKU SKU
WHERE ( ( POS_TXN_DTL_CUR.DATE_DT BETWEEN TO_DATE('01/01/2006','MM/DD/YYYY')
AND TO_DATE('01/08/2006','MM/DD/YYYY') AND SKU.BRAND_NBR = 2453 OR SKU.SKU_NBR IN ( 339711,339716 ) )
AND POS_TXN_DTL_CUR.XTRA_CARD_NBR = XTRA_CARD.XTRA_CARD_NBR
AND SKU.SKU_NBR = POS_TXN_DTL_CUR.SKU_NBR ) and btch_cntl_nbr in
(Select btch_cntl_nbr from campaign_batch_control where cmpgn_id = 943 and dv_id = 690 and proc_end_dt IS NULL
AND NVL(hold_ind,'N') ='N')GROUP BY XTRA_CARD.XTRA_CARD_NBR
and the plan is
Code:
-----------------------
| Operation | Name | Rows | Bytes | Cost | Pstart| Pstop | |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
| CREATE TABLE STATEMENT | | 1 | 66 | 15447 | | || | |
| LOAD AS SELECT | | | | | | | | P->S | QC (RAND) |
| SORT GROUP BY | | 1 | 66 | 15447 | | | | PCWP | |
| NESTED LOOPS | | 1 | 66 | 15444 | | | | P->P | HASH |
| NESTED LOOPS | | 1 | 56 | 15443 | | | | PCWP | |
| MERGE JOIN CARTESIAN | | 1 | 28 | 2875 | | | | PCWP | |
| SORT UNIQUE | | | | | | | | S->P | BROADCAST |
| TABLE ACCESS BY INDEX ROWID | CAMPAIGN_BATCH_CONTROL | 1 | 22 | 3 | | || | |
| INDEX RANGE SCAN | CAMPAIGN_BATCH_CONTROL_PK | 1 | | 2 | | || | |
| BUFFER SORT | | 79M| 455M| 2872 | | | | PCWP | |
| INDEX FAST FULL SCAN | XTRA_CARD_PK | 79M| 455M| 1607 | | | | PCWP | |
| PARTITION RANGE ALL | | | | | 1 | 25 | | PCWP | |
| TABLE ACCESS BY LOCAL INDEX ROWID| POS_TXN_DTL | 1 | 28 | 3142 | 1 | 25 | | PCWP | |
| INDEX RANGE SCAN | PTD_CRD_IX | 306 | | 28 | 1 | 25 | | PCWP | |
| TABLE ACCESS BY INDEX ROWID | SKU | 1 | 10 | 1 | | | | PCWP | |
| INDEX UNIQUE SCAN | SKU_SKU_PK | 1 | | | | | | PCWP | |
-----------------------------------------------------------------------------------------------------------------
-----------------------
cate Information (identified by operation id):
----------------------------------------------
- filter("CAMPAIGN_BATCH_CONTROL"."PROC_END_DT" IS NULL AND NVL("CAMPAIGN_BATCH_CONTROL"."HOLD_IND",'N')='N')
- access("CAMPAIGN_BATCH_CONTROL"."CMPGN_ID"=943 AND "CAMPAIGN_BATCH_CONTROL"."DV_ID"=690)
- filter("PTD"."BTCH_CNTL_NBR"="CAMPAIGN_BATCH_CONTROL"."BTCH_CNTL_NBR")
- access("PTD"."XTRA_CARD_NBR"="XTRA_CARD"."XTRA_CARD_NBR")
- filter("PTD"."DATE_DT">=TO_DATE('2006-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "PTD"."DATE_DT"<=TO_DATE('2
1-08 00:00:00',
'yyyy-mm-dd hh24:mi:ss') AND "SKU"."BRAND_NBR"=2453 OR "SKU"."SKU_NBR"=339711 OR "SKU"."SKU_NBR"=339716)
- access("SKU"."SKU_NBR"="PTD"."SKU_NBR")
however if change the query to hard code the btch_cntl_nbr like this
Code:
explain plan for
CREATE TABLE HRISHY1_943_690 PARALLEL 4 NOLOGGING AS
SELECT
SUM(POS_TXN_DTL_CUR.SCAN_QTY) a,XTRA_CARD.XTRA_CARD_NBR
FROM XTRA.XTRA_CARD XTRA_CARD,COMMON.V_POS_TXN_DTL_CUR POS_TXN_DTL_CUR, PRODUCT.SKU SKU
WHERE ( ( POS_TXN_DTL_CUR.DATE_DT BETWEEN TO_DATE('01/01/2006','MM/DD/YYYY')
AND TO_DATE('01/08/2006','MM/DD/YYYY') AND SKU.BRAND_NBR = 2453 OR SKU.SKU_NBR IN ( 339711,339716 ) )
AND POS_TXN_DTL_CUR.XTRA_CARD_NBR = XTRA_CARD.XTRA_CARD_NBR
AND SKU.SKU_NBR = POS_TXN_DTL_CUR.SKU_NBR ) and btch_cntl_nbr in
(5748)GROUP BY XTRA_CARD.XTRA_CARD_NBR
/
the the plan changes like
Code:
Operation | Name | Rows | Bytes | Cost | Pstart| Pstop | TQ
T| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE STATEMENT | | 64 | 2752 | 34305 | | | | |
LOAD AS SELECT | | | | | | | 96,01 | QC (RAND) |
SORT GROUP BY | | 64 | 2752 | | | | 96,01 | |
SORT GROUP BY | | 64 | 2752 | | | | 96,00 | HASH |
CONCATENATION | | | | | | | 96,00 | |
NESTED LOOPS | | 1 | 43 | 61 | | | 96,00 | |
NESTED LOOPS | | 223 | 6021 | | | | 96,00 | |
TABLE ACCESS FULL | SKU | 1 | 10 | 1 | | | 96,00 | |
PARTITION RANGE ALL | | | | | 1 | 25 | 96,00 | |
TABLE ACCESS BY LOCAL INDEX ROWID| POS_TXN_DTL | 223 | 6021 | 4 | 1 | 25 | 96,00 | |
INDEX RANGE SCAN | PTD_SKU_DT_CRD_IX | | | | 1 | 25 | 96,00 | |
INDEX UNIQUE SCAN | XTRA_CARD_PK | 1 | 6 | 1 | | | 96,00 | |
NESTED LOOPS | | 1 | 43 | 61 | | | 96,00 | |
NESTED LOOPS | | 1 | 37 | 60 | | | 96,00 | |
TABLE ACCESS FULL | POS_TXN_DTL | 223 | 6021 | 4 | 25 | 25 | 96,00 | |
TABLE ACCESS BY INDEX ROWID | SKU | 1 | 10 | 1 | | | 96,00 | |
INDEX UNIQUE SCAN | SKU_SKU_PK | 408 | | | | | 96,00 | |
INDEX UNIQUE SCAN | XTRA_CARD_PK | 1 | 6 | 1 | | | 96,00 | |
------------------------------------------------------------------------------------------------------------------------------
te Information (identified by operation id):
--------------------------------------------
filter("SKU"."SKU_NBR"=339711 OR "SKU"."SKU_NBR"=339716)
filter("PTD"."BTCH_CNTL_NBR"=5748)
access("SKU"."SKU_NBR"="PTD"."SKU_NBR")
access("PTD"."XTRA_CARD_NBR"="XTRA_CARD"."XTRA_CARD_NBR")
filter("PTD"."DATE_DT"<=TO_DATE('2006-01-08 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "PTD"."BTCH_CNTL_NBR"=5748)
filter("SKU"."BRAND_NBR"=2453)
access("SKU"."SKU_NBR"="PTD"."SKU_NBR")
filter(LNNVL("SKU"."SKU_NBR"=339716) AND LNNVL("SKU"."SKU_NBR"=339711))
access("PTD"."XTRA_CARD_NBR"="XTRA_CARD"."XTRA_CARD_NBR")
whats wrong i dont understand.
I am also attaching the 10053 trace here
regards
Hrishy
