query optimisation

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • ymrmcm
    New Member
    • May 2007
    • 1
    #1

    query optimisation

    Hi all,

    Iam trying to run below query, its taking lot of time .

    query:

    SELECT
    LN.STORE_CD, LN.LOC_CD, LN.DEL_DOC_NUM,
    LN.DEL_DOC_LN#, LN.ITM_CD, LN.QTY*100,
    LN.OUT_CD, LN.OUT_ID_CD
    FROM LOC LOC, SO_LN LN
    WHERE EXISTS (SELECT 'X'
    FROM STORE S2
    WHERE S2.STORE_CD = LN.STORE_CD
    AND NVL(S2.RF_WHSE, 'N') = 'Y')
    AND EXISTS (SELECT 'X'
    FROM SO SO2
    WHERE SO2.DEL_DOC_NUM = LN.DEL_DOC_NUM
    AND SO2.STAT_CD = 'O'
    AND SO2.ORD_TP_CD IN ('SAL','EEX','R ES'))
    AND EXISTS (SELECT 'X'
    FROM ITM I2
    WHERE I2.ITM_CD = LN.ITM_CD
    AND I2.ITM_TP_CD != 'CPT'
    AND NVL(I2.BULK_TP_ ITM,'N') != 'Y')
    AND LN.VOID_FLAG = 'N'
    AND LN.STORE_CD IS NOT NULL
    AND LN.LOC_CD IS NOT NULL
    AND LOC.LOC_CD = LN.LOC_CD
    AND LOC.STORE_CD = LN.STORE_CD
    AND LN.PICKED || '' = 'F'
    AND NOT EXISTS (SELECT 'X'
    FROM IST_LN$SO_LN ILSL , INV_XREF IX
    WHERE ILSL.DEL_DOC_NU M = LN.DEL_DOC_NUM
    AND ILSL.DEL_DOC_LN # = LN.DEL_DOC_LN#
    AND IX.DEL_DOC_NUM = ILSL.DEL_DOC_NU M
    AND IX.DEL_DOC_LN# = ILSL.DEL_DOC_LN #
    AND IX.DISPOSITION = 'PEN' )
    ORDER BY LN.STORE_CD, LN.LOC_CD, LN.DEL_DOC_NUM, LN.DEL_DOC_LN#, LN.ITM_CD

    Explain plan:
    Rows Execution Plan
    ------- ---------------------------------------------------
    0 SELECT STATEMENT GOAL: RULE
    7367 SORT (ORDER BY)
    7367 FILTER
    37023 NESTED LOOPS
    37040 TABLE ACCESS (FULL) OF 'SO_LN'
    37023 INDEX (UNIQUE SCAN) OF 'LOC_PK' (UNIQUE)
    4 TABLE ACCESS (BY INDEX ROWID) OF 'STORE'
    270 INDEX (UNIQUE SCAN) OF 'STORE_PK' (UNIQUE)
    8402 TABLE ACCESS (BY INDEX ROWID) OF 'SO'
    9129 INDEX (UNIQUE SCAN) OF 'SO1' (UNIQUE)
    1957 TABLE ACCESS (BY INDEX ROWID) OF 'ITM'
    3585 INDEX (UNIQUE SCAN) OF 'ITM_PK' (UNIQUE)
    13 NESTED LOOPS
    26264432 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
    'INV_XREF'
    26264432 INDEX GOAL: ANALYZED (RANGE SCAN) OF
    'INV_XREF_DISPO SITION' (NON-UNIQUE)
    13 INDEX (RANGE SCAN) OF 'IST_LN$SO_LN1' (NON-UNIQUE)

    No. of rows in my SO_LN table is 954554

    No. of rows in my LOC table is 12030

    optimizer mode is RULE

    what is wrong with this?

    can anybody help me out...

    Regards
    Mallikarjun
    Tags: None
    • chandu031
      Recognized Expert New Member
      • Mar 2007
      • 77
      #2
      Originally posted by ymrmcm
      Hi all,

      Iam trying to run below query, its taking lot of time .

      query:

      SELECT
      LN.STORE_CD, LN.LOC_CD, LN.DEL_DOC_NUM,
      LN.DEL_DOC_LN#, LN.ITM_CD, LN.QTY*100,
      LN.OUT_CD, LN.OUT_ID_CD
      FROM LOC LOC, SO_LN LN
      WHERE EXISTS (SELECT 'X'
      FROM STORE S2
      WHERE S2.STORE_CD = LN.STORE_CD
      AND NVL(S2.RF_WHSE, 'N') = 'Y')
      AND EXISTS (SELECT 'X'
      FROM SO SO2
      WHERE SO2.DEL_DOC_NUM = LN.DEL_DOC_NUM
      AND SO2.STAT_CD = 'O'
      AND SO2.ORD_TP_CD IN ('SAL','EEX','R ES'))
      AND EXISTS (SELECT 'X'
      FROM ITM I2
      WHERE I2.ITM_CD = LN.ITM_CD
      AND I2.ITM_TP_CD != 'CPT'
      AND NVL(I2.BULK_TP_ ITM,'N') != 'Y')
      AND LN.VOID_FLAG = 'N'
      AND LN.STORE_CD IS NOT NULL
      AND LN.LOC_CD IS NOT NULL
      AND LOC.LOC_CD = LN.LOC_CD
      AND LOC.STORE_CD = LN.STORE_CD
      AND LN.PICKED || '' = 'F'
      AND NOT EXISTS (SELECT 'X'
      FROM IST_LN$SO_LN ILSL , INV_XREF IX
      WHERE ILSL.DEL_DOC_NU M = LN.DEL_DOC_NUM
      AND ILSL.DEL_DOC_LN # = LN.DEL_DOC_LN#
      AND IX.DEL_DOC_NUM = ILSL.DEL_DOC_NU M
      AND IX.DEL_DOC_LN# = ILSL.DEL_DOC_LN #
      AND IX.DISPOSITION = 'PEN' )
      ORDER BY LN.STORE_CD, LN.LOC_CD, LN.DEL_DOC_NUM, LN.DEL_DOC_LN#, LN.ITM_CD

      Explain plan:
      Rows Execution Plan
      ------- ---------------------------------------------------
      0 SELECT STATEMENT GOAL: RULE
      7367 SORT (ORDER BY)
      7367 FILTER
      37023 NESTED LOOPS
      37040 TABLE ACCESS (FULL) OF 'SO_LN'
      37023 INDEX (UNIQUE SCAN) OF 'LOC_PK' (UNIQUE)
      4 TABLE ACCESS (BY INDEX ROWID) OF 'STORE'
      270 INDEX (UNIQUE SCAN) OF 'STORE_PK' (UNIQUE)
      8402 TABLE ACCESS (BY INDEX ROWID) OF 'SO'
      9129 INDEX (UNIQUE SCAN) OF 'SO1' (UNIQUE)
      1957 TABLE ACCESS (BY INDEX ROWID) OF 'ITM'
      3585 INDEX (UNIQUE SCAN) OF 'ITM_PK' (UNIQUE)
      13 NESTED LOOPS
      26264432 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
      'INV_XREF'
      26264432 INDEX GOAL: ANALYZED (RANGE SCAN) OF
      'INV_XREF_DISPO SITION' (NON-UNIQUE)
      13 INDEX (RANGE SCAN) OF 'IST_LN$SO_LN1' (NON-UNIQUE)

      No. of rows in my SO_LN table is 954554

      No. of rows in my LOC table is 12030

      optimizer mode is RULE

      what is wrong with this?

      can anybody help me out...

      Regards
      Mallikarjun

      Hi ,

      Did you collect statistics on the table? Try it out and see if lessens the time.

      Cheers!!

        Comment

        • bonzi1405
          New Member
          • May 2007
          • 5
          #3
          can you try this out and let me know...........



          SELECT LN.STORE_CD, LN.LOC_CD, LN.DEL_DOC_NUM, LN.DEL_DOC_LN#, LN.ITM_CD, LN.QTY*100,
          LN.OUT_CD, LN.OUT_ID_CD
          FROM LOC LOC, SO_LN LN,STORE S2,SO SO2 ,ITM I2,IST_LN$SO_LN ILSL , INV_XREF IX
          where S2.STORE_CD = LN.STORE_CD
          and ND NVL(S2.RF_WHSE, 'N') = 'Y'
          and SO2.DEL_DOC_NUM = LN.DEL_DOC_NUM
          AND SO2.STAT_CD = 'O'
          AND SO2.ORD_TP_CD IN ('SAL','EEX','R ES')
          and I2.ITM_CD = LN.ITM_CD
          AND I2.ITM_TP_CD != 'CPT'
          AND NVL(I2.BULK_TP_ ITM,'N') != 'Y'
          AND LN.VOID_FLAG = 'N'
          AND LN.STORE_CD IS NOT NULL
          AND LN.LOC_CD IS NOT NULL
          AND LOC.LOC_CD = LN.LOC_CD
          AND LOC.STORE_CD = LN.STORE_CD
          AND LN.PICKED || '' = 'F'
          and ILSL.DEL_DOC_NU M <> LN.DEL_DOC_NUM
          AND ILSL.DEL_DOC_LN # <> LN.DEL_DOC_LN#
          AND IX.DEL_DOC_NUM <> ILSL.DEL_DOC_NU M
          AND IX.DEL_DOC_LN# <> ILSL.DEL_DOC_LN #
          AND IX.DISPOSITION <> 'PEN'
          ORDER BY LN.STORE_CD, LN.LOC_CD, LN.DEL_DOC_NUM, LN.DEL_DOC_LN#, LN.ITM_CD

            Comment

            • debasisdas
              Recognized Expert Expert
              • Dec 2006
              • 8119
              #4
              Originally posted by ymrmcm
              Hi all,

              Iam trying to run below query, its taking lot of time .

              query:

              SELECT
              LN.STORE_CD, LN.LOC_CD, LN.DEL_DOC_NUM,
              LN.DEL_DOC_LN#, LN.ITM_CD, LN.QTY*100,
              LN.OUT_CD, LN.OUT_ID_CD
              FROM LOC LOC, SO_LN LN
              WHERE EXISTS (SELECT 'X'
              FROM STORE S2
              WHERE S2.STORE_CD = LN.STORE_CD
              AND NVL(S2.RF_WHSE, 'N') = 'Y')
              AND EXISTS (SELECT 'X'
              FROM SO SO2
              WHERE SO2.DEL_DOC_NUM = LN.DEL_DOC_NUM
              AND SO2.STAT_CD = 'O'
              AND SO2.ORD_TP_CD IN ('SAL','EEX','R ES'))
              AND EXISTS (SELECT 'X'
              FROM ITM I2
              WHERE I2.ITM_CD = LN.ITM_CD
              AND I2.ITM_TP_CD != 'CPT'
              AND NVL(I2.BULK_TP_ ITM,'N') != 'Y')
              AND LN.VOID_FLAG = 'N'
              AND LN.STORE_CD IS NOT NULL
              AND LN.LOC_CD IS NOT NULL
              AND LOC.LOC_CD = LN.LOC_CD
              AND LOC.STORE_CD = LN.STORE_CD
              AND LN.PICKED || '' = 'F'
              AND NOT EXISTS (SELECT 'X'
              FROM IST_LN$SO_LN ILSL , INV_XREF IX
              WHERE ILSL.DEL_DOC_NU M = LN.DEL_DOC_NUM
              AND ILSL.DEL_DOC_LN # = LN.DEL_DOC_LN#
              AND IX.DEL_DOC_NUM = ILSL.DEL_DOC_NU M
              AND IX.DEL_DOC_LN# = ILSL.DEL_DOC_LN #
              AND IX.DISPOSITION = 'PEN' )
              ORDER BY LN.STORE_CD, LN.LOC_CD, LN.DEL_DOC_NUM, LN.DEL_DOC_LN#, LN.ITM_CD

              Explain plan:
              Rows Execution Plan
              ------- ---------------------------------------------------
              0 SELECT STATEMENT GOAL: RULE
              7367 SORT (ORDER BY)
              7367 FILTER
              37023 NESTED LOOPS
              37040 TABLE ACCESS (FULL) OF 'SO_LN'
              37023 INDEX (UNIQUE SCAN) OF 'LOC_PK' (UNIQUE)
              4 TABLE ACCESS (BY INDEX ROWID) OF 'STORE'
              270 INDEX (UNIQUE SCAN) OF 'STORE_PK' (UNIQUE)
              8402 TABLE ACCESS (BY INDEX ROWID) OF 'SO'
              9129 INDEX (UNIQUE SCAN) OF 'SO1' (UNIQUE)
              1957 TABLE ACCESS (BY INDEX ROWID) OF 'ITM'
              3585 INDEX (UNIQUE SCAN) OF 'ITM_PK' (UNIQUE)
              13 NESTED LOOPS
              26264432 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
              'INV_XREF'
              26264432 INDEX GOAL: ANALYZED (RANGE SCAN) OF
              'INV_XREF_DISPO SITION' (NON-UNIQUE)
              13 INDEX (RANGE SCAN) OF 'IST_LN$SO_LN1' (NON-UNIQUE)

              No. of rows in my SO_LN table is 954554

              No. of rows in my LOC table is 12030

              optimizer mode is RULE

              what is wrong with this?

              can anybody help me out...

              Regards
              Mallikarjun
              Hi
              ymrmcm
              Welcome to TSDN.

              You have reached the right place for knowledge shairing.

              Here you will find a vast resource of related topics and code.

              Feel free to post more doubts/questions in the forum.

              But before that give a try from your side and if possible try to post what/how you have approached to solve the problem.

              It will help Experts in the forum in solving/underestanding your problem in a better way.

              Please follow the posting guidelines in every new post/reply and use proper CODE n other tags. In that way your code will be more readable.

                Comment

                • debasisdas
                  Recognized Expert Expert
                  • Dec 2006
                  • 8119
                  #5
                  May I know please what exactly is the problem u are facing in the query or its a performance issue.

                    Comment

                    • jsmithstl
                      New Member
                      • Jun 2007
                      • 14
                      #6
                      Statistics have no bearing when OPTIMIZER = RULE Change your OPTIMIZER to COST or CHOOSE then Oracle will use the statistics gathered through the analyze command or better yet use the dbms_stats package.

                        Comment

                        • nsreedhar
                          New Member
                          • Jun 2007
                          • 1
                          #7
                          Hi,

                          Which version of Oracle U R working on.
                          How come rule based optimizer in the age of 10g.

                          How many rows are there in tables SO, ITM, IST_LN$SO_LN.

                          Is it necessary to user exists as this creates co-related query and your outer table goes for full table scan.

                          If the rows of table SO, ITM, IST_LN$SO_LN are lesser go for 'IN' operator.

                          This might give u better result.

                          But 'CBO' or 'CHOOSE' optimizer first.

                          Thanks
                          N Sreedhar

                            Comment

                            Previous template Next
                            Working...