Lazy evaluation of Oracle PL/SQL statements in SELECT clauses of SQL queries

Question

I have a performance problem with an Oracle select statement that I use in a cursor. In the statement one of the terms in the SELECT clause is expensive to evaluate (it's a PL/SQL procedure call, which accesses the database quite heavily). The WHERE clause and ORDER BY clauses are straightforward, however.

I expected that Oracle would first perform the WHERE clause to identify the set of records that match the query, then perform the ORDER BY clause to order them, and finally evaluate each of the terms in the SELECT clause. As I'm using this statement in a cursor from which I then pull results, I expected that the expensive evaluation of the SELECT term would only be performed as needed, when each result was requested from the cursor.

However, I've found that this is not the sequence that Oracle uses. Instead it appears to evaluate the terms in the SELECT clause for each record that matches the WHERE clause before performing the sort. Due to this, the procedure that is expensive to call is called for every result result in the result set before any results are returned from the cursor.

I want to be able to get the first results out of the cursor as quickly as possible. Can anyone tell me how to persuade Oracle not to evaluate the procedure call in the SELECT statement until after the sort has been performed?

This is all probably easier to describe in example code:

Given a table example with columns a, b, c and d, I have a statement like:

select a, b, expensive_procedure(c)
  from example
 where <the_where_clause>
 order by d;

On executing this, expensive_procedure() is called for every record that matches the WHERE clause, even if I open the statement as a cursor and only pull one result from it.

I've tried restructuring the statement as:

select a, b, expensive_procedure(c)
  from example, (select example2.rowid, ROWNUM
                   from example example2
                  where <the_where_clause>
                  order by d)
  where example.rowid = example2.rowid;

Where the presence of ROWNUM in the inner SELECT statement forces Oracle to evaluate it first. This restructuring has the desired performance benefit. Unfortunately it doesn't always respect the ordering that is required.

Just to be clear, I know that I won't be improving the time it takes to return the entire result set. I'm looking to improve the time taken to return the first few results from the statement. I want the time taken to be progressive as I iterate over the results from the cursor, not all of it to elapse before the first result is returned.

Can any Oracle gurus tell me how I can persuade Oracle to stop executing the PL/SQL until it is necessary?

Answer 1

Hi

You might want to give this a try

select a, b, expensive_procedure(c)
  from example, (select /*+ NO_MERGE */
                    example2.rowid, 
                    ROWNUM
                    from example example2
                    where <the_where_clause>
                    order by d)
  where example.rowid = example2.rowid;

Answer 2

Might some form of this work?

FOR R IN (SELECT a,b,c FROM example WHERE ...) LOOP
  e := expensive_procedure(R.c);
  ...
END LOOP;

Answer 3

Does this do what you intend?

WITH 
cheap AS
(
    SELECT A, B, C
    FROM EXAMPLE
    WHERE <the_where_clause>
)
SELECT A, B, expensive_procedure(C)
FROM cheap
ORDER BY D

Answer 4

Why join EXAMPLE to itself in the in-line view? Why not just:

select /*+ no_merge(v) */ a, b, expensive_procedure(c)
from 
( select a, b, c
  from example
  where <the_where_clause>
  order by d
) v;

Answer 5

If your WHERE conditions are equalities, i. e.

WHERE   col1 = :value1
        AND col2 = :value2

you can create a composite index on (col1, col2, d):

CREATE INDEX ix_example_col1_col2_d ON example(col1, col2, d)

and hint your query to use it:

SELECT  /*+ INDEX (e ix_example_col1_col2_d) */
        a, b, expensive_procedure(c)
FROM    example e
WHERE   col1 = :value1
        AND col2 = :value2
ORDER BY
        d

In the example below, t_even is a 1,000,000 rows table with an index on value.

Fetching 100 columns from this query:

SELECT  SYS_GUID()
FROM    t_even
ORDER BY
        value

is instant (0,03 seconds), while this one:

SELECT  SYS_GUID()
FROM    t_even
ORDER BY
        value + 1

takes about 170 seconds to fetch first 100 rows.

SYS_GUID() is quite expensive in Oracle

As proposed by others, you can also use this:

SELECT  a, b, expensive_proc(c)
FROM    (
        SELECT  /*+ NO_MERGE */
                *
        FROM    mytable
        ORDER BY
                d
        )

, but using an index will improve your query response time (how soon the first row is returned).

Answer 6

One of the key problems with the solutions that we've tried is how to adjust the application that generates the SQL to structure the query correctly. The built SQL will vary in terms of number of columns retrieved, number and type of conditions in the where clause and number and type of expressions in the order by.

The inline view returning ROWIDs for joining to the outer was an almost completely generic solution that we can utilise, except where the search is returning a significant portion of the data. In this case the optimiser decides [correctly] that a HASH join is cheaper than a NESTED LOOP.

The other issue was that some of the objects involved are VIEWs that can't have ROWIDs.

For information: "D" was not a typo. The expression for the order by is not selected as part of the return value. Not an unusual thing:

select index_name, column_name
from user_ind_columns
where table_name = 'TABLE_OF_INTEREST'
order by index_name, column_position;

Here, you don't need to know the column_position, but sorting by it is critical.

We have reasons (with which we won't bore the reader) for avoiding the need for hints in the solution, but it's not looking like this is possible.

Thanks for the suggestions thus far - we have tried most of them already ...