Update and Delete takes too long, Help...

From: Sally Madeira <sallym1_at_home.com>
Date: Fri, 15 Sep 2000 20:20:11 GMT
Message-ID: <%vvw5.41061$7W2.1469567@news1.wwck1.ri.home.com>

On a monthly basis I bring new records and updated records into our data warehouse
for reporting purposes and querying. The size of the tables to me are not that large (compared to
other warehouses) but my code seems to take way too long. Here is the senerio and the code.

I am using SQLPlus to run a sql statement that updates about 35 tables in the warehouse. The
statements are all similiar in fashion. I first bring all the records into tempory tables from datajunction
because we are bringing the data in from a btrieve system. One of the tables, Comdhis, is about 100,000
records. Once the temporary table is built I clean it up removing older records (this takes a long time).

I then do the same thing to the original table in the warehouse then I insert all the records left in the
temporary table into Comdhis. I timed this and it took over 30 minutes!

The index on both tables is by LoanNum, OfficeNum, CliNum and Date_R The statement is as follows:

--Comdhis
SET TRANSACTION USE ROLLBACK SEGMENT RB1; DELETE FROM DivisionEOD.temp_Comdhis
WHERE DATE_R <
TO_DATE('01'||TO_CHAR(Add_MONTHS(sysdate,-1),'MMYYYY'),'DDMMYYYY'); COMMIT; SET TRANSACTION USE ROLLBACK SEGMENT RB1; DELETE FROM DivisionEOD.Comdhis
WHERE LoanNum || OfficeNum || CliNum || Date_R IN

(SELECT DISTINCT LoanNum || OfficeNum || CliNum || Date_R

     FROM temp_Comdhis
     WHERE DATE_R BETWEEN

TO_DATE('01'||TO_CHAR(Add_MONTHS(sysdate,-1),'MMYYYY'),'DDMMYYYY') AND Add_MONTHS(sysdate,1)

               AND temp_Comdhis.loannum = divisioneod.Comdhis.loannum
               AND temp_Comdhis.OfficeNum = divisioneod.Comdhis.OfficeNum
               AND temp_Comdhis.clinum = divisioneod.Comdhis.clinum
               AND temp_Comdhis.Date_R = divisioneod.Comdhis.Date_R);

COMMIT; SET TRANSACTION USE ROLLBACK SEGMENT RB1; INSERT INTO DivisionEOD.Comdhis

(SELECT *

     FROM Temp_Comdhis
     WHERE OfficeNum Is Not Null
       AND DATE_R BETWEEN

TO_DATE('01'||TO_CHAR(Add_MONTHS(sysdate,-1),'MMYYYY'),'DDMMYYYY') AND Add_MONTHS(sysdate,1));
COMMIT; Any help on this would be greatly appreciated. If you need any more information please let me know and I will provide it....

S Received on Fri Sep 15 2000 - 15:20:11 CDT