Re: Partition pruning problem

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Thu, 19 Aug 2004 16:52:10 -0600
Message-Id: <6.1.0.6.2.20040819164611.02f8bad8@pop.centrexcc.com>

Can you show me the rules of logic by which the CBO (or anybody) can deduce the value(s) for DWSDEPOTDATE.DATE_KEY when DWSDEPOTDATE.CAL_DAY_DT = sysdate-1 (I left off the to_char function for clarity). Given the CAL_DAY_DT predicate, the DATE_KEY can still be all over the map and include values from any and all partitions. Based on what criteria should the optimizer do the pruning?

At 02:26 PM 8/18/2004, you wrote:

>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 ?
>
>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'
Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com

---

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 - 17:48:05 CDT