SQL Tunning

[hitesh_parikh]

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

[gandolf989]

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.


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;

[hitesh_parikh]

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

[gandolf989]

What does the explain plan look like?

[Cookies]

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

[hitesh_parikh]

Plan for the query is


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

[tamilselvan]

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