StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Let's try an experiment. First we'll run the following query:</p> <pre><code>select lvl, rnd from (select level as lvl from dual connect by level <= 5) a, (select dbms_random.value() rnd from dual) b; </code></pre> <p>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:</p> <pre><code> LVL RND ---------- ---------- 1 .417932089 2 .963531718 3 .617016889 4 .128395638 5 .069405568 5 rows selected. </code></pre> <p>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:</p> <pre><code>select lvl, rnd from ( select level as lvl from dual connect by level <= 5) a, (select dbms_random.value() rnd, rownum from dual) b; </code></pre> <p>As you can see from the results, the function was only run once in this case:</p> <pre><code> LVL RND ---------- ---------- 1 .028513902 2 .028513902 3 .028513902 4 .028513902 5 .028513902 5 rows selected. </code></pre> <p>In your case, it seems likely that the filter provided by the <code>where</code> 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.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload