RE: Deleting Large records and impact on index

There are numerous possible variations of these steps, but in essence
you want to:

1) Use CTAS statements to build temp tables with the rows you want to
keep.
2) Drop and rebuild the tables and indexes.
3) Insert the data back into the tables from the temp tables you built.

One variation is to truncate with a reuse option instead of the drop in
step 2, but I prefer the confidence that results in a drop and rebuild.
As long as you have, or can create the full DDL for the objects, then
it's just as easy and fast to drop and rebuild.

Another variation is to avoid step 3 by renaming the temp tables to the
production names after step 2, but again, I prefer the direct approach
when I am dealing with the small amount of data you are currently
working with. If I had millions and millions of rows to insert I would
go with the rename instead.

Final word: If you are using the cost-based optimizer, don't forget to
re-analyze those tables and indexes!

-Richard

-----Original Message-----
From: NUTAN
[mailto:oracledba-ezmlmshield-x72007831.[Email address protected]
Sent: Wednesday, June 29, 2005 10:18 AM
To: LazyDBA Discussion
Subject: Deleting Large records and impact on index

Hi,

We are using Oracle 8. One of our table say "A" is having 128998
records. I
want to delete 88253 records from this table. This table "A" is having
Primary Key.

This Primary Key is used as Foreign Key in Table "B" having records
370071
of which, I want to delete 236487 records.

1. How to speed up the delete operation?
2. After delete how to free the unused index space?
3. Will the Primary Key and Foreign Key Indexes changed
automatically
or I need to run Alter Index Rebuild statement.

Can someone clarify these questions?

Thanks

Nutan

--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html

Oracle LazyDBA home page