SQL EXISTS Why does choosing rownum cause an inefficient execution plan?

Question

Problem

I am trying to understand why what seems like a slight difference in the two Oracle Syntax Update queries causes a radically different execution plan.

Request 1:

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) 

Request 2:

 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) 

As you can see, the only difference between the two is that the subquery in Query 2 returns rownum instead of the values ​​of each row.

Implementation plans for the two cannot be more different:

• Query1 - Outputs summary results from both tables and uses sorting and hashjoin to return results. This is in good agreement with the favorable cost of 2346 (despite using the EXISTS offer and the cohesive subquery).

• Query2 - Pulls both results from a table, but uses a counter and a filter to accomplish the same task and returns a plan of execution with an amazing price of 77,789,696! I should note that his query is just up to me, so I'm not sure if this returns the same results (although I believe it should).

From my understanding of the Exists clause, this is just a simple boolean check that runs on the row of the main table. It doesn’t matter if one row is returned in my EXISTS state or 100,000 rows ... if any results are returned for the row that it is running, then you have passed the existence check. So, why would it matter if my SELECT query returns a SELECT query?

-------------------- EDIT ----------------------

For the query below are the execution plans that I run in TOAD ... note that I edited the table names in my example above to simplify. In these plans, ALSS_SALES2 = sales above and SALESEXT_TMP = tempTABLE above.

It should also be mentioned, but none of the two tables have indexes at the moment. I haven't added them to my tempTable yet, and I'm testing with a cheap copy of a sales table that contains only fields and data but no indexes, restrictions or security.

Thanks for helping everyone!

Request Execution Plan 1

Request Execution Plan 2

-------------------------------------------- ----

Questions

1) Why did calling rownum change the execution plan?

2) What is a filter that is so incredibly inefficient?

3) I missed something fundamental with how the Exists article works that causes this change?

Answer 1

Posting actual query plans will be very helpful.

In general, however, when the optimizer sees a subquery with rownum , this drastically limits its ability to transform the query and combine the results from the subquery with the main query because it potentially affects the results. This can be a quick way to get Oracle to materialize a subquery if it turns out to be more efficient than the plan chosen by the optimizer. In this case, it is likely that this leads to the fact that the optimizer refuses the conversion step, which makes the request more efficient.

Sometimes you will see someone making a request like

 SELECT b.* FROM (SELECT <<columns>> FROM driving_table WHERE <<conditions>>) a, b WHERE a.id = b.id 

and bind to rownum to subquery a

 SELECT b.* FROM (SELECT <<columns>>, rownum FROM driving_table WHERE <<conditions>>) a, b WHERE a.id = b.id 

to force the optimizer to evaluate the subquery a before performing the join. Usually, of course, the optimizer should do this by default if it is more efficient. But if the optimizer is wrong, adding rownum can be faster than calculating the right set of prompts to force a plan or delve into the main problem to find the right solution.

Of course, in the special case when you have a subquery in WHERE EXISTS , where the only use of rownum is on the SELECT list, we humans may find that rownum should not be the query conversion step that the optimizer would like to use. The optimizer probably uses a more general rule, which states that subqueries that reference a function like rownum must be fully executed (this may depend on the exact version of Oracle and / or the optimizer settings). Therefore, the optimizer realistically does a ton of extra work, because it is not smart enough to admit that the added rownum cannot affect the query results.

Answer 2

The only question is what is the execution plan for this request:

 UPDATE sales s SET status = 'DONE', trandate = sysdate WHERE EXISTS (Select NULL FROM tempTable tmp WHERE s.key1 = tmp.key1 AND s.key2 = tmp.key2 AND s.key3 = tmp.key3); 

It renders what is needed in an EXISTS (...) expression - virtually nothing! As already mentioned, Oracle just needs to check if something is returning, and not what is returning in Sub-Query.