By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,952 Members | 916 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,952 IT Pros & Developers. It's quick & easy.

How to improve the deletion rate.

P: n/a
Hi,
I have a requirement on improving the deletion rate on on records
of a table.
The table contains 5 million records, but since deleting everything
matching the condition at one go was giving the ROLLBACK segment
allocation error, the query was called in a loop and deleting 2000
rows at one interation.
My query is:
DELETE FROM Table1 WHERE TimeStamp >= TO_DATE('2003-11-30 18:30:00',
'YYYY-MM-DD HH24:MI:SS') and TimeStamp <= TO_DATE('2003-12-18
18:29:59', 'YYYY-MM-DD HH24:MI:SS') AND ROWNUM < 2000

The performance I am getting is deletion of 1.5 lakhs records
happening in 10 minutes. I am looking forward to 10 lakhs records in
10 minutes.

Thanks,
Tuhin
Jul 19 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
tk****@ipolicynet.com (Tuhin Kumar) wrote in message news:<e4**************************@posting.google. com>...
Hi,
I have a requirement on improving the deletion rate on on records
of a table.
The table contains 5 million records, but since deleting everything
matching the condition at one go was giving the ROLLBACK segment
allocation error,
Then ask your DBA to increase rollback segment space. That's the right
answer.
the query was called in a loop and deleting 2000
rows at one interation.
My query is:
DELETE FROM Table1 WHERE TimeStamp >= TO_DATE('2003-11-30 18:30:00',
'YYYY-MM-DD HH24:MI:SS') and TimeStamp <= TO_DATE('2003-12-18
18:29:59', 'YYYY-MM-DD HH24:MI:SS') AND ROWNUM < 2000
Let me guess, NO INDEX on the Timestamp column. (so this does a full
table scan)

the DELETE itself is inside another loop that doesn't do a COMMIT
WORK; before executing the DELETE again.

(And I still wonder whether there are any foreign keys to this table,
resulting in cascading deletes.)

The performance I am getting is deletion of 1.5 lakhs records
happening in 10 minutes. I am looking forward to 10 lakhs records in
10 minutes.

Thanks,
Tuhin


I'm STILL curious: what is a "lakhs"?

ED
Jul 19 '05 #2

P: n/a
Hi Tuhin,

You should try to refine the where clause. You may use a BETWEEN clause
instead of two comparaison.

DELETE FROM Table1 WHERE TimeStamp BETWEEN TO_DATE('2003-11-30 18:30:00',
'YYYY-MM-DD HH24:MI:SS') and TO_DATE('2003-12-18 18:29:59', 'YYYY-MM-DD
H24:MI:SS') AND ROWNUM < 2000

Also it can be a good idea to create an index the field TimeStamp if it is
not already.

"Tuhin Kumar" <tk****@ipolicynet.com> wrote in message
news:e4**************************@posting.google.c om...
Hi,
I have a requirement on improving the deletion rate on on records
of a table.
The table contains 5 million records, but since deleting everything
matching the condition at one go was giving the ROLLBACK segment
allocation error, the query was called in a loop and deleting 2000
rows at one interation.
My query is:
DELETE FROM Table1 WHERE TimeStamp >= TO_DATE('2003-11-30 18:30:00',
'YYYY-MM-DD HH24:MI:SS') and TimeStamp <= TO_DATE('2003-12-18
18:29:59', 'YYYY-MM-DD HH24:MI:SS') AND ROWNUM < 2000

The performance I am getting is deletion of 1.5 lakhs records
happening in 10 minutes. I am looking forward to 10 lakhs records in
10 minutes.

Thanks,
Tuhin

Jul 19 '05 #3

P: n/a

"Ed prochak" <ed********@magicinterface.com> wrote in message
news:4b**************************@posting.google.c om...
tk****@ipolicynet.com (Tuhin Kumar) wrote in message

news:<e4**************************@posting.google. com>...
Hi,
I have a requirement on improving the deletion rate on on records
of a table.
The table contains 5 million records, but since deleting everything
matching the condition at one go was giving the ROLLBACK segment
allocation error,


Then ask your DBA to increase rollback segment space. That's the right
answer.
the query was called in a loop and deleting 2000
rows at one interation.
My query is:
DELETE FROM Table1 WHERE TimeStamp >= TO_DATE('2003-11-30 18:30:00',
'YYYY-MM-DD HH24:MI:SS') and TimeStamp <= TO_DATE('2003-12-18
18:29:59', 'YYYY-MM-DD HH24:MI:SS') AND ROWNUM < 2000


Let me guess, NO INDEX on the Timestamp column. (so this does a full
table scan)

the DELETE itself is inside another loop that doesn't do a COMMIT
WORK; before executing the DELETE again.

(And I still wonder whether there are any foreign keys to this table,
resulting in cascading deletes.)

The performance I am getting is deletion of 1.5 lakhs records
happening in 10 minutes. I am looking forward to 10 lakhs records in
10 minutes.

Thanks,
Tuhin


I'm STILL curious: what is a "lakhs"?

ED


It is a local tem to India meaning a particular magnitude (like K for
thousands). I do not remember what particular order of magnitude it is.
Jim
Jul 19 '05 #4

P: n/a
Tuhin Kumar wrote:
Hi,
I have a requirement on improving the deletion rate on on records
of a table.
The table contains 5 million records, but since deleting everything
matching the condition at one go was giving the ROLLBACK segment
allocation error, the query was called in a loop and deleting 2000
rows at one interation.
My query is:
DELETE FROM Table1 WHERE TimeStamp >= TO_DATE('2003-11-30 18:30:00',
'YYYY-MM-DD HH24:MI:SS') and TimeStamp <= TO_DATE('2003-12-18
18:29:59', 'YYYY-MM-DD HH24:MI:SS') AND ROWNUM < 2000

The performance I am getting is deletion of 1.5 lakhs records
happening in 10 minutes. I am looking forward to 10 lakhs records in
10 minutes.

Thanks,
Tuhin


Have your dba create partitions, based on date.
Drop partion takes a fraction of what you are trying to accomplish.
Typical example of an application not designed for a lifetime.

If you don't get this, decrease your time stamp window, and run
until completion (e.g. delete 1 day at a time)
--
Merry Christmas and a Happy New Year,
Frank van Bortel

Jul 19 '05 #5

P: n/a
>
I'm STILL curious: what is a "lakhs"?

ED


Hi ED,

1 million = 10 lakh
"lakhs" is the local term used in India.

Regards,
Unmesh Deshpande
Jul 19 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.