Re: Subquery partition pruning

Hello

With that subquery indeed partition elimination takes place in this
particular query, will have to check other scenarios.

I do agree that is probably a design issue but my experience with Star
Schema is limited so I am not in position to criticize the designer


Thank you



2010/4/4 Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>

>
>  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
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

Other related posts: