Why is this Oracle 10g SQL running slower only when querying a subquery with a where clause?

Question

I cannot insert all of 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 calculates based on the identifier of the record passed through it. If it returns null, I use nvl() to force it to 10.

The query runs very slowly when I use the WHERE clause in the parent query. However, when I comment on the WHERE , it works very fast. I don’t understand why it can display data very quickly, but it cannot request it as fast. I also request the results of the subquery. I had the impression that the subqueries returned a “visualized” dataset. It is almost as if the request for the identifier available_qty forced to refer to something inside the subquery.

That’s why I don’t think the content of the get_quantity function is relevant here, so I didn’t post it. Instead, I think this is a misunderstanding in my part of how Oracle handles subqueries and more.

Do you have an Oracle guru, someone knows what I'm doing wrong?

Subsequent thought: when I entered tags for this question, the tag "correlated subquery" appeared. Having done some quick research, it seems that this subquery is somewhat dependent on an external query. Could this be due to my problem?

Answer 1

Try the experiment. First we 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; 

Subquery "a" returns 5 rows with values ​​from 1 to 5. Subquery "b" returns one row with a random value. If the function is run before the two tables are joined (according to the Cartesian table), then the same random value will be returned for each row. Actual Results:

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

Obviously, the function was run for each of the connected rows, and not for a subquery before joining. This is the result of the Oracle optimizer, which decided that the best way to query is to do things in that order. To prevent this from happening, we need to add something to the second subquery, which will force Oracle to run the entire subquery before performing the join. We will add rownum to the subquery since Oracle knows that rownum will change if it starts after connection. 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 can be seen from the results, the function was run only 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 makes the optimizer a different way, where it runs the function again, and not once. Forcing Oracle to run the subquery as written, you should get more consistent time intervals.