Re: Pb with MERGE JOIN (CARTESIAN)

"zeb" <thierry.constant1.nospam_at_free.fr> wrote in message news:<3ff1e073$0$24019$626a54ce_at_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)

I cant read your plan. its too messy. Are you stating that after you got the cartesian join your performance improved?

oracle will occassionally use them to improve performance. If you have a 3 table join. 2 tables dont have alot of data and the third has alot of data. Oracle will ocassionally cartesian join the two small tables and use it as a hash map to join to the larger table.

I cant be more specific. Ive only run across it a few times. Received on Wed Dec 31 2003 - 07:26:29 CST