Re: Optimizing inline view

From: Mike C <michaeljc70_at_hotmail.com>
Date: 7 Nov 2006 11:51:02 -0800
Message-ID: <1162929062.313742.233190@i42g2000cwa.googlegroups.com>

Charles Hooper wrote:
> 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

>

> I don't see anything really odd in the above explain plan, you might
> try a DBMS_XPLAN to see if it helps - Daniel Morgan recently posted how
> to work with this feature in another thread.

>

> Looking at the SQL statement (reformatted so that I can follow it):
> 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;

>

> QT_UTIL.GET_SUBREGION(L1.LEFT_ADMIN_PLACE_ID) REGION - this seems to
> be a PL/SQL function, is it optimized?

>


> NVL(

>     (SELECT

>       'Y'

>      FROM

>        NT_DATE_TIME_MODIFIER DTM

>      WHERE

>        ROWNUM<2

>        AND DTM.CONDITION_ID=X.CONDITION_ID),'N') DATE_MODIFIERS

> - might be better to throw this into an inline view. I see that you
> are stopping after the first row is retrieved, there may be better
> ways.

>


>   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

>

> Looks like an intentional Cartesian join. It might also help to
> specify AND L2.FUNCTIONAL_CLASS<5. Maybe if you prejoin each of these
> copies of TMP_LINKS with NT_LINK, and then join those results with each
> other it would help.

>

> With these changes, your SQL statement _might_ look similar to this:
> 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,
> NVL2(DTM.MATCHES,'Y','N') DATE_MODIFIERS
> FROM
> (SELECT
> L1.LINK_PVID_1,
> L1.CONDITION_ID,
> L1.FC_1,
> L1.NAV_STRAND_ID,
> L2.LINK_PVID_2,
> L2.FC_2
> FROM
> (SELECT
> L1.LINK_PVID LINK_PVID_1,
> TL1.CONDITION_ID,
> TL1.FUNCTIONAL_CLASS FC_1,
> TL1.NAV_STRAND_ID
> FROM
> TMP_LINKS TL1,
> NT_LINK L1
> WHERE
> TL1.SEQUENCE_NUMBER=0
> AND TL1.CNT=2
> AND TL1.FUNCTIONAL_CLASS<5
> AND TL1.LINK_ID=L1.LINK_ID) L1,
> (SELECT
> L2.LINK_PVID LINK_PVID_2,
> TL2.FUNCTIONAL_CLASS FC_2,
> TL2.NAV_STRAND_ID
> FROM
> TMP_LINKS TL2,
> NT_LINK L2
> WHERE
> TL2.SEQUENCE_NUMBER=1
> AND TL2.FUNCTIONAL_CLASS<5
> AND TL2.LINK_ID=L2.LINK_ID) L2
> WHERE
> L2.NAV_STRAND_ID=L1.NAV_STRAND_ID
> AND L2.FC_2<=L1.FC_1) X,
> (SELECT
> CONDITION_ID,
> COUNT(*) MATCHES
> FROM
> NT_DATE_TIME_MODIFIER
> GROUP BY
> CONDITION_ID) DTM,
> NT_CONDITION_MODIFIER CM
> WHERE
> X.CONDITION_ID=DTM.CONDITION_ID(+)
> AND X.CONDITION_ID=CM.CONDITION_ID(+)
> AND CM.TYPE(+)=1;

>

> Without understanding your data model, the above could execute faster,
> the same, or slower than the SQL that you posted. I am not sure that
> this exercise will help you resolve the problem, but it may be another
> way to look at the SQL statement.

>

> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.

I appreciate the help. I did run the above query and it seemed to chug along pretty slowly (no results after 40 min). Since this problem is basically solved with using the temp table, I am wondering more in general. I mean, yes I can create temp table(s) and break things up to get them to execute faster. Maybe that is the route to go, maybe it looks amateurish. I've noticed if you get beyond around 6 tables, Oracle is more iffy with picking the best join order . That is why I was wondering if there is a hint or more elegant way to tell Oracle "do this first" without creating temp tables. On less than 6 tables, I can throw in a hint and usually get it to do what I want and it isn't usually an issue. Received on Tue Nov 07 2006 - 13:51:02 CST