Hi,
We have a problem trying to use an uncorrelated subquery with a partitioned table. The query listed below returns almost instantly and the explain plan looks as expected.
select count(*),max(block_id) from ZAP_SOLE1_DS_TXN_VW_C01 ct where block_id = (select block_id from rkp_css_dictionary3 pp where table_name ='ZAP_SOLE1_DS_TXN_VW' and rownum = 1)
If however we run the exact same query the with the IN clause rather then the =
select count(*),max(block_id) from ZAP_SOLE1_DS_TXN_VW_C01 ct where block_id in (select block_id from rkp_css_dictionary3 pp where table_name ='ZAP_SOLE1_DS_TXN_VW' and rownum = 1)
It runs for a very long time periodically. Often it runs quickly, but sometimes it just runs VERY long. We have tried forcing stats, dynamic sampling (on and off), putting a cardinality hint, no_merge options etc...
The table is interval partitioned.
Are there any thoughts or suggestions on what to look for or how to approach this?
Thanks in advance!