What is effective? query with subquery or pivot table

Question

Here are two sample requests for the same purpose (in this example, I want a great seller from the city where Patrick lives.)

select * from salesman where salesman.city = ( select city from salesman where salesman_name='Patriks' ); 

and

 select s1.* from salesman s1,salesman s2 where s1.city=s2.city and s2.salesman_name='Patriks'; 

Which is better or effective and why? (I know this is a small example, but I want to find out what will be good in a difficult situation and for a large database.)

Answer 1

As a general rule:

If you use a subquery, you force Oracle to use a specific execution path (i.e., it must execute the subquery before it can execute the external query)

If you use a connection, Oracle can choose the one that it considers the most effective.

Therefore, I would always like to join the subquery. YMMV.

Answer 2

My experience is that in Oracle, a smoothed query (i.e. the one with the connection) is often more efficient than an equivalent query using a subquery. It seems that in more complex cases, there are query paths that the Oracle optimizer cannot find for a query with a subquery.

In SQL Server, DB2, Ingres, and Sybase, my experience is that it does not matter - these DBMSs have optimizers that find the same query paths, whether you use a flattened query or a query with a subquery .

I do not have enough experience with other DBMSs to comment on them.

But this is only my experience. I would not be surprised if you find different results for specific queries or specific data sets. The best thing to do is try and see which query works best for your situation.

Answer 3

In my experience, if a subquery and JOIN mean the same thing, Oracle will execute them equally fast.

People often rewrite their queries and think that they remained equivalent when in fact they introduced subtle differences. For example, whether an OP query depends on whether salesman_name PRIMARY KEY (or UNIQUE). If this is not the case, these queries no longer mean the same thing.

I would not be surprised if there really were some cases (as others have pointed out) where Oracle does produce substantially different execution plans. Your mileage may vary, but, as always, measure on typical amounts of data and not just assume blindly anyway.

Answer 4

There is simply no answer to this question. Even if your table structure does not change, queries may receive different execution paths over time, depending on the amount of data, indexes, restrictions, due to the hanging variable binding and other factors. Entire books have been written on this subject.

Cagcowboy's answer is incorrect. Oracle rewrites your query to ensure what it considers the best execution plan. Queries like the ones you describe often translate into sub-query blur . I assume that 9 times out of 10, requests similar to the ones you describe will have the same execution plan.

In my opinion, start with what is most readable, what will be clear to someone else reading your code (or yourself, after reading it in six months), what are your intentions. If your request is unacceptably slow, try optimizing it.

As Branko Dimitrievich points out, two questions that you think are the same are often not equivalent. In your two examples, if salesman_name is not unique, your first query will throw an exception ORA-01427: single-row subquery returns more than one row , but your second example will work fine.