How to improve the deletion rate.

[Tuhin Kumar]

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

[Ed prochak]

tk****@ipolicyn et.com (Tuhin Kumar) wrote in message news:<e4******* *************** ****@posting.go ogle.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

[Eric Manseau]

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****@ipolicy net.com> wrote in message
news:e4******** *************** ***@posting.goo gle.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, 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

[Jim Kennedy]

"Ed prochak" <ed********@mag icinterface.com > wrote in message
news:4b******** *************** ***@posting.goo gle.com...

tk****@ipolicyn et.com (Tuhin Kumar) wrote in message

news:<e4******* *************** ****@posting.go ogle.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

[Frank van Bortel]

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

[Unmesh Deshpande]

>

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