Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Oprimizing Oracle Delete Query

Posted on 2002-06-07
11
Medium Priority
?
10,317 Views
Last Modified: 2011-08-18
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.
0
Comment
Question by:sarankrishna
11 Comments
 
LVL 6

Expert Comment

by:M-Ali
ID: 7063895
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
0
 
LVL 7

Expert Comment

by:waynezhu
ID: 7064371
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 ...
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 7064636
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.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 3

Expert Comment

by:UsamaMunir
ID: 7065179
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.
0
 
LVL 3

Expert Comment

by:mnicoras
ID: 7065363
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
0
 
LVL 3

Accepted Solution

by:
sidcap earned 600 total points
ID: 7068434
Hi sarankrishna:

I've wrote a generic procedure we're using within our daily basis batch processes:

create or replace procedure MyDelete(pTableName IN varchar2 default null,pWhere IN varchar2,pCommit IN number default 5000)
is
ret integer;
tot number(6) default 0;
vSql varchar2(2500);

  begin


       vSql := 'DELETE ' || pTableName || ' WHERE ROWNUM <= ' || pCommit;

       if pWhere is not null then

       vSql := vSql || ' AND ' || pWhere;
        else
       vSql := 'TRUNCATE TABLE ' || pTableName;

       end if;

       loop
       ret := ExecSql(vSql);
       commit;

       exit when ret <= 0;
       tot := tot + ret;
       end loop;

  end;

and the auxiliary function:

create or replace function ExecSql(pSql IN long) return integer
is

a integer;
fdbk integer;

Begin

     execute immediate pSql;
     
     fdbk := sql%rowcount;
     

return fdbk;


Parameters:

pTableName IN varchar2:

Table name that you want to delete.

pWhere IN varchar2:

Where condition, if there is no one, a truncate table will be automatically executed.


pCommit IN number:

Number of records within each commit.

The idea of this simple procedure is to avoid rollback problems.

ej:

myDelete('TABLEA','C1 <= 'VV');

This's an implementation on what markgeer has secondly suggested.

Hope this helps, SIDCAP.
0
 

Author Comment

by:sarankrishna
ID: 7068934
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.
0
 
LVL 3

Expert Comment

by:sidcap
ID: 7093801
sarankrishna:

How did you solve your problem ?

sidcap.
0
 
LVL 4

Expert Comment

by:marper
ID: 7196928
use "truncate"
0
 
LVL 5

Expert Comment

by:jpkemp
ID: 9034205
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
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many‚Ķ
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question