Oracle Query on Pipelined function works fine, but compresses if I add a condition

Question

I have pipelind functions that return a collection of user objects, i.e. a nested table.

It works fine (<4 seconds) when I select it as follows:

select e.* from table(MY_PIPLINED_FUNCTION)e 

But when I add any condition (except where rownum<X ), the query is executed forever (for example, 5+ minutes), but it returns the correct value at the end .

It seems to me that it works but it takes a huge amount of time.

Anyone have any ideas on this?

ps: this is a large set of results, both in the number of rows (30K +) and in the number of columns (50 + columns).

Answer 1

Are you comparing time to get the whole set of results? Or just the first N lines?

Does your predicate retrieve 99% of the data, making one query a lot harder to get these N rows?

The conveyor function may not have anything to do with this. You can have a pipelined function and still fetch the first N rows without evaluating the entire result set. For example, the infinite loop below quickly returns results in an IDE that only retrieves a small number of rows.

 create or replace type number_nt as table of number; create or replace function pipe_function return number_nt pipelined is begin while 1 = 1 loop pipe row(1); end loop; end; / select column_value from table(pipe_function) where column_value < 2; 

You may need to add additional information about your function and predicate.

Answer 2

It gets the whole set of results for applying filters.

You should improve MY_PIPLINED_FUNCTION. It probably uses indexes now, and because of this, first_rows comes quickly.

1. You can try to force it to use the hash for joins (this can get a complete set of results in less time, but the first lines will not come quickly)

2. You can change the function and put the condition in the arguments of the function, changing the function, therefore, - filter the rows from a particular table. (IE instead

 select e.* from table(MY_PIPLINED_FUNCTION)e where e.name = 'mark' 

to do

 select e.* from table(MY_PIPLINED_FUNCTION('mark'))e 

)

These things can help ...