Optimizing inline view

From: Mike C <michaeljc70_at_hotmail.com>
Date: 7 Nov 2006 09:46:36 -0800
Message-ID: <1162921596.315824.5700@f16g2000cwb.googlegroups.com>

I have a query with an inline view aliased as 'x'. When I run the query, it takes hours. If I take the inline view out, throw it into a temporary table and then run the query using that, it comes back in a few minutes. My question is is there a hint I can give it to process the inline view first? I tried using Ordered or Leading (x), but it didn't change the plan. The plan is listed below also.

SELECT l1.link_pvid link_pvid_1, fc_1, l2.link_pvid link_pvid_2, fc_2,
qt_util.get_subregion(l1.left_admin_place_id) region, cm.value cond_mod

,NVL((SELECT 'Y' FROM nt_date_Time_modifier dtm WHERE rownum<2 AND
dtm.condition_id= x.condition_id),'N') date_modifiers   FROM
(SELECT l1.condition_id,l1.link_id l1_link_id, l2.link_id l2_link_id,

l1.functional_class fc_1,
l2.functional_class fc_2 FROM tmp_links l1, tmp_links l2 WHERE
l1.cnt=2
AND l2.functional_class  <= l1.functional_class
AND l1.nav_strand_id=l2.nav_strand_id
and l1.sequence_number=0

and l2.sequence_number=1
and l1.functional_class<5
) x
, nt_link l1, nt_link l2, nt_condition_modifier cm
WHERE x.l1_link_id=l1.link_id and x.l2_link_id=l2.link_id AND cm.condition_id(+)=x.condition_id and cm.type(+)=1

Plan:
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=CHOOSE 1 8

  COUNT STOPKEY

    INDEX RANGE SCAN	EU_MAP_IW.PK_DATE_TIME_MODIFIER_CONDITIO	2  	14
	3
  NESTED LOOPS		1  	178  	8
    NESTED LOOPS		1  	164  	6
      NESTED LOOPS OUTER		1  	143  	4
        NESTED LOOPS		1  	130  	2
          TABLE ACCESS BY INDEX ROWID	EU_MAP_IW.TMP_LINKS	1  	78  	1

            INDEX RANGE SCAN	EU_MAP_IW.IDX_TMP_LINK2	1  	 	2

          TABLE ACCESS BY INDEX ROWID	EU_MAP_IW.TMP_LINKS	1  	52  	1

            INDEX RANGE SCAN	EU_MAP_IW.IDX_TMP_LINK2	1  	 	1

        TABLE ACCESS BY INDEX ROWID	EU_MAP_IW.NT_CONDITION_MODIFIER	1
	13  	2
          INDEX UNIQUE SCAN	EU_MAP_IW.PK_CONDITION_MODIFIER_CONDITIO	1
	 	1
      TABLE ACCESS BY INDEX ROWID	EU_MAP_IW.NT_LINK	1  	21  	2

        INDEX UNIQUE SCAN	EU_MAP_IW.PK_LINK_LINKID	1  	 	1

    TABLE ACCESS BY INDEX ROWID	EU_MAP_IW.NT_LINK	1  	14  	2

      INDEX UNIQUE SCAN	EU_MAP_IW.PK_LINK_LINKID	1  	 	1

Received on Tue Nov 07 2006 - 11:46:36 CST