Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Partition pruning problem

RE: Partition pruning problem

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Fri, 20 Aug 2004 00:15:10 -0400
Message-ID: <KNEIIDHFLNJDHOOCFCDKGENFFDAA.mwf@rsiz.com>


doh, important typo: if date_key is effectively trunc(cal_day_dt)....

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Mark W. Farnham Sent: Thursday, August 19, 2004 8:17 PM
To: oracle-l_at_freelists.org
Subject: RE: Partition pruning problem

Did you mean something like: (By the way if youse guys ever hire my firm, remind me we charge double for allcaps and triple for table aliases longer AND simultaneously less meaningful than the table names.) I'm not sure how many date_key rows might be in that select. If it is guaranteed to be 1, you might use
where (1,ia_purch_schlns.ordered_on_dk) in (select rownum, distinct date_key from ia_dates where cal_day_dt >= trunc(sysdate-1) and cal_day_dt < trunc(sysdate)

you may have to move the equals sign if you have a different opinion of which day midnight belongs to. I think that unless you project the values of date_key explicitly, there is no reliable relationship between dates and date_key. If there is a simple relationship, you might not have to query ia_dates, anyway. If date_key is effectively trunc(ordered_on_dk), where you keep the full detailed date for transaction details, possibly for driving who gets their stuff first, then I'm guessing that all you need is

where ordered_on_dk = trunc(sysdate-1)

(But that's a wild guess.)

eeeew. run that stuff through a tool that understands quote nesting and lowercase everything. Your eyes will thank you and it will raise your effective IQ.

mwf

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 in (select distinct date_key from ia_dates where cal_day_dt >= trunc(sysdate-1) and cal_day_dt < trunc(sysdate)) -----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of RSL Sent: Wednesday, August 18, 2004 4:27 PM To: oracle-l_at_freelists.org
Subject: Partition pruning problem

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)




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


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


----------------------------------------------------------------
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 Thu Aug 19 2004 - 23:11:47 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US