Re: Why does Oracle do Merge Join Cartesian when it causes execution to go from 1 min to 8 hours?

On Feb 23, 10:17 pm, mist..._at_mail.com (Mistton) wrote:
> using Oracle 9.2.0.3 on Solaris 8 on Sun Server E15 with 10 CPUS 4 gig ram 2
> TB disk
>
> we have three tables
>
> tableA
> 10k rows
>
> tableb
> 86 million
>
> tableC
> 100k
>
> we do following qurey
>
> select tableA.col1, tableB.Col1, tableC.col1
> from
> A, B, C
> where
> tableA.co1 = tableB.col1 and tableB.col2 = tableC.col1
>
> this completes in 39 secs.
>
> we then add one more where conditon we need two columns to be equal in tableA
> - and tableA.col1 = tableA.col4.
>
> this takes 8 hours to run
>
> plan for query for 39 secs (note that we are able to use the main index on
> tableB to get all cols so no need to access table index is 7.5 gig but we do a
> range scan
>
> SELECT STATEMENT Optimizer Mode=CHOOSE 1 86
>
> SORT GROUP BY 1 100 86
> HASH JOIN 1 100 83
> NESTED LOOPS 1 79 79
> TABLE ACCESS FULL TRST_WORK 1 25 56
> INDEX RANGE SCAN IND_TREX_ACTUALS 2 108 23
> INDEX FAST FULL SCAN IND_A7_DEPT 1 K 21 K 3
>
> when we add tableA.col1 = tableA.col4. it goes from 39 secs to 8 hours note
> plan now has a Merge Join Cartesian
>
> SELECT STATEMENT Optimizer Mode=CHOOSE 1 85
> SORT GROUP BY 1 100 85
> NESTED LOOPS 1 100 82
> MERGE JOIN CARTESIAN 1 46 59
> TABLE ACCESS FULL TRST_WORK 1 25 56
> BUFFER SORT 1 21 3
> INDEX FAST FULL SCAN IND_A7_DEPT 1 21 3
> INDEX RANGE SCAN IND_TREX_ACTUALS 1 54 23
>
> tables were fully analysed with DBMS_STATS, etc but nothing changed
>
> we finally added an Ordered Hint and that changed the plan to the good one.
>
> why is this happening?? why is Oracle fooled so easily?
>
> Is this bug: 3444115 CBO may choose inappropriate CARTESIAN join?
>
> or is it just nature of the beast?
>
> please help. we want to get away from using hints

You did not provide column headings for the various columns, but it looks like Oracle is predicting only a single row to be returned. A Cartesian join between 1 row in one row source and 1 row in another row source results in 1 * 1 result rows. Oracle is possibly wrong.

Are there any check constraints on the tables, particularly on the tableA.col1, tableA.col4, tableA.co1, tableB.col1, tableB.col2, and tableC.col1 columns? Check constraints (and possibly foreign keys) can cause additional predicates to be created. When those predicates are generated, Oracle has the opportunity to discard some table to table join conditions. When the join conditions are discarded, a Cartesian join will result.

You will notice that the second plan is calculated as slightly less expensive.

Do you see the same behavior if you change:   and tableA.col1 = tableA.col4
To:
  and NVL(tableA.col1,'tableA.col1 IS NULL') = NVL(tableA.col4,'tableA.col4 IS NULL')

If the above still does not resolve the problem, start taking a look at DBMS Xplans, 10046 traces, and 10053 traces.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Fri Feb 23 2007 - 22:03:32 CST