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

Question

I have a performance issue with the Oracle select statement that I use in the cursor. In the statement, one of the terms in the sentence is SELECTexpensively evaluated (this is a call to the PL / SQL procedure, which pretty much accesses the database). However, the proposals WHEREand ORDER BYare simple.

I expected Oracle to first execute the sentence WHEREto identify the set of records matching the query, then execute the sentence ORDER BYto sort them, and finally evaluate each of the conditions in the sentence SELECT, since I use this operator in the cursor, from which I then retrieve the results , I expected that an expensive member evaluation SELECTwould only be performed as needed, when each result is queried using the cursor.

However, I found that this is not the sequence that Oracle uses. Instead, it evaluates the terms in the sentence SELECTfor each record that matches the WHERE clause before sorting. In this regard, a procedure that is expensive to call is called for each result result in the result set before any results are returned from the cursor.

I want to get the first results from the cursor as quickly as possible. Can someone tell me how to convince Oracle not to evaluate the procedure call in the SELECT statement until sorting is done?

This is probably easier described in the code example:

For a table examplewith columns a, b, cand dI have a statement like:

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

, expensive_procedure() , WHERE, .

:

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;

ROWNUM SELECT Oracle . . , , .

, , , . , , . , , , , , .

Oracle , Oracle PL/SQL , ?

Answer 1

EXAMPLE ? :

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 2

, ?

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 3

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 4

- ?

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

Answer 5

WHERE , i. .

WHERE   col1 = :value1
        AND col2 = :value2

(col1, col2, d):

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

, :

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

t_even 1,000,000 value.

100 :

SELECT  SYS_GUID()
FROM    t_even
ORDER BY
        value

(0,03 ), :

SELECT  SYS_GUID()
FROM    t_even
ORDER BY
        value + 1

170 , 100.

SYS_GUID() Oracle

, :

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

( ).

Answer 6

, , , , SQL, . SQL , where .

, ROWID , , , , . [], HASH , NESTED LOOP.

, - VIEW, ROWID.

: "D" . . :

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

column_position, .

( ), , .

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