Databases
StackOverflow2013
Postsdbo
POSQL EXISTS Why does selecting rownum cause inefficient execution plan?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL EXISTS Why does selecting rownum cause inefficient execution plan?
    primarykey
    data
    text
    <p><strong>Problem</strong></p> <p>I'm trying to understand why what seems like a minor difference in these two Oracle Syntax Update queries is causing a radically different execution plan. </p> <p>Query 1:</p> <pre><code>UPDATE sales s SET status = 'DONE', trandate = sysdate WHERE EXISTS (Select * FROM tempTable tmp WHERE s.key1 = tmp.key1 AND s.key2 = tmp.key2 AND s.key3 = tmp.key3) </code></pre> <p>Query 2:</p> <pre><code>UPDATE sales s SET status = 'DONE', trandate = sysdate WHERE EXISTS (Select rownum FROM tempTable tmp WHERE s.key1 = tmp.key1 AND s.key2 = tmp.key2 AND s.key3 = tmp.key3) </code></pre> <p>As you can see the only difference between the two is that the subquery in Query 2 returns a rownum instead of the values of every row. </p> <p>The execution plans for these two couldn't be more different:</p> <ul> <li><p>Query1 - Pulls the total results from both tables and uses a sort and a hashjoin to return the results. This peforms well with a favorable 2,346 cost (despite the use of the EXISTS clause and the cohesive subquery).</p></li> <li><p>Query2 - Pulls both table results as well but uses a count and a filter to accomplish the same task and returns an execution plan with an astonishing 77,789,696 cost! I should note that his query just hangs on me so I'm not actually positive this returns the same results (though I believe it should).</p></li> </ul> <p>From my understanding of the Exists clause it is just a simple boolean check that runs per line of the main table. It doesn't matter if a single row is returned in my EXISTS condition or 100,000 rows... if any results are returned for the row that it is being run, then you've passed the exist check. So why would it matter what my subquery SELECT statement returns?</p> <p><strong>--------------------EDIT----------------------</strong> </p> <p>Per request, below are the execution plans I'm running in TOAD... please note I edited the table names in my example above for ease - In these plans ALSS_SALES2 = sales above and SALESEXT_TMP = tempTABLE above.</p> <p>Also should have mentioned but neither of the two tables has indices at this point.. I haven't yet added them to my tempTable and I'm testing with a cheap copy of the sales table which only contains the fields and data but no indices, constraints or security. </p> <p>Thanks for the assistance everyone!</p> <p><strong>Query 1 Execution Plan</strong></p> <p><img src="https://i.stack.imgur.com/F5ATS.jpg" alt="Query1 Execution Plan"></p> <p><strong>Query 2 Execution Plan</strong></p> <p><img src="https://i.stack.imgur.com/RYd2i.jpg" alt="Query2 Execution Plan"></p> <p><strong>------------------------------------------------</strong></p> <p><strong>Questions</strong></p> <p>1) Why did the call for rownum cause the execution plan to change?</p> <p>2) What is it about the Filter that is so incredibally inefficient?</p> <p>3) Am I missing something fundamental with the way the Exists clause works that is causing this change?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USDanK
    1. USDanK
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQL EXISTS Why does selecting rownum cause inefficient execution plan?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POSQL EXISTS Why does selecting rownum cause inefficient execution plan?
      1. USkrokodilko
      1. VTFavorite
    3. VO
      singulars
      1. POSQL EXISTS Why does selecting rownum cause inefficient execution plan?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COMy assumption is that when you use * the engine uses the most relevant index but just returning rownum may ignore the index and do a sequential plan. but I'm not sure
      singulars
      1. POSQL EXISTS Why does selecting rownum cause inefficient execution plan?
      1. USSam D
    2. COCan you include the execution plans, including the access and filter section?
      singulars
      1. POSQL EXISTS Why does selecting rownum cause inefficient execution plan?
      1. USDavid Aldridge
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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