How to make it into one query?

[sreenath205]

Hi All

I have a procedure which runs fine,but since the data in table XYZ,ABC runs into millions it becomes too slow..Can any one guide me in optimising the code.I have already created indexes on these columns

CREATE OR REPLACE PROCEDURE test AS
    CURSOR c1 IS
        SELECT
            t.cutGrp,
            t.cutGrpCode,
            t.modelDesc,
            t.startdate,
            t.enddate,
            avg(t.price) price,
            avg(t.netprice) netprice, 
            sum(t.awdQTY) awardqty,
            sum(t.planQTY) revplanqty,
            
        FROM
            ABC t
        GROUP BY 
            t.cutGrp,
            t.cutGrpCode,
            t.modelDesc,
            t.startdate,
            t.enddate;
 
counter NUMBER := 0;
l_aggregate NUMBER;
BEGIN
     FOR rec IN c1
     LOOP
        counter := counter + 1;
 
        SELECT
            sum(t.anotherQTY) INTO l_aggregate
        FROM
             XYZ t
        WHERE
            t.cutstomer_Grp = rec.cutGrp
            AND t.cutGrp_Code = rec.cutGrpCode
            AND t.product_model = rec.modelDesc;
            
        UPDATE XYZ t 
        SET
            t.testQTY = l_aggregate,
            t.testprice = rec.price,
            t.testnetprice = rec.netprice,
            t.testawardQTY = rec.awardqty,
            t.testplanQTY = rec.revplanqty,          
            t.fcstartdate = rec.startdate,
            t.fcenddate = rec.enddate
        WHERE
            t.cutstomer_Grp = rec.cutGrp
            AND t.cutGrp_Code = rec.cutGrpCode
            AND t.cutGrp_Code = rec.modelDesc;
           
        
 
    END LOOP;
 
    COMMIT WORK;
 
END test;
/ 
SHOW ERRORS;

 

[SantaMufasa]

Sreenath, ??????, ??? ???? ??

After looking at your code, it seems to me that you could transform the code logic into a single (non-PL/)SQL statement. I propose that you attempt to make that transformation and post your code here.

Also, so that we may assist you better, please post "CREATE TABLE..." and "INSERT INTO <table>..." statement with just a few rows of data that will allow us to test both your code and our code suggestions.

???????

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[sem]

sreenath205,
I'm sure that your code needs more than Oracle optimization, its logic is quite suspicious.

First of all your cursor query is grouped by [also]..t.startdate,t.enddate so you get a distinct set of records for each pare of these values. Thus you make multiple updates of same rows in XYZ table. As Oracle doesn't guaranty the order of records returned without explicit order by clause, the result of your procedure is unpredictable.

Then XYZ is not normalized as each record with the same combination (cutstomer_Grp,cutGrp_Code,product_model) obtains the same, independent on other fields, testQTY value.

I think you should revize your code to make it correct before making it fast.

Regards, Dima

http://seminihin.narod.ru