Re: Optimizing inline view

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 7 Nov 2006 10:48:04 -0800
Message-ID: <1162925284.252876.154850@h54g2000cwb.googlegroups.com>

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. Received on Tue Nov 07 2006 - 12:48:04 CST