Oracle 10g using collection with sql
One of the problems with using collections in SQL is that the optimizer isn't able to guess how many elements the collection has.  It defaults to assuming that the collection has a few thousand elements (I want to say 4k elements but I wouldn't wager on that).  If that is roughly 1,000 times more elements than you have in your actual collection, that's will certainly tend to cause the optimizer to make poor choices about the query plan.

The best way to alleviate that problem is to use the CARDINALITY hint.

SELECT /*+ cardinality(a 4) */ a.col1, b.col2, b.col3
  FROM 
  TABLE(FOO.my_table) a
  INNER JOIN existing_table b ON (b.existing_table_id = a.existing_table_id)
  --b.existing_table_id - primary key supported by unique index
  ORDER BY a.existing_table_id


tells the optimizer to assume that the collection aliased to a has only 4 elements which should cause it to pick a more appropriate query plan.