Thanks Margaret.
This does help. But I'm afraid I already tried this and .... it works.
But you may have guessed that this littel statement is only a small
part of a big statement and when I make the join in the big statement
(instead of subselect), the optimizer does not do partition pruning.
Maybe there is another reason ....
I just showed you a small part, the part where I believes lies the
bottleneck/performance issue. I was alread able to 'force' partition
pruning for this small statement....
But of course, you're right about the join. I was just hoping for other
solutions as well....
Kind regards,
Benny
MTNorman schreef:
> Benny,
>
> Try moving the subquery out of the where clause and into the from table
> list for a join. My experience with range partitioned dates is that
> the
> optimizer favors partition elimination for joins on the partition
> key(s) and
> often disfavors partition elimination for partition key subqueries.
>
> select VW_RRP00010.*,RRP0.TB_RRP00115.*
> FROM RRP0.MV_RRP00010 VW_RRP00010,rrp0.TB_RRP00115,
> (SELECT TB_RRP00104.LOAD_DT COL1 FROM
> RRP0.TB_RRP00104 TB_RRP00104 WHERE '1' = TB_RRP00104.VSN_CD AND 'D' =
> TB_RRP00104.LOAD_TYPE_CD) a,
> where VW_RRP00010.LOAD_DT = TB_RRP00115.LOAD_DT
> AND VW_RRP00010.LOAD_TYPE_CD = TB_RRP00115.LOAD_TYPE_CD
> AND VW_RRP00010.FUSE_CTY_ID = TB_RRP00115.FUSE_CTY_ID
> AND (VW_RRP00010.ISO2_CTY_CD = TB_RRP00115.ISO2_CTY_CD OR ' ' =
> TB_RRP00115.ISO2_CTY_CD)
> and tb_rrp00115.load_dt = a.LOAD_DT COL1
>
> Hope this helps,
> Margaret
Received on Wed Jan 17 2007 - 07:22:52 CST
