Hi Folks
Currently i have a query that runs for long
and the plan isCode: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
however if change the query to hard code the btch_cntl_nbr like thisCode:----------------------- | 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")
the the plan changes likeCode: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 /
whats wrong i dont understand.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")
I am also attaching the 10053 trace here
regards
Hrishy
Reply With Quote