Solved

Oprimizing Oracle Delete Query

Posted on 2002-06-07
11
10,303 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 34

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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 3

Expert Comment

by:oraelbis
ID: 7067113
0
 
LVL 3

Accepted Solution

by:
sidcap earned 200 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
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.

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now