Re: Partition pruning problem
- From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 19 Aug 2004 16:52:10 -0600
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
