Optimizing inline view

#11

Mike C wrote:

Quote:

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.

#12

On 09.11.2006 20:31, Mike C wrote:

Quote:

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.

Does it make any difference now that your stats are up to date?

robert

#13

Robert Klemme wrote:

Quote:

On 09.11.2006 20:31, Mike C wrote:
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.

Does it make any difference now that your stats are up to date?

robert

In this instance, yes it made a world of difference. However, I had
several other queries that get a set of data based on criteria. Then
there are additonal fields gathered to supplement that data from other
tables that are joined in. Often, it wants to join in those tables
with the supplemental data that have millions of rows at the beginning,
making the cost huge. Since the queires only returns hundreds of rows,
it is much more efficient to narrow down the records first, then get
the supplemental fields for those rows. I can force this by using
PUSH_SUBQ and making the base query a subquery (actually, inline view).

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Optimizing inline view

comp.databases.oracle.misc comp.databases.oracle.misc

Re: Optimizing inline view - 11-09-2006 , 01:31 PM

Re: Optimizing inline view - 11-10-2006 , 06:29 AM

Re: Optimizing inline view - 11-10-2006 , 10:22 AM