Pb with MERGE JOIN (CARTESIAN)

From: zeb <thierry.constant1.nospam_at_free.fr>
Date: Tue, 30 Dec 2003 21:33:08 +0100
Message-ID: <3ff1e073$0$24019$626a54ce@news.free.fr>

Hi all,

I have the following request:
time: 15 min
sort_ area_size = 1048576

I did:
alter session set sort_area_size = 65536

=> MERGE JOIN (CARTESIAN) become NESTED LOOPS
time: 1 min

How can I get a NESTED LOOPS ?
put a HINT ? where ?

Any Ideas ?
Thanks in advance

    1 SELECT /*+ INDEX (BVCP BV_CATEGORY_PATH_I_OID_PATH_NA)
 INDEX (BVCR BV_CONTENT_REF_PK) */

  2                           DISTINCT bve.OID, bve.last_mod_time last_date,
  3                                    bvc.NAME catname, bvc.path_name PATH,
  4                                    bvas.user_id, bvas.alert_name,
  5                                    bvas.alert_id, bvas.dlvy_type,

bvas.statu
s,

  6                                    bvas.job_id, bvas.creation_date,
  7                                    bvas.modif_date, bvas.notif_date,
  8                                    bvas.notif_freq, bvas.category_oid,
  9                                    bvas.content_oid,
bvas.navigation_oid,
 10                                    bvas.PARAMETERS, bvup.email,
 11                                    bvup.NAME user_name, bvup.nx_lang_1

lang1
,

 12                                    bvup.nx_lang_2 lang2, bvup.nx_lang_3

lang
3

 13                               FROM bv.bv_category_path bvc,
 14                                    bv.bv_content_ref bvcr,
 15                                    bv.bv_editorial bve,
 16                                    bv.bv_ep_qv2 bvqv,
 17                                    bv.nexans_alert_spec bvas,
 18                                    bv.bv_user_profile bvup,
 19                                    bv.nexans_alert_spec

bvas3_nexans_alert_s
pec,

 20                                    bv.bv_category_path bvcp,
 21                                    (SELECT     bvcp2.OID,
bvcp2.parent_oid,
 22                                                bvcp2.NAME,
bvcp2.path_name,
 23                                                bvcp2.cat_level
 24                                           FROM bv.bv_category_path bvcp2
 25                                     START WITH bvcp2.path_name =

'/nexansv2/
'

 26                                     CONNECT BY PRIOR bvcp2.OID =

 27
bvcp2.parent_
oid) oid_list

 28                              WHERE bvcr.parent_oid = oid_list.OID
 29                                AND bvas.alert_name =

 30
bvas3_nexans_alert_spec.alert_n
ame

 31                                AND bve.OID = bvcr.OID
 32                                AND bve.last_mod_time >= bvas.notif_date
 33                                AND bvc.OID = bvcr.parent_oid
 34                                AND bve.status = 1
 35                                AND bve.OID = bvqv.OID(+)
 36                                AND (   bvqv.OID IS NULL
 37                                     OR (    bvqv.OID IS NOT NULL
 38                                         AND bvqv.OID NOT IN (
 39                                                SELECT OID
 40                                                  FROM bv.bv_ep_qv2
 41                                                 WHERE qvid NOT IN (
 42                                                          SELECT qvid
 43                                                            FROM

bv.bv_ep_upr
of_qval

 44                                                           WHERE user_id

=
 45
bvas.user
_id))

 46                                        )
 47                                    )
 48                                AND bvup.user_id = bvas.user_id
 49                                AND bvas3_nexans_alert_spec.category_oid

=
 50
bvcp.
OID

 51                                AND INSTR (oid_list.path_name,
 52                                           bvcp.path_name || bvcp.NAME
 53                                          ) > 0
 54                                AND INSTR

(bvas3_nexans_alert_spec.alert_name
,

 55                                           'ENT'
 56*                                         ) > 0

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=85 Card=1 Bytes=506)    1 0 SORT (UNIQUE) (Cost=85 Card=1 Bytes=506)

   2    1     FILTER
   3    2       NESTED LOOPS (Cost=81 Card=1 Bytes=506)
   4    3         NESTED LOOPS (Cost=79 Card=1 Bytes=428)
   5    4           HASH JOIN (Cost=78 Card=1 Bytes=383)
   6    5             MERGE JOIN (CARTESIAN) (Cost=75 Card=31 Bytes=85
          56)

   7    6               NESTED LOOPS (Cost=73 Card=1 Bytes=254)
   8    7                 NESTED LOOPS (OUTER) (Cost=72 Card=1 Bytes=1
          76)

   9    8                   NESTED LOOPS (Cost=71 Card=1 Bytes=169)
  10    9                     HASH JOIN (Cost=66 Card=5 Bytes=755)
  11   10                       VIEW (Cost=20 Card=5 Bytes=685)
  12   11                         CONNECT BY
  13   12                           INDEX (FAST FULL SCAN) OF 'BV_CATE
          GORY_PATH_I_OID_PATH_NA' (NON-UNIQUE) (Cost=19 Card=5 Bytes=
          280)

  14   12                           TABLE ACCESS (BY USER ROWID) OF 'B
          V_CATEGORY_PATH'

  15   12                           TABLE ACCESS (FULL) OF 'BV_CATEGOR
          Y_PATH' (Cost=20 Card=5 Bytes=355)

  16   10                       INDEX (FULL SCAN) OF 'BV_CONTENT_REF_P
          K' (UNIQUE) (Cost=45 Card=9067 Bytes=126938)

  17    9                     TABLE ACCESS (BY INDEX ROWID) OF 'BV_EDI
          TORIAL' (Cost=1 Card=2235 Bytes=40230)

  18   17                       INDEX (UNIQUE SCAN) OF 'BV_EDITORIAL_P
          K' (UNIQUE)

  19    8                   INDEX (RANGE SCAN) OF 'BV_EP_QV2_KEY_IDX'
          (NON-UNIQUE) (Cost=1 Card=80 Bytes=560)

  20    7                 TABLE ACCESS (BY INDEX ROWID) OF 'BV_CATEGOR
          Y_PATH' (Cost=1 Card=9932 Bytes=774696)

  21   20                   INDEX (UNIQUE SCAN) OF 'BV_CATEGORY_PATH_P
          K' (UNIQUE)

  22    6               SORT (JOIN) (Cost=74 Card=31 Bytes=682)
  23   22                 TABLE ACCESS (FULL) OF 'NEXANS_ALERT_SPEC' (
          Cost=2 Card=31 Bytes=682)

  24    5             TABLE ACCESS (FULL) OF 'NEXANS_ALERT_SPEC' (Cost
          =2 Card=607 Bytes=64949)

  25    4           TABLE ACCESS (BY INDEX ROWID) OF 'BV_USER_PROFILE'
           (Cost=1 Card=950 Bytes=42750)

  26   25             INDEX (UNIQUE SCAN) OF 'BV_USER_PROFILE_ID_IDX'
          (UNIQUE)

  27    3         INDEX (RANGE SCAN) OF 'BV_CATEGORY_PATH_I_OID_PATH_N
          A' (NON-UNIQUE) (Cost=2 Card=9932 Bytes=774696)

  28    2       INDEX (FULL SCAN) OF 'BV_EP_QV2_PK' (UNIQUE) (Cost=5 C
          ard=1 Bytes=14)

  29   28         TABLE ACCESS (BY INDEX ROWID) OF 'BV_EP_UPROF_QVAL'
          (Cost=2 Card=1 Bytes=12)

  30   29           INDEX (RANGE SCAN) OF 'BV_EP_UPROF_QVAL_KEY_IDX' (
          NON-UNIQUE) (Cost=1 Card=1)

Received on Tue Dec 30 2003 - 14:33:08 CST