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

From: <oratune_at_aol.com>
Date: Fri, 15 Sep 2000 21:54:13 GMT
Message-ID: <8pu5ps$rpr$1@nnrp1.deja.com>

• Comments embedded. 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;
  >
  Presuming that your index is built in the order you specified, i.e.,
• LoanNum, OfficeNum, CliNum and Date_R then it is not surprising this delete is taking quite a bit of time. Since the Date_R column is not the leading column in the index the index is not being used by this statement. You would most likely wish to re-write the statement as follows:
• SET TRANSACTION USE ROLLBACK SEGMENT RB1;
• DELETE /*+ index <indexname> */ FROM DivisionEOD.temp_Comdhis
• WHERE DATE_R <
• TO_DATE('01'||TO_CHAR(Add_MONTHS(sysdate,-1),'MMYYYY'),'DDMMYYYY');
• COMMIT;

This should allow the index to be used for the delete.

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

Again, the Achiles heel of this is also the Date_R column, for the same reason. Use the same hint syntax in the delete to utilize the index for this transaction and the one following.

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

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Fri Sep 15 2000 - 16:54:13 CDT