CODEWITHSUNDEEP
sqloracle-databaseoracle10gquery-optimization
DmitryB
DmitryB

Reputation: 1179

How to optimize oracle query with 'merge join cartesian' in the plan?

Query:

select max(b.counter) as counter,
       b.m_group,
       b.m_code,
       a.s_id,
       s.id_sk
  from tbl_various a, tbl_map b, tbl_sheet s
 where coalesce(b.part, a.part) = a.part
   and coalesce(b.nums, to_number(a.nums)) = a.nums
   and coalesce(b.interc, a.interc) = a.interc
   and coalesce(b.segment, a.segment) = a.segment
   and coalesce(b.acountry, a.acountry) = a.acountry_midas
   and coalesce(b.orig_name, a.orig_name) = a.orig_name
   and coalesce(b.fact, a.fact) = a.fact
   and b.sect is not null
   and s.m_code = b.m_code
 group by b.m_group, b.m_code, a.s_id, s.id_sk;

Plan:

SELECT STATEMENT, GOAL = ALL_ROWS       86763   1           169
 HASH GROUP BY                          86763   1           169
  HASH JOIN                             86762   1           169
   TABLE ACCESS FULL    TBL_MAP         2       1717        92718
   MERGE JOIN CARTESIAN                 79688   300133251   34515323865
    TABLE ACCESS FULL   TBL_SHEET       5       912         18240
    BUFFER SORT                         79682   329274      31281030
     TABLE ACCESS FULL  TBL_VARIOUS     87      329274      31281030

Group by is too slow... How does it speed up?

Upvotes: 4

Views: 8081

Answers (3)

Martin
Martin

Reputation: 1622

Any row in b that has null values in all the coalesce'd columns and is not null in b.sect and b.m_code will match any row in a, ie do a cartesian join.

My guess is that this is causing the problem. Even if there actually are no such rows in b, the optimizer may go for the cartesian join.

You may be able to avoid this by adding 

and (b.part is not null or b.nums is not null or b.intersec is not null or etc... )

Also, its always a good idea to keep your statistics up-to-date (though I'm not enough of an Oracle expert to know how to do that).

EDIT: This is functionally identical and might work better:

and (b.part = a.part or b.nums = a.nums or b.intersec = a.intersec or etc... )

This will make sure that at least one value matches.

Upvotes: 1

Mark J. Bobak
Mark J. Bobak

Reputation: 14423

Any time you have a CARTESIAN JOIN, it could indicate that you missed a join condition when you wrote the query. Please check your joins, see if you missed one.

Upvotes: 1

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50057

Given the presence of those COALESCE invocations in the WHERE clause I honestly don't know if this can be sped up. However, hope springs eternal... Try adding the following indexes and see what happens:

TBL_VARIOUS
  PART
  NUMS
  INTERC
  SEGMENT
  ACOUNTRY_MIDAS
  ORIG_NAME
  FACT
  S_ID

TBL_MAP
  PART
  NUMS
  INTERC
  SEGMENT
  ACOUNTRY
  ORIG_NAME
  FACT
  SECT
  M_CODE
  (M_GROUP, M_CODE)

TBL_SHEET
  M_CODE
  ID_SK

Share and enjoy.

Upvotes: 0

Related Questions