sql - Oracle query plan efficiency problem -

the following query given in pl/sql procedure.

select e.data e e.external_id in     (select * table (p_external_ids)). 

the type of p_external_ids create or replace type "varchar2table" table of varchar2(4000 char).

oracle inefficiently executes query using full table scan. hints on query did not , necessary indexes in place. replacing select * part hardcoded ids reduce query running time factor of 20, when number of rows in table 200 000.

for reference takes 0.3 sec execute select * table clause, , around 0.015 ms single hardcoded id.

what suggested efficient ways (key search) write stored procedure extract data table multiple ids? provided collection type must used pass in list of ids stored procedure.

what hints did try? can post fast , slow query plan?

one of general issues using pl/sql collections in sql cbo guesses incorrectly @ number of elements in collection , chooses wrong plan result. helpful in cases use cardinality hint, i.e.

select e.data    e  e.external_id in (     select /*+ cardinality(ids 10) */ *       table( p_external_ids ) ids   ) 

tells optimizer expect 10 elements in p_external_ids.

tom kyte has more in depth discussion the cardinality hint , pl/sql collections on asktom well.

what data type of external_id column? collection collection of strings external_id tends imply number. there data type mismatch here?

copying collection temporary table expected if problem optimizer couldn't accurate cardinality estimate when referenced collection accurate estimate when referenced temporary table. if correctly specifying cardinality hint , doesn't change performance, imply problem not optimizer's cardinality estimates.

can post fast , slow query plans? can post exact sql statement using includes cardinality hint (perhaps there syntax error)