Yes, it should update index with every dml happening on the table if the indexes and constraints are enabled. However, delete does not give you the space back. It only marks the relevant blocks empty after rows are deleted from them. This happens because HWM for the object keeps on the same mark. But if you export and import the object or if you rebuild the object [you can rebuild both indexes and tables online] then only the HWM moves down and you see the size of your object becomes smaller. Again, this would also require you not to let an unnecessarily large sized next extent issued for this object because if the space issued to objects via extents is not reduced according to what is needed, the size of the object is also not reduced.
Gurmohan
www.freemath.info
------------------------------------------------------------------------------------------
-----Original Message-----
From: NUTAN [mailto:[Email address protected]
Sent: Wednesday, June 29, 2005 2:47 PM
To: Shergill, Gurmohan
Subject: RE: Deleting Large records and impact on index
Do you mean when I delete records, if index is not disabled, it is updated
automatically?
But then Space occupied by Index is not released.
Nutan
-----Original Message-----
From: Shergill Gurmohan [mailto:[Email address protected]
Sent: Wednesday, June 29, 2005 2:32 PM
To: [Email address protected]
Subject: RE: Deleting Large records and impact on index
**A LazyDBA.com subscriber has responded to your lazydba.com post**
**LazyDBA.com mail shield has forwarded you this email,
**and removed any attachments, and kept your email address secret
**from this person, and any viruses/trojans.
**If you reply to this email, the person will see your email address as
normal
**Anything below this line is the original email text
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
[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