RE: Deleting Large records and impact on index

RE: Deleting Large records and impact on index

 

  

... And you will also need to reset your HWM. Best way is to do the
ALTER TABLE ... MOVE TABLESPACE ... (can be same tablespace) cmd.

Then you will have to rebuild all your indexes as they will be marked
INVALID.

HTH



-----Original Message-----
From: Kerber Andrew
[mailto:oracledba-ezmlmshield-x66542966.[Email address protected]
Sent: Wednesday, June 29, 2005 9:27 AM
To: LazyDBA Discussion
Subject: RE: Deleting Large records and impact on index

If you do a delete, you will want to rebuild all indexes (including the
primary key index). The rebuild process will release the unused space.

It might actually be faster to do a create table as select the records
you want to keep. Then recreate privileges, indexes, etc.

-----Original Message-----
From: NUTAN
[mailto:oracledba-ezmlmshield-x72007831.[Email address protected]
Sent: Wednesday, June 29, 2005 11: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



--------
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