SQL Tunning

[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;