performancejoinsyntaxoracle9iansi-sql

Reason for poor performance of ANSI joins in Oracle 9i

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.

These 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.

UPDATE:

This are the execution plans, notice that the explain plan for query one shows no cardinality, cost or bytes info :

Fast, old-style join: enter image description here Slow, ANSI join: enter image description here

Solution

  • 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.