DBAsupport.com Forums - Powered by vBulletin
Results 1 to 1 of 1

Thread: Partition pruning not occuring

  1. #1
    Join Date
    Jan 2001
    Posts
    2,828

    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
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width