Home » SQL & PL/SQL » SQL & PL/SQL » Is my assumption correct in the correlated update query
Is my assumption correct in the correlated update query [message #383962] Sun, 01 February 2009 02:01 Go to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member

UPDATE DOCUMENT_INEQIRY DI  SET DI.REMAIN_QTY = DI.REMAIN_QTY - (SELECT SUM(EMPLA.QTY) 
                           FROM EMPLOYEE_LOCATIONS EMPLA 
                WHERE EMPLA.INVENQ_ID = DI.ID)), 
      DI.BALANCE_QTY  = DI.BALANCE_QTY + 
                   (SELECT SUM(EMPLA.QTY) 
                        FROM EMPLOYEE_LOCATIONS EMPLA 
                      WHERE EMPLA.DI_ID = DI.ID)) 
 WHERE EXISTS 
	(SELECT 'X'    FROM EMPLOYEE_LOCATIONS EMPLA, EMPLOYEE_ITEMS EMPII 
 WHERE EMPLA.REF_ID_1 = EMPII.ID 
 AND EMPLA.DI_ID = DI.ID
AND EMPII.EMP_ID = 1;


This is the basic relation.
1 EMPLOYEE CAN MULTIPLE EMPLOYEE ITEMS;
1 EMPLOYEE ITEM CAN HAVE MULTIPLE EMPLOYEE_LOCATIONS;
1 DOCUMENT_INEQIRY CAN HAVE MULTIPLE EMPLOYEE_LOCATIONS;

above query updates the DOCUMENT_INEQIRY related REMAIN_QTY and BALANCE_QTY for a particular employee id(in this case 1st employee) by using DOCUMENT_INEQIRY id.above query is working fine.as you know correlated subquery in this case.

my doubt is how the query works in oracle.just tell me my assumption is correct or not.

1.first it will retrive all records with this query-SELECT * FROM DOCUMENT_INEQIRY(if it has lakhs of records it is performance problem) then it will pass each DOCUMENT_INEQIRY id to the sub query in exists clause like below one;
2.SELECT * FROM DOCUMENT_INEQIRY 
 WHERE EXISTS 
	(SELECT 'X'    FROM EMPLOYEE_LOCATIONS EMPLA, EMPLOYEE_ITEMS EMPII 
 WHERE EMPLA.REF_ID_1 = EMPII.ID 
 AND EMPLA.DI_ID ="it will pass each DOCUMENT_INEQIRY id that we get from step-1" 
AND EMPII.EMP_ID = 1

3.whatever DOCUMENT_INEQIRY id comes from step2 query it will pass that id to the two subqueries written SET clause.

whatever steps written is correct or not?if yes can we expect any performace related problem?

I know that we can write the above update query in different ways but which approach is better for perforamce oriented way?


Re: Is my assumption correct in the correlated update query [message #383980 is a reply to message #383962] Sun, 01 February 2009 06:13 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You can use EXPLAIN PLAN to find out how Oracle will execute the query.

First of all it will find the matching rows in DOCUMENT_INEQIRY, and then for each matching row, it will execute the two nested queries in the SET clause.

ie. If there are 100 matching rows, the nested queries will be executed 100 times.

This is fine if there are not many matches, but terrible if there are lots.

If there are few rows to update, you can tune the query by looking at its execution plan and tweaking it. Post it here and we can help.

If there are a lot of rows to update, this method is DOOMED!!! You will need to convert it into a MERGE statement. For example, you could plug the following into a MERGE:

SELECT DI.ROWID
,      DI.REMAIN_QTY - EMPLA.QTY AS REMAIN_QTY
,      EMPLA.QTY AS BALANCE_QTY
FROM   DOCUMENT_INEQIRY DI
JOIN (
    SELECT DI_ID, SUM(QTY) QTY
    FROM   EMPLOYEE_LOCATIONS
    WHERE  REF_ID_1 IN (
        SELECT EMPII.ID
        FROM   EMPLOYEE_ITEMS EMPII
        WHERE  EMPII.EMP_ID = 1
    GROUP BY DI_ID
) EMPLA
ON EMPLA.DI_ID = DI.DI_ID


Ross Leishman
Previous Topic: Like operator
Next Topic: CONNECT BY inner workings
Goto Forum:
  


Current Time: Fri Dec 02 20:32:20 CST 2016

Total time taken to generate the page: 0.06231 seconds