Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a query that returns a resultset of three columns, namely SSN,PAID_YEAR and PAID_TOTAL. From this query I can:

  1. Create a view and then query it.
  2. Create a function and return resultset

If I go the first way a simple query like the following takes more than 20 seconds:


I know that is because when I query a view the engine first brings all the rows of the view and then it filters the rows for the criteria supplied.

If I go the second way I can send a parameter and make the engine look only for those rows that match the condition and return the recordset. But I do not know how to then SELECT from that returned resultset. I took a look at pipelined tables but didn't quite get how to benefit them. So my ultimate question is if it's somehow possible to select from the resultset that is returned from a function like this:

SELECT * FROM FUNCTION_1(12132323232).

If yes, then how, if no, what would be an alternative way?

EDIT:Here's the explain plan:

Here's the explain plan

share|improve this question
The alternative (and best option) is to post the query plan for the query you have included in your question, so that we can help you tune it. Do you know how to get an explain plan? – Phil Sep 9 '13 at 10:22
Sure I have the explain plan, but I'd appreciate if you told me how to post the explain plan here. I mean it's a tree structured result, do you want me to export it to html or just copy and paste the whole tree-view? – Mikayil Abdullayev Sep 9 '13 at 10:33
Edit your question, paste it in as text, then select it and hit the "code sample" button. Should look ok. – Phil Sep 9 '13 at 10:37
@MikeJM Out of interest, what datatype is SSN? (Double-check) – Phil Sep 9 '13 at 10:43
It's a NUMBER(13,0) – Mikayil Abdullayev Sep 9 '13 at 10:48

I know that is because when I query a view the engine first brings all the rows of the view and then it filters the rows for the criteria supplied.

This might be true, but is unlikely to be so.

When you apply a condition to a view, the condition is usually merged into the view query. This is called predicate pushing, and ought to allow the optimiser to apply the filter as early as possible in the execution plan.

There are some cases where this cannot happen, for example if your view summed up all of the sales orders by client and then you applied a condition to require that total sales order were greater then $1,000,000.

The explain plan wil tell you whether this has happened, but it's best to extract it using DBMS_Xplan.Display. the resulting text-based plan will show you the lines of the plan at which conditions are applied.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.