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

cursorsfunctionsoracle

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

Create a view and then query it.
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

Best Answer

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.

Related Solutions

Oracle extract slow function call from a WHERE clause

The second form of your query will work if you make your mark your function as deterministic, meaning that for a given set of input values, it will always return the same result.
With that set, Oracle will only run the conversion once for each parameters in the where clause rather than for every row.

With this:

CREATE OR REPLACE FUNCTION TO_MINUTE_D (DATE_IN IN DATE)
RETURN NUMBER DETERMINISTIC AS
BEGIN
  /* Minute 0 = 12/30/1899 12:00am */
  RETURN
    (TRUNC(DATE_IN, 'DD') - TO_DATE('12/30/1899', 'MM/DD/YYYY')) * 1440 +
    TO_NUMBER(TO_CHAR(DATE_IN, 'HH24'))  * 60 +
    TO_NUMBER(TO_CHAR(DATE_IN, 'MI'));
END TO_MINUTE_D;
/

On a table filled with a large bunch of dummy rows (increasing ints), I get the following timings consistently:

SQL> SELECT * FROM MY_TABLE
WHERE START_MINUTE < TO_MINUTE(TO_DATE('2013-01-31', 'YYYY-MM-DD'))
AND STOP_MINUTE > TO_MINUTE(TO_DATE('2013-01-01', 'YYYY-MM-DD'));

no rows selected

Elapsed: 00:00:12.69

versus deterministic-annotated function:

SQL> SELECT * FROM MY_TABLE
     WHERE START_MINUTE < TO_MINUTE_D(TO_DATE('2013-01-31', 'YYYY-MM-DD'))
     AND STOP_MINUTE > TO_MINUTE_D(TO_DATE('2013-01-01', 'YYYY-MM-DD')); 

no rows selected

Elapsed: 00:00:00.07

You should get very close to what you have with the values plugged in directly, and indexes on those columns can be used as if you'd plugged in literals.

(Putting the conversion function on the start|stop_minute columns isn't a good idea in general as you've discovered, unless you have a function-based index on those that matches exactly.)

How to correctly return values from a function

You should return something when nothing is found. Before exception comment in the statement.
The way your routine is written it may does find nothing and then hits the end of the function without returning anything, hence the error function returned without value.
Workaround :
Handle it with the exception

exception
   when TOO_MANY_ROWS then
        return 'Error bla bla '; -- do something meaningful here 
   when NO_DATA_FOUND then
        return null; -- if you want to return null on nothing found

Best Answer

Related Solutions

Oracle extract slow function call from a WHERE clause

How to correctly return values from a function

Related Question