Rebuilding indexes and reorganizing tables in the same tablespace doesn't always give you the desired benefit of recovering space in the tablespace, not at least as much as you thought you would recover. Good recovery of tablespace happens when you move all contents of the tablespace to another tablespace, coalesce the first tablespace and then move contents back from other tablespace to first one.
Deleting one or two hundred thousands of rows especially when the primary key is there and is being used in the where clause should not be very time consuming process. It is definitely not the worth of going to create another tables and rename it later on. Disabling indexes can help delete to happen fast.
If you do not disable the key of index then the indexes [including the index behind the primary key] are all updated simultaneously.
Gurmohan
www.freemath.info
--------------------------------------------------------------------------------
-----Original Message-----
From: NUTAN
[mailto:oracledba-ezmlmshield-x72007831.[Email address protected]
Sent: Wednesday, June 29, 2005 12:18 PM
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