OraFAQ Forum: SQL & PL/SQL » Delete rows from a table having 100 million rows.

Home » SQL & PL/SQL » SQL & PL/SQL » Delete rows from a table having 100 million rows.

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Delete rows from a table having 100 million rows. [message #40536]

Thu, 17 October 2002 13:06

Amit
Messages: 166
Registered: February 1999

Senior Member

Want to delete rows from a table having 100 million records. The data for the rows to be deleted comes from a flat file.
SQL*loader does not work for delete.
Can you recommend a fast process to do the same ?
I think running individual queries will take too long.

Any help would be appreciated

Report message to a moderator

Re: Delete rows from a table having 100 million rows. [message #40537 is a reply to message #40536]

Thu, 17 October 2002 14:01

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

How many of the rows will you be deleting?

Report message to a moderator

Re: Delete rows from a table having 100 million rows. [message #40544 is a reply to message #40536]

Fri, 18 October 2002 04:41

Amit
Messages: 166
Registered: February 1999

Senior Member

it will not be constant, as it will be based on a generated flat file. It should below 1 million all the time. Generally in the 20-50k range.

Thanks

Report message to a moderator

Re: Delete rows from a table having 100 million rows. [message #40546 is a reply to message #40536]

Fri, 18 October 2002 04:52

Rick Cale
Messages: 111
Registered: February 2002

Senior Member

Can you provide more info?

Is the data in the flat file just a record of records
to be deleted or they going to replace those deleted records.

How many records are going to be deleted?

What is your Oracle version?

Report message to a moderator

Re: Delete rows from a table having 100 million rows. [message #40549 is a reply to message #40544]

Fri, 18 October 2002 08:09

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

Here's one option:

Write a process using UTL_FILE that loads the key values - since that is all you need to do the delete - into a global temporary table.

Then, do the mass delete in one statement (make sure your rollback segments are sized correctly):

delete
  from t
 where key in (select key from gtt);

If you are on 9i, you could reference the flat file as an external table and skip the load step entirely.

Report message to a moderator

Re: Delete rows from a table having 100 million rows. [message #40551 is a reply to message #40544]

Fri, 18 October 2002 08:20

Amit
Messages: 166
Registered: February 1999

Senior Member

But do you think delete from a where (a.key) in (select b.key from b) is the best option ?

how about something like using a PLSQL/cursors ?

Thanks

Report message to a moderator

Re: Delete rows from a table having 100 million rows. [message #40553 is a reply to message #40536]

Fri, 18 October 2002 08:37

Amit
Messages: 166
Registered: February 1999

Senior Member

the data file will have the actual records to be deleted.
the expected deletes is in the 20k-50k range
We are currently on 8.0.4, but will be moving to some version on 9i.

Report message to a moderator

Re: Delete rows from a table having 100 million rows. [message #40555 is a reply to message #40544]

Fri, 18 October 2002 09:10

Rick Cale
Messages: 111
Registered: February 2002

Senior Member

This is the best option. I would use exists instead of IN clause. I would run against explain plan anyway. But try

DELETE FROM t A
WHERE EXISTS (SELECT 1 FROM gtt B
WHERE b.key1 = a.key1
AND b.key2 = a.key2
AND b.keyN = a.keyN);

If you use global temp table remember to index it on the key field

Report message to a moderator

Re: Delete rows from a table having 100 million rows. [message #40557 is a reply to message #40544]

Sat, 19 October 2002 06:28

Rick Cale
Messages: 111
Registered: February 2002

Senior Member

Todd,

I stand corrected, thanks for your usual thorough explanation.

Report message to a moderator

Previous Topic:	References to COMPOSITE PRIMARY KEY
Next Topic:	FEW QUERY

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 03 06:07:13 CDT 2024