Table Shrink Space and Performance [message #616387] |
Mon, 16 June 2014 08:37 |
preet_kumar
Messages: 204 Registered: March 2007
|
Senior Member |
|
|
I would like to run the below given commands to reclaim space and HWM for one of the table.
alter table business enable row movement;
alter table business shrink space cascade;
alter table business disable row movement;
Please let me know does this operation locks the table and has any performance issue?
Does the Index space also get shrink and is the Index remain usable after the shrink is complete ?
|
|
|
|
|
Re: Table Shrink Space and Performance [message #616394 is a reply to message #616389] |
Mon, 16 June 2014 08:56 |
preet_kumar
Messages: 204 Registered: March 2007
|
Senior Member |
|
|
I am not talking here about disk space but to reset the HWM and table fragmentation.
Fragmentation comes with when we update/delete data in table.
The space which gets freed up during non-insert DML operations is not immediately re-used (or sometimes, may not get reuse ever at all). This leaves behind holes in table which results in table fragmentation
High water mark of table actually defines the border line between (ever) used and unused (never) space. While performing full table scan, Oracle will always read the data up to HWM. And if there is lot of free space with-in HWM, that is read too, and hence degrading the performance of FTS.
This is the reason i want to run the shrink space operation.
|
|
|
|
|
Re: Table Shrink Space and Performance [message #616397 is a reply to message #616396] |
Mon, 16 June 2014 09:16 |
preet_kumar
Messages: 204 Registered: March 2007
|
Senior Member |
|
|
@Blackswan What do you mean by "fragmentation only exists between your ear"
@pvsarat Yes i have deleted a huge amount of data from the table.
Below given the result
TABLE_NAME ACTUAL_MB OPTIMAL_MB CLAIMABLE_MB
--------- ---------------------- ------- -------------
Business_Plan 179811 142927 36884
Please if possible answer the below given questions with valid reason instead of vague responses.
* If operation locks the table during the entire shrink operation?
* Does the Index space also get shrink and is the Index remain usable after the shrink is complete ?
[Updated on: Mon, 16 June 2014 09:18] Report message to a moderator
|
|
|
|
|
|
|
|