CODEWITHSUNDEEP
oracle-databaseperformance
Hypino
Hypino

Reputation: 1248

Why does this query perform better than the ANSI JOIN version?

I've been trying to improve the performance of this query for awhile now, and after trying multiple different approaches I found that removing the ANSI JOIN and using conventional syntax reduces the cost according to the explain plan significantly (459928 vs. 82518117). Here is the original (snipped and trimmed):

select distinct api.locn_id         -- Explain Plan: 82518117
                 ,i.item_set_id
                 ,api.variant_id
    from all_planogram_items api
    join includes i
      on api.variant_id = i.variant_id
      or api.dept_id = i.dept_id
      or api.category_id = i.category_id
      or api.fixture_id = i.fixture_id
      or api.aisle_id = i.aisle_id
   where 1 = 1
     and /* variant is not in excludes list*/
        --e.item_set_id is null
         not exists
... etc

and the better performance with the conventional join:

select distinct api.locn_id        -- Explain Plan: 459928
                 ,i.item_set_id
                 ,api.variant_id
    from all_planogram_items api, includes i
   where 1 = 1
     and (api.variant_id = i.variant_id
      or api.dept_id = i.dept_id
      or api.category_id = i.category_id
      or api.fixture_id = i.fixture_id
      or api.aisle_id = i.aisle_id)
     and /* variant is not in excludes list*/
        --e.item_set_id is null
         not exists
... etc

Like I mentioned, there is more to this query (inline views, etc), but the only change I made was here on the join. I have a feeling it has to do with the or clauses but I cannot explain it myself. Why is this so much more efficient?

More information:

Here are the explain plans. I want to re-iterate that the only difference in these explain plans is the join method explained above. Oddly, the lower cost and faster conventional JOIN explain plan is over 3 times as large as the slower ANSI JOIN explain plan (sorry for the screenshots, the explain plans are too large to post text versions here):

Conventional JOIN Explain Plan:

  1. https://i.sstatic.net/ocgOs.png
  2. https://i.sstatic.net/zbdOe.png
  3. https://i.sstatic.net/lVyQk.png

ANSI JOIN Explain Plan:

  1. https://i.sstatic.net/HtjUm.png

Upvotes: 2

Views: 826

Answers (3)

Matthew McPeak
Matthew McPeak

Reputation: 17944

Why is this so much more efficient?

One of your plans is probably doing OR expansion and the other probably is not.

Oracle's logic to transform ANSI join syntax queries is not perfect and not identical to the logic to transform non ANSI join syntax queries. If you will look on Oracle's support website for patches, you will see that just about every release has a number of bugfixes in this area.

The answer extended to other areas as well. For example, in some Oracle versions, you can have a fast-running SELECT statement that will tank if you put it into an INSERT statement or a CREATE TABLE AS SELECT statement.

Oracle joins and ANSI joins are conceptually the same and should make no difference. But, in reality, there are differences.

Upvotes: 2

user5683823
user5683823

Reputation:

You can see the explain plan for each query easily if you use SQL Developer or Toad (they have a button that shows you the execution plan directly). It would be good to compare the plans, to see if, for example, the Optimizer doesn't "see" the opportunity to push predicates from the join to the component tables or views due to the complex ON condition. Moving the conditions to a WHERE clause allows the Optimizer to look at filters in a different order (perhaps) and to see such opportunities. THIS MAY BE ENTIRELY WRONG but it shows the kind of things you may be able to notice by looking at the explain plans.

Upvotes: 0

Akio Hamasaki
Akio Hamasaki

Reputation: 535

Explain Plan Cost is generally not a good measure of performance. It can vary for similar queries and not have an affect on performance. You might want to have a look at autotrace statistics and tkprof for more insight. Also see this link

Upvotes: 0

Related Questions