-
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;
Last edited by gandolf989; 08-16-2004 at 09:14 AM.
-
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?
- Cookies
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|