Re: Inefficient query plan using large in-list

Senthil,

But in your case I don't see the reason to use collections. You showed this
>> code:
>> select sh.*
>> from table(cast(multiset(select level from dual
>> connect by level <= length
>> (regexp_replace(:in_list, '[^,]+')) + 1) as sys.OdciNumberList)) xt,
>> secmaster_history sh
>> where sh.security_alias = xt.column_value
>> and to_date('15-MAY-2010') between effective_date and
>> nvl(expiration_date,sysdate) ;
>>
>> and looks like there should be regexp_substr(...) instead of level:
>> select sh.*
>> from table(cast(multiset(select *regexp_substr(:in_list,
>> '[^,]+',1,level)* from dual
>> connect by level <= length
>> (regexp_replace(:in_list, '[^,]+')) + 1) as sys.OdciNumberList)) xt,
>> secmaster_history sh
>> where sh.security_alias = xt.column_value
>> and to_date('15-MAY-2010') between effective_date and
>> nvl(expiration_date,sysdate) ;
>>
>> In this case you don't need table(cast multiset(...)), just use inline
>> view:
>> select sh.*
>> from (select regexp_substr(:in_list, '[^,]+',1,level) as val
>> from dual
>> connect by level <= length (regexp_replace(:in_list, '[^,]+')) +
>> 1) xt,
>> secmaster_history sh
>> where sh.security_alias = xt.val
>> and to_date('15-MAY-2010') between effective_date and
>> nvl(expiration_date,sysdate) ;
>>
>
Have you checked this part?
Why do you need table() function here?

-- 
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org

--
http://www.freelists.org/webpage/oracle-l