Please before flagging as duplicate, read the last paragraphs.
In an Oracle 9i database this query runs in 0.18 seconds:
select count(*) from dba_synonyms s,dba_objects t where s.TABLE_OWNER = t.OWNER(+) and s.TABLE_NAME = t.OBJECT_NAME(+) and s.DB_LINK is null and t.OWNER is null;
...but this one runs in an appalling 120 seconds!:
select count(*) from dba_synonyms s left join dba_objects t on ( s.TABLE_OWNER = t.OWNER and s.TABLE_NAME = t.OBJECT_NAME ) where s.DB_LINK is null and t.OWNER is null
Notice the only difference is using propietary Oracle join syntax vs ANSI join syntax.
This question is not a duplicate of this one because that other question is about a very complex query involving more than 9 tables, and the only answer points out that the queries are very different besides the syntax usage (mainly the order of the tables).
In my case it is a extremelly simple query, a mere join between two relations with no mayor complications of differences, including the order of tables.
- Is this a bug in Oracle 9i?
- What is the cause of such dramatic difference in performance?