Thanks for the question, Mel.
Asked: January 06, 2026 - 10:11 pm UTC
Last updated: January 08, 2026 - 4:17 am UTC
Version: Oracle 19c
Viewed 100+ times
You Asked
I have PL/SQL package with a function that returns a list of item numbers. This package/function is repeatedly called by online with multiple users; passing parameters to the function to use within the SQL statement(s). A new string (short list of item types) will now be passed to the function to narrow down the item numbers being returned. My question: is it better to use a Collection or a Global Temporary Table to insert the item types into, which will then be used in the where clause of the SQL statement to select the item numbers.
and Connor said...
In 19c, if the list of parameters is small and consistently sized (less than a few hundred) I'd be inclined to use a collection, because you can lock down the plan for any SQL's using it.
The moment the size gets large or is variable, I'd lean toward temporary tables because you can take advantage of dynamic sampling to get better optimizer plans.
I say "In 19c" because in 26ai, we've improved dynamic sampling for collections in queries, so you could then reduce this debate down to a size (small = collection, large = table).