Link to home
Start Free TrialLog in
Avatar of sarankrishna
sarankrishna

asked on

Oprimizing Oracle Delete Query

I am using Oralce as a back end for my payroll application. One of my payroll table having more than 5 lakhs records. My problem is deleting records from that table. Let say If I want to delete some 70000 records. What is happening is either the delete query taking more than 2 minutes or it saying rollback segement problem.

My question is how to faster the delete query. If i am able to stop using rollback segement(or redo log buffer for roll back facility) my delete query will be faster. I don't want a roll back facility for my delete query.

Can any body help me regarding the above problm.
Avatar of M-Ali
M-Ali

Hi,

Actually there is a "nologging" option (for tables) that creates less redo, but it does not work with normal insert/update/delete operations. These operations HAVE to be logged to allow for multiple user access. So, you CANNOT avoid using rollback, and redo for your delete.

The nologging option can be used to create tables in an unrecoverable manner. In order to use nologging you would:

CREATE TABLE new_table NOLOGGING
AS SELECT * FROM old_table WHERE ...
CREATE INDEX new_index1 .... nologging parallel ...
....
DROP TABLE old_table;
RENAME new_table TO old_table;
GRANT ... ON old_table;

Try the above, it may reduce the time. Another alternative for you to look into would be export/import:
EXP ... TABLES=old_table ROWS=N;
CREATE TABLE new_table NOLOGGING
AS SELECT * FROM old_table WHERE ...
DROP TABLE old_table;
RENAME new_table TO old_table;
IMP ... FULL=y IGNORE=y

This would recreate indexes, trigger, constraints etc.


In order to avoid rollback segment errors, ensure all your rollback segments are equi-sized, and have enough number of extents (lets say 20). Alternatively you can create one large rollback segment, and explicitly assign your delete transaction to this rollback segment.

HTH

Ali
Avatar of waynezhu
Based on the information you provided, the deleted records
is more or less 15% of total records, therefore there is a
chance for Oracle to performance the deletion using index
instead of full table scan. Altough there exist many
tips or tricks to tune deletes, to make things simple,  
first I'd to suggest you to investigate the usage of index
as follows:
1) explain plan the delete statement and to see if Oracle
   perform full table? if yes, goto 2); if no, goto 3)
2) create index(es) based on the WHERE clause of the delete
   statement, and explain plan the detele statement again.
   If yes, Oracle uses the index, then your problem
   most likely be solved; if no, goto 3)
3) for it is the case of full scan, I'll privide more tips
   on this

Regarding the rollback segement problem, you need to create
a large rollback segement, and assign the delete operstion
to use the large rollback segement such as:
1) create rollback segement large_rb ...
2) set transaction using large_rb ...
3) delete from tablename where ...
No, there is no way to avoid generating rollback and redo log data when you do deletes.  Yes, you can create a larger rollback segments that is usually offline.  Then you have to put it online before your delete, force your delete to use it, then take it offline again after your large delete.

If you name the large rollback segment "rb_large", here are the commands you need:
alter rollback segment rb_large online;
set transaction use rollback segment rb_large;
--(do your delete here)
commit;
alter rollback segment rb_large offline;

Another option would be to use a PL\SQL procedure with a loop, that could delete a smaller set of records, commit them, then do the next set, etc.
Yep, The bottom line is 'divide and conquer', either u do deletes with a very specific criteria and make sure right indexes are used. use a large rollback segment when u delete and i wud recomend u partition ur table. The partitioning keeps the table divided in small logical boundries and help fast completion of operations.
Hi,

using 8i feature for bulk collect into I recommend a very fast solution.

declare

MyTableRowType MyTable%rowtype;

begin

select *
into MyTableRowType
from MyTable
where ....; -- it's your turn here to write the where clause for the rows you want to keep them in the table!!!!

execute immediate 'truncate table MyTable';

forall i in MyTableRowType.FIRST .. MyTableRowType.LAST
  insert into MyTable values MyTableRowType(i).col1, MyTableRowType(i).col2 ....; -- complete here with all the columns

commit;

end;


enjoy,
Marius Nicoras
ASKER CERTIFIED SOLUTION
Avatar of sidcap
sidcap

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of sarankrishna

ASKER

Hi sidcap

I aggre with you. The procedure what you had work fine and rollback segment problem will get solved. But I still feel performance won't get increase. I think finallay I have to follow your approach only. Thankx you very much.
sarankrishna:

How did you solve your problem ?

sidcap.
use "truncate"
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
Accept sidcap's comment as answer
(It appears sidcap's code was accepted by the questioner)

Please leave any comments here within the next seven days.
 
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
 
jpkemp
EE Cleanup Volunteer