RE: Partition pruning problem
- From: "Leslie Tierstein" <leslie.tierstein@xxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>, <rob.langmuir@xxxxxxxxxxxxxxx>
- Date: Thu, 19 Aug 2004 14:15:41 -0700
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of RSL
Sent: Wednesday, August 18, 2004 4:27 PM
To: oracle-l@xxxxxxxxxxxxx
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
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 =3D DWSDEPOTBLOC.BUSN_LOC_KEY =
!!!!!*****AND
DWSPSCH.ORDERED_ON_DK =3D DWSDEPOTDATE.DATE_KEY*****!!!!!
AND DWSPSCH.SUPPLIER_KEY =3D DWSSPLR.SUPPLIER_KEY AND =
DWSPSCH.PRODUCT_KEY
=3D 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 =3D '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=20
Rows Execution Plan
------- ---------------------------------------------------
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)
***** Additional info
TABLE_NAME INDEX_NAME
COLUMN_NAME=20
------------------------------ ------------------------------
------------------------------=20
IA_PURCH_SCHLNS IA_PURCH_SCHLNS_IDX_001
ORDERED_ON_DK=20
=20
STORAGE_LOC_KEY=20
IA_PURCH_SCHLNS_IDX_002
PRODUCT_KEY=20
=20
PSCH_ATTR1_CODE=20
IA_PURCH_SCHLNS_IDX_003
SUPPLIER_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
----------------------------------------------------------------
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
-----------------------------------------------------------------
----------------------------------------------------------------
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:
