SQL EXISTS Why does selecting rownum cause inefficient execution plan?

Posting the actual query plans would be quite helpful.

In general, though, when the optimizer sees a subquery with rownum, that radically limits its ability to transform the query and merge the results from the subquery with the main query because doing so potentially affects the results. That can be a quick way to force Oracle to materialize a subquery if that happens to be more efficient than the plan chosen by the optimizer. In this case, though, it is probably causing the optimizer to forego a transform step that makes the query more efficient.

Occasionally, you'll see someone take a query like

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

and tack on a rownum to the a subquery

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

in order to force the optimizer to evaluate the a subquery before executing the join. Normally, of course, the optimizer should do this by default if it is more efficient. But if the optimizer makes a mistake, adding rownum can be quicker than figuring out the right set of hints to force a plan or digging in to the underlying problem to figure out the right solution.

Of course, in the particular case that you have a subquery in a WHERE EXISTS where the only use of rownum comes in the SELECT list, we humans can detect that the rownum shouldn't prevent any query transform step that the optimizer would care to use. The optimizer, though, is probably using a more general rule that says that subqueries that reference a function like rownum must be completely executed (this may depend on the exact Oracle version and/or the optimizer settings). So the optimizer is realistically doing a bunch of extra work because it's not smart enough to recognize that the rownum you added cannot possibly affect the results of the query.

Just a question, what's the execution plan for this query:

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 visualize what is needed in an EXISTS (...) expression - actually nothing! As already stated Oracle just have to check if anything is returned, not what is returned in Sub-Query.