Mike C wrote:
> 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
Just for reference, aside from the statistics problem, what I as
looking for is the PUSH_SUBQ hint. This forces subqueries to be
processed first.
Received on Thu Nov 09 2006 - 13:31:49 CST
