The following query is given in a
SELECT e.data FROM extra e WHERE e.external_id in
(SELECT * FROM TABLE (p_external_ids)).
create or replace type "VARCHAR2TABLE" as table of VARCHAR2(4000 CHAR)
factor of 20
SELECT * FROM TABLE
What hints did you try? Can you post the fast and the slow query plan?
One of the general issues with using PL/SQL collections in SQL is that the CBO often guesses incorrectly at the number of elements in the collection and chooses the wrong plan as a result. It is often helpful in those cases to use the CARDINALITY hint, i.e.
SELECT e.data FROM extra e WHERE e.external_id IN ( SELECT /*+ cardinality(ids 10) */ * FROM TABLE( p_external_ids ) ids )
tells the optimizer to expect 10 elements in P_EXTERNAL_IDS.
Tom Kyte has a more in depth discussion about the cardinality hint and PL/SQL collections on askTom as well.
What is the data type of the EXTERNAL_ID column? Your collection is a collection of strings but EXTERNAL_ID tends to imply a NUMBER. Is there really a data type mismatch here?
Copying the collection into a temporary table would only be expected to help if the problem was that the optimizer couldn't get an accurate cardinality estimate when you referenced the collection but it could get an accurate estimate when you referenced the temporary table. If you are correctly specifying the CARDINALITY hint and that doesn't change performance, that would imply that the problem is not with the optimizer's cardinality estimates.
Can you post the fast and the slow query plans? Can you post the exact SQL statement you are using that includes the CARDINALITY hint (perhaps there is a syntax error)