Optimize a Delete

[bflochick]

We have table A which contains a great deal of information about our "entities" (id, name, address, etc.) in a specific format for the other system we send the information to and various reports, procedures etc. We can't change how this table is/the overall procedure is done because of the dependencies.

Currently the script create a temp table of the IDs (the ones who have changes in the system, determined in the overall script) and then what we do is delete the rows in table A where the id is in the temp table. We then repopulate the data in table A (using an insert) for those IDs in the temp table.

I am finding the delete takes a long time. All other parts of the procedure run fairly quick, or as expected. Is there a good way I could optimize this delete? I realize the delete effects the table as well as the indexes, but I wish there were a better way. I've looked up information about a direct-path insert but I do not totally understand what this does, and if this could even be applied in my case.

Thanks for the help.

 

[Dagon]

What is the format of the delete ? Is it something like:

delete from a
where exists
(select 1 from temp t
where t.key = a.key)

 

[bflochick]

Yes, that is exactly it.

 

[Dagon]

The obvious question then is, do you have an index on the key column in the temp table ?

 

[bflochick]

No, but it is simply a one column table with ids. The ids in the temp table drive the delete and insert.
So, would an index on this one column be the way to go?

 

[SantaMufasa]

BfloChick,

Another alternative depends upon the typical ratio of:

rows to delete:total rows

What is that ratio?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[bflochick]

It varies, but here is a current approximation (from a test run today)
4071:272534

The table being deleted from is indexed, very well at that. But I also suspect that continuously doing these deletes/inserts on the large table (90 columns) may be wreaking havoc as well. But the only hang ups I'm seeing is on that Delete. I actually monitored everything this morning.

 

[SantaMufasa]

BfloChick,

It is likely that the continous DELETEs are causing a terrific amoung of fragmentation within the table.

One very fast method removing fragmentation from both the table (providing you have no LONG or CLOB columns) and the index are the following commands:

ALTER TABLE <owner>.<tablename> MOVE PARALLEL NOLOGGING;
ALTER INDEX <owner>.<indexname> REBUILD PARALLEL;

This completely removes all fragmentation from both the table and the index.

So, I suggest that you try the above, run your next iteration of the DELETEs, then let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[bflochick]

Thank you! I will run this tomorrow am and post the results.

 

[SantaMufasa]

BfloChick,

I failed to mention earlier, but do so now: run the above commands at a time when no one else is accessing the table/index that you are reorganising.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Dagon]

Another option to improve the speed of the delete would be to rewrite it in PL/SQL and drive from the small temp table.

DECLARE
    cursor c_temp is select key from temp;
BEGIN
   for rec_temp in c_temp loop
      delete from a where key = c_temp.key;
   end loop;
END;

This method is advantageous when there is a small amount of data in the temp table (as there is in your case). It avoids the overhead of a full table scan of the main table and means there are only a relatively small number of indexed deletes.

You could further improve it by using bulk fetches and deletes.

 

[bflochick]

Mufasa - I saw a slight improvement in the delete after running those lines of code. About 10 minute improvement on the overall procedure, and the delete takes the bulk of that.

Dagon - I will give your suggestion a try as well and post the results.

Thank you both so much for your help! This process was written by someone else and handed to me and it has caused me too many headaches!!

 

[Beilstwh]

I have a real problem with how you are doing your procedures. let me feedback what you are doing.
1) You determine what ID's are changing in the main table.
2) You make a temporary table to hold the id's
3) you delete those Id's from the main table.
4) You insert the Id's back into the main table with the changes.


WHY don't you do the following.

1) insert any new id's into main table.
2) delete only the permanently removed Id's from the main table.
3) perform an UPDATE on the main table to change the Id's

If you have an index on the ID column, this should go VERY fast.

Bill
Oracle DBA/Developer
New York State, USA

 

[bflochick]

Bill - if I am understanding your point correct...the table that changes is about 90 columns which contain anything and everything about our entities. The IDs do not change, but rather various information about the entities...for instance their home address, email, marriage status, etc.
We do not make changes to the IDs, just information about them. So for any given entity they could have one change such as their email address, or many changes such as home/business/phone numbers...using an update just doesn't seem feasible because there would be so many, and the fields which need to be updated vary.

 

[Beilstwh]

What does it matter if you have a single update that updates all the columns for the particular ID. For example if the row has 40 columns and only two items change, it will cause NO harm to update a column to the same value. So do something like the following.

update main_table a
set (col1,col2,col3,col4,col5,col6) =
(select col1,col2,col3,col4,col5,col6
from update_table b
where a.id = b.id)
where exists
(select null
from update_table c
where a.id = b.id);

Bill
Oracle DBA/Developer
New York State, USA

 

[bflochick]

OK, I see what you are saying.
I will look at that. Thanks.