Re: Update and Delete takes too long, Help...

From: Michael Bialik <michael_bialik_at_my-deja.com>
Date: Sat, 16 Sep 2000 17:08:50 GMT
Message-ID: <8q09em$3jo$1@nnrp1.deja.com>

Hi.

1. Try creating a new index on ( DATE_R ) column for "temp_Comdhis" and "Comdhis" tables. It may help if you are deleting relative small portion of these tables. If you are using Oracle 8.0 or 8.i - consider partitions on DATE_R field ( one partition for each month ) - it will enable you to use TRUNCATE PARTITION instead of DELETE.
2. It is not clear what do you have to do:
3. Do you want to delete from DivisionEOD.Comdhis table all records with DATE_R < TO_DATE('01'||TO_CHAR(Add_MONTHS(sysdate,-1),'MMYYYY'), 'DDMMYYYY')? In that case PARTITIONS will help ( for Oracle 8.x ).
4. Do you have to check that records with same data appear in temp_Comdhis table ( Or you are working with Oracle 7.x )? In that case index on DATE_R field may help. Try following ( if you replace small portion of the table ) after creating an index on DATE_R column:

    DELETE FROM DivisionEOD.Comdhis
    WHERE

     DATE_R BETWEEN
      TO_DATE('01'||TO_CHAR(Add_MONTHS(sysdate,-1),'MMYYYY'),'DDMMYYYY')
      AND Add_MONTHS(sysdate,1)
      AND EXISTS ( SELECT 1 FROM temp_Comdhis
                   WHERE 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 );

 3. For statement:

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

   If you have a lot of records where OfficeNum IS NULL then try    creating an index on DATE_R, OFFICENUM columns instead of DATE_R    column only and using OfficeNum > 0 instead of OfficeNum IS NOT NULL.

 HTH. Michael.

In article <%vvw5.41061$7W2.1469567_at_news1.wwck1.ri.home.com>,   "Sally Madeira" <sallym1_at_home.com> wrote:
> 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
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat Sep 16 2000 - 12:08:50 CDT