Partition pruning problem

From: RSL <rob.langmuir_at_joking.plus.com>
Date: Wed, 18 Aug 2004 21:26:37 +0100
Message-ID: <001e01c48561$a9e9a4c0$0100000a@lan>

Background Info..

Oracle version 9.2.0.4
These tables are Informatica tables

Main Fact table IA_PURCH_SCHLNS (partitioned on "ordered_on_dk" - julian date ) As most queries have predicate involving "ordered_on_dk" it was decided to partition on this attribute.

1st query (below) is built using Poweranalyzer tool

The partition key value has not been determined before partition is accessed. CBO appears to have decided to full scan Fact table first.

In the 2nd query (below) modified by me to include literal value

      
       ........ AND DWSPSCH.ORDERED_ON_DK = 2453235                  
    

partition pruning occurs ( as expected )

Is there anyway I can get the 1st query to get the "partitioned date_key value" before it goes accessing the Fact table ?

Thanks very much ...../Bob

SELECT DWSDEPOTBLOC.BUSN_LOC_NUM,
   TO_DATE(TO_CHAR(DWSDEPOTDATE.CAL_DAY_DT, 'yyyy-MM-dd'), 'yyyy-MM-dd'),    DWSSPLR.SUPPLIER_NUM || ':' || DWSSPLR.SUPPLIER_NAME,    (SUM(DWSPSCH.RECEIVED_QTY))
FROM IA_BUSN_LOCS DWSDEPOTBLOC,

   IA_SUPPLIERS DWSSPLR,
   IA_PRODUCTS DWSPROD,
   IA_DATES DWSDEPOTDATE,
   IA_PURCH_SCHLNS DWSPSCH

WHERE DWSPSCH.STORAGE_LOC_KEY = DWSDEPOTBLOC.BUSN_LOC_KEY !!!!!*****AND DWSPSCH.ORDERED_ON_DK = DWSDEPOTDATE.DATE_KEY*****!!!!! AND DWSPSCH.SUPPLIER_KEY = DWSSPLR.SUPPLIER_KEY AND DWSPSCH.PRODUCT_KEY = DWSPROD.PRODUCT_KEY AND TO_CHAR(DWSDEPOTDATE.CAL_DAY_DT, 'yyyy-MM-dd') IN (TO_CHAR(SYSDATE-1,'yyyy-MM-dd')) AND DWSDEPOTBLOC.BUSN_LOC_NUM IN ('THA') AND DWSPROD.DIVISION_CODE = 'F01' GROUP BY DWSDEPOTBLOC.BUSN_LOC_NUM,
  TO_DATE(TO_CHAR(DWSDEPOTDATE.CAL_DAY_DT, 'yyyy-MM-dd'), 'yyyy-MM-dd'),   DWSSPLR.SUPPLIER_NUM || ':' || DWSSPLR.SUPPLIER_NAME ORDER BY 1, 3 Rows Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
     20   SORT (GROUP BY)
    218    HASH JOIN
      1     TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF 
                'IA_DATES'
      1      INDEX   GOAL: ANALYZED (RANGE SCAN) OF 'CHAR_DAY_DT' 
                 (NON-UNIQUE)
  20662     HASH JOIN
   3383      TABLE ACCESS   GOAL: ANALYZED (FULL) OF 'IA_SUPPLIERS'
  20662      HASH JOIN
   1039       INDEX   GOAL: ANALYZED (FAST FULL SCAN) OF 


                  'IA_PRODUCTS_IDX_008' (NON-UNIQUE)

 327634       HASH JOIN
      1        TABLE ACCESS   GOAL: ANALYZED (FULL) OF 
                   'IA_BUSN_LOCS'
2188129        PARTITION RANGE (ALL) PARTITION: START=1 STOP=2
2188129         TABLE ACCESS   GOAL: ANALYZED (FULL) OF 
                    'IA_PURCH_SCHLNS' PARTITION: START=1 STOP=2



                                       
     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
     20   SORT (GROUP BY)
    218    MERGE JOIN (CARTESIAN)
    218     HASH JOIN
    218      HASH JOIN
   1039       INDEX   GOAL: ANALYZED (FAST FULL SCAN) OF 


                  'IA_PRODUCTS_IDX_008' (NON-UNIQUE)

   3970       TABLE ACCESS   GOAL: ANALYZED (BY LOCAL INDEX ROWID) OF 


                  'IA_PURCH_SCHLNS' PARTITION: START=2 STOP=2

   3972        NESTED LOOPS
      1         TABLE ACCESS   GOAL: ANALYZED (FULL) OF 
                    'IA_BUSN_LOCS'
   3970         INDEX   GOAL: ANALYZED (RANGE SCAN) OF 


                  'IA_PURCH_SCHLNS_IDX_001' (NON-UNIQUE) PARTITION: START=2 

                    STOP=2
   3383      TABLE ACCESS   GOAL: ANALYZED (FULL) OF 'IA_SUPPLIERS'
    218     BUFFER (SORT)
      1      TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF 
                 'IA_DATES'
      1       INDEX   GOAL: ANALYZED (RANGE SCAN) OF 'CHAR_DAY_DT' 
                  (NON-UNIQUE)

• Additional info

TABLE_NAME                     INDEX_NAME                     COLUMN_NAME 
------------------------------ ------------------------------ ------------------------------ 
IA_PURCH_SCHLNS                IA_PURCH_SCHLNS_IDX_001        ORDERED_ON_DK 
                                                              STORAGE_LOC_KEY 
                               IA_PURCH_SCHLNS_IDX_002        PRODUCT_KEY 
                                                              PSCH_ATTR1_CODE 

                               IA_PURCH_SCHLNS_IDX_003        SUPPLIER_KEY 

                               IA_PURCH_SCHLNS_PK             KEY_ID 
                                                              SOURCE_ID 

INDEX_NAME                     LOCALI ALIGNMENT 
------------------------------ ------ ------------ 
IA_PURCH_SCHLNS_IDX_001        LOCAL  PREFIXED 
IA_PURCH_SCHLNS_IDX_002        LOCAL  NON_PREFIXED 
IA_PURCH_SCHLNS_IDX_003        LOCAL  NON_PREFIXED 
IA_PURCH_SCHLNS_PK             GLOBAL PREFIXED 

select count(*) from busanal.ia_dates 54787 
select count(*) from busanal.ia_products 22342 

select count(*) from busanal.ia_suppliers 3383 select count(*) from busanal.ia_busn_locs 192

Stats have been gathered on tables/indexes involved

Table IA_DATES attr date_key ( all julian dates from 1900 - 2049 )

Hidden parameter settings

_subquery_pruning_reduction          50                             
_subquery_pruning_enabled            TRUE                          
_subquery_pruning_cost_factor        20  


----------------------------------------------------------------

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Wed Aug 18 2004 - 15:22:02 CDT