0

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?
0

Well, after a long time with no answers. I've done some testing.

I ran the same query in 10g and 11g and both versions, the one with ANSI joins and the ones with the WHERE joins run in less than 1 second.

As problems exists only in 9i, the same version the support for ANSI joins where introduced I assume it's a bug in 9i that could or could have not be solved in a patch.

Gladly as I mentioned, from 10g onwards both flavors of joins perform well.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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