Take the 2-minute tour ×
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. It's 100% free, no registration required.

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:

SELECT PAID_YEAR,PAID_TOTAL FROM VIEW_1 WHERE SSN=12345678912882;

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? –  Mike JM 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) –  Mike JM Sep 9 '13 at 10:48

1 Answer 1

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

 
discard

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.