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
dasgupta.amitava@gmail
Messages: 32
Registered: November 2007
Member
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
Amitava...
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
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
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
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
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: Find missing orders
Next Topic: how to use DBMS STATS
Goto Forum:
  


Current Time: Wed Dec 07 03:11:20 CST 2016

Total time taken to generate the page: 0.06313 seconds