2 Replies Latest reply: Mar 24, 2013 4:46 AM by Arturo Gutierrez RSS

    How to disable MERJE JOIN CARTESIAN in Oracle9i?

    Arturo Gutierrez
      Hello,
      I have an execution plan using CBO optimizer in a SQL joins with multiple operations. The tables are properly related to each other, so it s not justified by the Cartesian product joins Union problems. Tables have updated statistics. I tried to use hints to force a hash join for but can not. I suspect that the Cartesian product is performed by applying the rules of transitivity level optimizer.
      Any know how to disable this transformation for the use sql Hash Joins?
      The BD version is 9.2.0.8

      The execution plan is:
      Execution Plan
      ----------------------------------------------------------
      0 SELECT STATEMENT Optimizer=CHOOSE (Cost=167668961512454000 Card=2739657621013990 Bytes=1213668326109200000)
      1 0 FILTER
      2 1 SORT (GROUP BY) (Cost=167668961512454000 Card=2739657621013990 Bytes=1213668326109200000)
      3 2 MERGE JOIN (Cost=167668961332844000 Card=441199942603335000000 Bytes=195451574573277000000000)
      4 3 SORT (JOIN) (Cost=167668961332839000 Card=142777672565453000000 Bytes=48544408672254100000000)
      5 4 MERGE JOIN (CARTESIAN) (Cost=167668954679307000 Card=142777672565453000000 Bytes=48544408672254100000000)
      6 5 MERGE JOIN (Cost=1864734714877 Card=1524246272223560 Bytes=399352523322573000)
      7 6 SORT (JOIN) (Cost=1864734713198 Card=664093883338651 Bytes=155397968701244000)
      8 7 MERGE JOIN (CARTESIAN) (Cost=1860854406653 Card=664093883338651 Bytes=155397968701244000)
      9 8 MERGE JOIN (CARTESIAN) (Cost=21152201 Card=14425063988 Bytes=1932958574392)
      10 9 TABLE ACCESS (FULL) OF 'DPOLCOBE' (Cost=1022 Card=111911 Bytes=6043194)
      11 9 BUFFER (SORT) (Cost=21151179 Card=128898 Bytes=10311840)
      12 11 TABLE ACCESS (FULL) OF 'DPOLPER' (Cost=189 Card=128898 Bytes=10311840)
      13 8 BUFFER (SORT) (Cost=1860854406464 Card=46038 Bytes=4603800)
      14 13 TABLE ACCESS (FULL) OF 'DPOLIZAS' (Cost=129 Card=46038 Bytes=4603800)
      15 6 SORT (JOIN) (Cost=1679 Card=208909 Bytes=5849452)
      16 15 TABLE ACCESS (FULL) OF 'DPOLSCON' (Cost=577 Card=208909 Bytes=5849452)
      17 5 BUFFER (SORT) (Cost=167668954679306000 Card=93671 Bytes=7306338)
      18 17 TABLE ACCESS (FULL) OF 'DPERSONA' (Cost=110 Card=93671 Bytes=7306338)
      19 3 SORT (JOIN) (Cost=5560 Card=290500 Bytes=29921500)
      20 19 TABLE ACCESS (FULL) OF 'DPOLPECO' (Cost=924 Card=290500 Bytes=29921500)


      Many Thanks
      Arturo
        • 1. Re: How to disable MERJE JOIN CARTESIAN in Oracle9i?
          SomeoneElse
          Please use code tags to preserve formatting of things like this. This is unreadable. And post the actual query as well. Thread is kind of useless without it.

          Put one of these before your code and one after:
                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
          • 2. Re: How to disable MERJE JOIN CARTESIAN in Oracle9i?
            Arturo Gutierrez
            Hello,
            The sql is:
            select  *
             from  ntjdatsba.dpolper peri,
              (SELECT id_dpolizas_fk, MAX(b.dpolcobe_scon) dpolcobe_scon , MAX(b.dpolpeco_scon) dpolpeco_scon, MAX(b.dpolper_scon) dpolper_scon
                       FROM ntjdatsba.dpolscon b
                        WHERE
                        fechemis <= TRUNC(SYSDATE)
                  GROUP BY id_dpolizas_fk
                  ) scon,
              ntjdatsba.dpolizas pol,
              ntjdatsba.dpersona pers,
              ntjdatsba.dpolpeco peco,
              ntjdatsba.dpolcobe cobe
              WHERE  pol.id_dpolizas = scon.id_dpolizas_fk
                 AND pol.estado = 'V'
                 AND cobe.id_dpolscon_fk = scon.dpolcobe_scon
                AND cobe.garancod = 'AC01'
                AND peco.id_dpolscon_fk = scon.dpolpeco_scon
                AND peco.relacion IN ('CHAB', 'COCA')
                 AND peco.id_dpersona_fk = pers.id_dpersona
                 AND peri.id_dpolscon_fk = scon.dpolper_scon
                 AND peri.poliefec <= TRUNC (SYSDATE); 
            And the execution plan (I hope know is better formated).
            Execution Plan
            ----------------------------------------------------------
               0      SELECT STATEMENT Optimizer=CHOOSE (Cost=167668961512454000 Card=2739657621013990 Bytes=1213668326109200000)
               1    0   FILTER
               2    1     SORT (GROUP BY) (Cost=167668961512454000 Card=2739657621013990 Bytes=1213668326109200000)
               3    2       MERGE JOIN (Cost=167668961332844000 Card=441199942603335000000 Bytes=195451574573277000000000)
               4    3         SORT (JOIN) (Cost=167668961332839000 Card=142777672565453000000 Bytes=48544408672254100000000)
               5    4           MERGE JOIN (CARTESIAN) (Cost=167668954679307000 Card=142777672565453000000 Bytes=48544408672254100000000)
               6    5             MERGE JOIN (Cost=1864734714877 Card=1524246272223560 Bytes=399352523322573000)
               7    6               SORT (JOIN) (Cost=1864734713198 Card=664093883338651 Bytes=155397968701244000)
               8    7                 MERGE JOIN (CARTESIAN) (Cost=1860854406653 Card=664093883338651 Bytes=155397968701244000)
               9    8                   MERGE JOIN (CARTESIAN) (Cost=21152201 Card=14425063988 Bytes=1932958574392)
              10    9                     TABLE ACCESS (FULL) OF 'DPOLCOBE' (Cost=1022 Card=111911 Bytes=6043194)
              11    9                     BUFFER (SORT) (Cost=21151179 Card=128898 Bytes=10311840)
              12   11                       TABLE ACCESS (FULL) OF 'DPOLPER' (Cost=189 Card=128898 Bytes=10311840)
              13    8                   BUFFER (SORT) (Cost=1860854406464 Card=46038 Bytes=4603800)
              14   13                     TABLE ACCESS (FULL) OF 'DPOLIZAS' (Cost=129 Card=46038 Bytes=4603800)
              15    6               SORT (JOIN) (Cost=1679 Card=208909 Bytes=5849452)
              16   15                 TABLE ACCESS (FULL) OF 'DPOLSCON' (Cost=577 Card=208909 Bytes=5849452)
              17    5             BUFFER (SORT) (Cost=167668954679306000 Card=93671 Bytes=7306338)
              18   17               TABLE ACCESS (FULL) OF 'DPERSONA' (Cost=110 Card=93671 Bytes=7306338)
              19    3         SORT (JOIN) (Cost=5560 Card=290500 Bytes=29921500)
              20   19           TABLE ACCESS (FULL) OF 'DPOLPECO' (Cost=924 Card=290500 Bytes=299215
            Thanks
            Arturo