Why does this Oracle 10g SQL run slow only when I query a subquery with a where clause?

Question

I can't paste in the entire SQL for various reasons, so consider this example:

select * 
from
    (select nvl(get_quantity(1), 10) available_qty 
     from dual)
where available_qty > 30;

get_quantity is a function that makes a calculation based on the ID of a record that's passed through it. If it returns null, I use nvl() to force it to 10.

The query runs very slow when I use the WHERE clause in the parent query. When I comment out the WHERE clause, however, it runs very fast. What I don't get is why it can display the data very fast, but it can't query it just as fast. I am querying the results of a subquery, too. I was under the impression that subqueries return a "rendered" dataset. It's almost as if querying the available_qty identifier is causing it to reference something within the subquery.

This is why I don't think the contents of the get_quantity function are relevant here, so I didn't bother posting it. Instead, I think it's a misunderstanding on my part of how Oracle handles subqueries and whatnot.

Do any of you Oracle gurus have any idea what I am doing wrong?

Afterthought: as I was entering tags for this question, the tag "correlated subquery" came up. In doing some quick research, it seems that this type of subquery somewhat depends on the outer query. Could this be related to my problem?

Answer 1

Let's try an experiment. First we'll run the following query:

select lvl, rnd 
from (select level as lvl from dual connect by level <= 5) a,
     (select dbms_random.value() rnd from dual) b;

The "a" subquery will return 5 rows with values from 1 to 5. The "b" subquery will return one row with a random value. If the function is run before the two tables are join (by Cartesian), the same random value will be returned for each row. The actual results:

       LVL        RND
---------- ----------
         1 .417932089
         2 .963531718
         3 .617016889
         4 .128395638
         5 .069405568

5 rows selected.

Clearly the function was run for each of the joined rows, not for the subquery before the join. This is a result of Oracle's optimizer deciding that the best path for the query is to do things in that order. To prevent this, we have to add something to the second subquery that will make Oracle run the subquery in it's entirety before performing the join. We'll add rownum to the subquery, since Oracle knows rownum will change if it's run after the join. The following query demonstrates this:

select lvl, rnd from (
select level as lvl from dual connect by level <= 5) a,
(select dbms_random.value() rnd, rownum from dual) b;

As you can see from the results, the function was only run once in this case:

       LVL        RND
---------- ----------
         1 .028513902
         2 .028513902
         3 .028513902
         4 .028513902
         5 .028513902

5 rows selected.

In your case, it seems likely that the filter provided by the where clause is making the optimizer take a different path, where it's running the function repeatedly, rather than once. By making Oracle run the subquery as written, you should get more consistent run-times.