SQL Tunning
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: SQL Tunning

  1. #1
    Join Date
    Jul 2003
    Location
    India
    Posts
    12

    SQL Tunning

    Hi All,


    I have two table

    Table A

    Col1 Col2 Col3 Qty
    ---- ---- ---- ----
    1 2 3 1
    1 2 2 2
    1 2 3 3


    Table B

    Col1 Col2 Col3 Qty
    ---- ---- ---- ----
    1 2 3 1
    1 2 3 2
    1 2 3 3
    1 2 3 4
    1 2 3 5
    1 2 3 6
    1 2 3 7

    Now i have to compare the quantity of two table based on Col1, Col2, Col3

    The query which i have written is

    SELECT SAP_INV.PLANT, SAP_INV.MATNR,
    Decode(SAP_INV.BATCH,'NOBATCHNO','',SAP_INV.BATCH),
    SAP_INV.SLOC,SAP_INV.SAP_QTY, USER_INV.USER_QTY,
    SAP_INV.SAP_QTY - USER_INV.USER_QTY QTY_DIFF
    FROM
    (SELECT UPPER(PLANT) PLANT,UPPER(MATNR) MATNR,
    UPPER(NVL (BATCH,'NOBATCHNO'))BATCH,UPPER(SLOC)SLOC,
    SUM(BOOK_QTY) SAP_QTY FROM PI_SLOC_SAP_INV
    GROUP BY UPPER(PLANT), UPPER(MATNR),
    UPPER(NVL(BATCH,'NOBATCHNO')),
    UPPER(SLOC)) SAP_INV,
    (SELECT UPPER(MATNR)MATNR,UPPER(NVL(BATCH,'NOBATCHNO'))BATCH,
    UPPER(SLOC)SLOC,SUM(COUNT_QTY) USER_QTY
    FROM PI_SLOC_USER_INV
    GROUP BY UPPER(MATNR),UPPER(NVL(BATCH,'NOBATCHNO')),
    UPPER(SLOC)) USER_INV
    WHERE SAP_INV.MATNR = USER_INV.MATNR
    AND SAP_INV.BATCH = USER_INV.BATCH
    AND SAP_INV.SLOC = USER_INV.SLOC

    What i have done is grouping of Table and A
    then Grouping of Table B and then Compare the resultset of this grouping.


    Can u suggest me anyother efficient way to write this query


    Thanks in advance

    With Regards

    Hitesh Parikh

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,968
    Do you really need the UPPER functions? Try it without the UPPER functions and see if it still works. i.e. Do you really have mixed case values? You used a decode where a NVL should work at the beggining.

    Code:
    SELECT sap_inv.plant, sap_inv.matnr,
           DECODE( sap_inv.batch, 'NOBATCHNO', '', sap_inv.batch ),
           sap_inv.sloc,
           sap_inv.sap_qty, user_inv.user_qty,
           sap_inv.sap_qty - user_inv.user_qty qty_diff
    FROM   ( SELECT  UPPER( plant ) plant, UPPER( matnr ) matnr,
                     UPPER( NVL( batch, 'NOBATCHNO' )) batch, 
                     UPPER( sloc ) sloc,
                     SUM( book_qty ) sap_qty
            FROM     pi_sloc_sap_inv
            GROUP BY UPPER( plant ),
                     UPPER( matnr ),
                     UPPER( NVL( batch, 'NOBATCHNO' )),
                     UPPER( sloc )) sap_inv,
           ( SELECT  UPPER( matnr ) matnr, 
                     UPPER( NVL( batch, 'NOBATCHNO' )) batch,
                     UPPER( sloc ) sloc, SUM( count_qty ) user_qty
            FROM     pi_sloc_user_inv
            GROUP BY UPPER( matnr ),
                     UPPER( NVL( batch, 'NOBATCHNO' )),
                     UPPER( sloc )) user_inv
    WHERE  sap_inv.matnr = user_inv.matnr AND
           sap_inv.batch = user_inv.batch AND
           sap_inv.sloc = user_inv.sloc;
    Last edited by gandolf989; 08-16-2004 at 09:14 AM.

  3. #3
    Join Date
    Jul 2003
    Location
    India
    Posts
    12
    Hi gandolf989,

    Thanks for you suggestion.

    I have mix value in any case, so i have to make use of upper.

    Second thing is nvl and decode i can remove that, but my question is Grouping the resultset an then compare the quantity is that OK or we can write the same query some other efficient way.

    With Regards

    Hitesh Parikh

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,968
    What does the explain plan look like?

  5. #5
    Join Date
    Oct 2002
    Posts
    182
    could this be a situation where they should start storing all their data in upper case?
    - Cookies

  6. #6
    Join Date
    Jul 2003
    Location
    India
    Posts
    12
    Plan for the query is

    Code:
    OPERATION                      OPTIONS         OBJECT_NAME
    ------------------------------ --------------- -------------------------
    SELECT STATEMENT
    MERGE JOIN
    SORT                           JOIN
    VIEW
    SORT                           GROUP BY
    TABLE ACCESS                   FULL            PI_SLOC_USER_INV
    SORT                           JOIN
    VIEW
    SORT                           GROUP BY
    TABLE ACCESS                   FULL            PI_SLOC_SAP_INV

  7. #7
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    If you think join between the 2 table may produce less number of rows in the result set, then try it also.

    Or you can add parallel hint for the tables used in the in-line views to speed up read.

    Tamil

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width