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

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;

---

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

What does the explain plan look like?

could this be a situation where they should start storing all their data in upper case?

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

---

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