Re: IN clause doesnt considers INDEXES in CBO mode ALL_ROWS , ( If we use approach suggested in AskTOM column)

Jonathan mentioned that you can get rid of the THE and DUAL references, and you really don't need a CAST in this case. In addition, it can often be helpful to add a 'where rownum > 0' clause to the select in the IN clause. This is a Tom Kyte tip that will often cause the CBO to switch to a nested loop lookup on the large table using an index.

select count(*)
  from catalogue c inner join catalogue_type ct on ct.catalogue_item_type_id = c.catalogue_item_type_id  where c.campaign_guid in

         (select *
            from
table(csvtoarray('0001545A-A1CF-4524-B8EC-841BDF325E83,0005EC3A-0712-420F-970C-85788C2C8E18,...'))
           where rownum > 0);

-Todd Received on Thu Oct 23 2003 - 18:49:32 CDT