Re: subselect results in poor performance

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 - 06:56:28 CST