Re: Subquery partition pruning
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 4 Apr 2010 07:59:29 +0100
Jonathan,
but how should the "PARTITION RANGE MULTI-COLUMN" operation in line 4 pick
upthe A.DATE_EID without a bloom filter (from 11g on) or a recursive
subqueryin case of a hash join? My understanding of the "PARTITION RANGE
MULTI-COLUMN" operation (introduced in 10.2) is in fact to visualize that
only a non-leading part of the partition key is used to prune.
Randolf,
The multi-column prune is able to prune on just a non-leading part of the
partition key - but can also use the leading part whenever it's available.
But, as you point out, in this case the leading part cannot be made available
as the pruning subquery cannot be used because the driving table doesn't
hold both columns of the partition key (as Greg pointed out).
Given the peculiar implementation of a "seconds" dimension that seems to
require the "meaningless" key to be an exact match for the "real" key, the
OP could consider scaling the "date" dimension up by a factor of 86,400
to include every second with each date - but I doubt if that would be seen
as desirable.
For this particular query, it's possible that a rewrite of the form:
SELECT
TO_DATE('20100322', 'yyyymmdd') date_name,
B.MSISDN,
B.IMSI,
SUM(B.EVENT_AMOUNT)
FROM
DWH_OWN.T_MOBILE_EVENT B
WHERE B.DATE_EID = (select max(a.date_eid) from d_date a where A.DATE_NAME=
TO_DATE('20100322', 'yyyymmdd'))
GROUP BY
B.MSISDN,
B.IMSI
would be sufficient. (Might need a little experimentation and hinting
to avoid an unnest back to a join).
If the date "meaningless key" values were constrained to be
correctly ordered, like the time ones, then this approach could
also work with date ranges with two subqueries.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
