| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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
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
------- ---------------------------------------------------
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
-- 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 - 19:12:49 CDT
 |
 |