Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I was wondering performance wise what would be faster a join or a from and where query. I am trying merge and update query but performance is terrible?

share|improve this question
can you give more information about your problem? – Atilla Ozgur Feb 3 '12 at 12:20
Can you post your query? – Aaron Feb 3 '12 at 14:13
Post your query plan – Gaius Feb 3 '12 at 19:40
up vote 5 down vote accepted

If you are asking whether it is more efficient to use SQL 92 join syntax

SELECT empno, ename, dname
  FROM emp
       JOIN dept USING (deptno)

or to use the older join syntax

SELECT empno, ename, dname
  FROM emp  e,
       dept d
 WHERE e.deptno = d.deptno

the answer is that it doesn't matter. The optimizer is automatically going to do an internal transformation from the SQL 92 syntax to the older syntax anyway.

If you have performance problems, it is highly unlikely that they are related to your choice of join syntax (though when Oracle first added support for SQL 92 join syntax, there were bugs relating to its ability to transform the SQL into the older style).

share|improve this answer
Just want to add to your answer that Oracle (at least Oracle 10)still has some problems with JOIN syntax. I can create refresh fast on commit materialized view using the old syntax (join condition in where), but I can't do the same with normal join. – a1ex07 Feb 3 '12 at 16:22
@a1ex07 - still true in 11g (, anyway). – Alex Poole Feb 3 '12 at 17:53

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.