Selecting from a function that returns a sys refcursor or an alternative

Question

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:

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.