For Oracle 11.2.0.4:
I have a list of ID numbers or ID strings that I want to search for in a sub-query in a Select statement. Something like this (in my real-world cases the outer query is fairly complex):
SELECT *
FROM bigtable
WHERE bigtable.id IN (SELECT id
FROM TABLE(the_nested_table));
This produces the correct answer. However, in some cases, the performance can become terrible.
I noticed, in the execution plan, that the cardinality for the_nested_table was defaulting to some large number around 7,900 even when it actually only had 1-10 rows in it. I tried using the DYNAMIC_SAMPLING hint to correct this:
SELECT *
FROM bigtable
WHERE bigtable.id IN (SELECT /*+ dynamic_sampling(p1, 10) */ id
FROM TABLE(the_nested_table) p1);
Which made the cardinality come out correct and improved the performance in some cases. However, in other cases the performance was still very bad.
Do you know of any performance issues with nested tables that are cast as table() and used in sub-queries?
Regards,
Alan