Home » SQL & PL/SQL » SQL & PL/SQL » how do I prevent Oracle from invoking a time-consuming table function unnecesarily?
how do I prevent Oracle from invoking a time-consuming table function unnecesarily? [message #233347] Wed, 25 April 2007 15:30 Go to next message
hduin07
Messages: 2
Registered: April 2007
Junior Member
I have a view that uses a time consuming table function
called get_assign_tbl and joins it with one other small table of less than 100 rows. In all usages of this view the client will specify the primary key of the small table to be used. But what I am seeing is that the get_assign_tbl gets called for all rows in the second table. That is not acceptable in terms of performance and in fact it seems silly that Oracle is doing this. The code is below. Look forward to hearing from others what I can do to fix this. Oh yes, the client has to use a view.

-Harry

CREATE OR REPLACE VIEW VIEW1_VW AS
select
ne.ne_id,
fac_type,
tp_is,
tp_xc,
from js_ne_master ne, table(get_assign_tbl(ne.ne_id))
WITH READ ONLY;

Normal usage:
select ne_id, ne_name, fac_type, tp_is, tp_xc from VIEW1_VW a
where a.ne_id=103;
Re: how do I prevent Oracle from invoking a time-consuming table function unnecesarily? [message #233448 is a reply to message #233347] Thu, 26 April 2007 03:06 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Can you post an Explain Plan for one of these queries.

[Updated on: Thu, 26 April 2007 03:08]

Report message to a moderator

Re: how do I prevent Oracle from invoking a time-consuming table function unnecesarily? [message #233563 is a reply to message #233448] Thu, 26 April 2007 11:11 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
http://laurentschneider.com/wordpress/2007/04/variable-in-a-view.html

[Updated on: Thu, 26 April 2007 11:15]

Report message to a moderator

Re: how do I prevent Oracle from invoking a time-consuming table function unnecesarily? [message #233822 is a reply to message #233347] Fri, 27 April 2007 12:27 Go to previous message
hduin07
Messages: 2
Registered: April 2007
Junior Member
I solved this problem by rewriting the table function. I fond an example of a PIPELINED function and use the PIPE ROW construct to return each row.

I believe that the reason that Oracle was executing the old table function repeatedly was due to the unusual/inefficient implementation of the table function and Oracle stopped at one point optimizing the execution.

Thanks for all the help!
Previous Topic: Generate Rows
Next Topic: Table_name as variable
Goto Forum:
  


Current Time: Fri Dec 09 19:35:18 CST 2016

Total time taken to generate the page: 0.15717 seconds