| 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
If you figure this one out, please let me know. This is probably not
what you want to hear:
Sad story with similar scenario, except using Oracle 8i (8.1.7):
Couldn't for the life of us get Oracle (in queries built by either Business Objects or MicroStrategy) to use partition pruning on a date selected from a lookup table. (And in those tools you can't rewrite the generated SQL or embed hints in the SQL.) This affected all queries/reports that used filters that need date predicates based on the current date, e.g., Last 4 weeks, current week, etc.
So, we hit it over the head:
Wrote two (Perl) programs (one for BO, one for MSTR, using the corresponding APIs) that look for filters coded to use the current date and insert (hard-code) the actual date. Then, the SQL that gets generated uses the hard-coded date and correctly does partition pruning.
The environment is a data warehouse which may be loaded daily or weekly, and the Perl programs are scheduled to automatically run at the completion of a successful data load. We are now on Oracle 9i, but since this solution is working, we hadn't tested to see if the CBO got any smarter in regards to partition pruning. From your results, it looks as if it hasn't. (Sigh)
Leslie
-----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=20
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. =20
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
=20
........ AND DWSPSCH.ORDERED_ON_DK =3D 2453235 =20
=20
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=20
'IA_DATES'
1 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'CHAR_DAY_DT'=20
(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=20
'IA_PRODUCTS_IDX_008' (NON-UNIQUE)
327634 HASH JOIN
1 TABLE ACCESS GOAL: ANALYZED (FULL) OF=20
'IA_BUSN_LOCS'
2188129 PARTITION RANGE (ALL) PARTITION: START=3D1 STOP=3D2
2188129 TABLE ACCESS GOAL: ANALYZED (FULL) OF=20
'IA_PURCH_SCHLNS' PARTITION: START=3D1 STOP=3D2
=20
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=20
'IA_PRODUCTS_IDX_008' (NON-UNIQUE)
3970 TABLE ACCESS GOAL: ANALYZED (BY LOCAL INDEX ROWID) OF=20
'IA_PURCH_SCHLNS' PARTITION: START=3D2 STOP=3D2
3972 NESTED LOOPS
1 TABLE ACCESS GOAL: ANALYZED (FULL) OF=20
'IA_BUSN_LOCS'
3970 INDEX GOAL: ANALYZED (RANGE SCAN) OF=20
'IA_PURCH_SCHLNS_IDX_001' (NON-UNIQUE) PARTITION:
START=3D2=20
STOP=3D2
3383 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'IA_SUPPLIERS'
218 BUFFER (SORT)
1 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF=20
'IA_DATES'
1 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'CHAR_DAY_DT'=20
(NON-UNIQUE)
TABLE_NAME INDEX_NAMECOLUMN_NAME=20
------------------------------ ------------------------------ ------------------------------=20 IA_PURCH_SCHLNS IA_PURCH_SCHLNS_IDX_001ORDERED_ON_DK=20
IA_PURCH_SCHLNS_IDX_002PRODUCT_KEY=20
IA_PURCH_SCHLNS_IDX_003SUPPLIER_KEY=20
IA_PURCH_SCHLNS_PK KEY_ID=20
SOURCE_ID=20
INDEX_NAME LOCALI ALIGNMENT=20
------------------------------ ------ ------------=20
IA_PURCH_SCHLNS_IDX_001 LOCAL PREFIXED=20
IA_PURCH_SCHLNS_IDX_002 LOCAL NON_PREFIXED=20
IA_PURCH_SCHLNS_IDX_003 LOCAL NON_PREFIXED=20
IA_PURCH_SCHLNS_PK GLOBAL PREFIXED=20
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=20
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 =20 _subquery_pruning_enabled TRUE =20 _subquery_pruning_cost_factor 20 =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 - 16:15:35 CDT
 |
 |