Home » SQL & PL/SQL » SQL & PL/SQL » Problem when using Collection (10g, Unix)
Problem when using Collection [message #351633] Wed, 01 October 2008 15:43 Go to next message
Messages: 32
Registered: November 2007
Karma: 0
Hi All,
I am having a type defined in the DB as:
type long_array as table of number;

I am using this array to write a query where I am passing this array within a "IN" clause. But the cost for the query is going beyond 100,000,000!!!! but when I am using the same query with the same amount of values (hardcoded in the "IN" clause), then it is running very fast. Both the queries alongwith their explain plan are attached with this post. Can anyone tell me why this is happening. Is using the array like this is not a good practice??

Thanks in advance
Re: Problem when using Collection [message #351656 is a reply to message #351633] Thu, 02 October 2008 02:31 Go to previous messageGo to next message
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Karma: 0
Senior Member
Data access from arrays is slower, but it is also vastly more flexible.
You should use it if you need to.

In 10g, there is a less clunky syntax for this:
cf.item_id in (SELECT column_value FROM table(cast(:v_arr as long_array)))

Re: Problem when using Collection [message #351689 is a reply to message #351633] Thu, 02 October 2008 08:18 Go to previous message
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Karma: 0
Senior Member
I believe the syntax referenced by JRowbottom is good in 8iR3 also, and I'd recommend using it over what you've got if you can.

What I usually see with table() functions is that Oracle has trouble with the cardinality estimates. As it should. It has no way to know how many "rows" will be returned.

In 10g (I think is the earliest) you can use a cardinality hint in your query to let Oracle know to expect ballpark 10 rows instead of ballpark 8,000 or 32,000 rows, which will then drive the choices made for the rest of the query.

Prior to that hint, you are stuck with using other hints as you normally would to drive the query based on your knowledge.
Previous Topic: Java Developer learning PL/SQL
Next Topic: Find missing orders
Goto Forum:

Current Time: Tue Aug 22 13:48:00 CDT 2017

Total time taken to generate the page: 0.19219 seconds