Hi everyone!!
I have been lately informing myself about this issue. Being a total noob in database administration, this has taken A LOT of reading and effort, but i fail to find complete information about this issue, or i even find contradictory information. This is getting quite confusing, honestly. I am kindly asking for your knowledge about this issue to be able to have a good picture about the different methods. Lets go!
Talking about Oracle 10.
As far as i know, there are 3 major ways of dealing with fragmentation and empty spaces:
1- Shrink command (alter table <table_name> shrink space; -> both steps):
* My understanding:
- Reorganizes the rows of the table moving trying to fill empty spaces and condense row scattering
- Resets HWM
- De-allocates space
- Indexes remain viable
- Very slow but except for a tiny part, the table is not blocked so the database can still be used meanwhile (whit a decreased performance)
*My questions:
- At what level does this commend work: block, extent, segment or all of them? I mean, it reorganizes data in blocks, blocks in extents or the extents in the segments of the table, all of it ? My guess is block since it tries to move rows to the blocks that have some free space thus filling some blocks and emptying others that will be deallocated.
- When the operation deallocates space, that "freed" space is given back to the OS or is it given back to the tablespace where the table is allocated so it can be used for new tables and other objects?
-Despite maintaining viable indexes, if shrinking a table as caused a lot of row movement, is it wise to reconstruct the index so the references are updated and "reordered" or despite a huge row movement, the index will still work fine?
- In relation to the previous question, "alter table shrink space cascade" also shriks the associated indexes. My question is, does this only reorganize the entries to free up space leading to a similar problem as exposed above?
2- Alter table move + rebuild indexes
* My understanding:
- Reorganizes the rows of the table moving trying to fill empty spaces and condense row scattering similarly to shrink
- Resets HWM
- Dos NOT De-allocate space. Instead it keeps that space below the watermark so it can be used for further inserts. I have a doubt tho. You can either move the table to the same tablespace or to a different tablespace and then move it back to the original (tablespace name can't change due to table being used by external software and can lead to problems). Is there a difference in terms of freed space (table space and table's tablespace space) between both options?
- It is more adequate than shrink when we have deleted a lot of rows from a table but it is expected that it will grow to a similar size in a short time do to intense inserts due to the fact stated in the previous line (Oracle wont have to ask for further space for the table as it is already "reserved" beforehand)
- More quick than shrink, but entire table is locked during the process.
* My questions:
- At what level does this command work: block, extent, segment or all of them? I mean, it reorganizes data in blocks, blocks in extents or the extents in the segments of the table, all of it?
3- Import / Export
* My understanding:
- As you are creating the table again, my understanding is that this method does the best job at organizing the table completely and getting rid of free space since you will end up with a fresh new table with all its rows perfectly distributed in the data blocks (assuming that you have used a decent configuration with pctfree and other parameters, i am assuming that in the 3 method we are working with the exact same database).
- Risky as it can lead to data loss
* My questions
- What other downsides are there to this method? The risk of data loss is quite frightening, but besides that i can't find other downsides to be honest (again, i might just be too ignorant in the subject, which i probably am).
- As shrink usually suggested to use instead of this method, in what special situations do you think this one would be better than shrink?
- When you drop the table, the freed space is : given back to the OS? given back to the tablespace where the table was located?
And my final question:
* In what situations do you advice to use methods 2 or 3 instead of shrink and why? As far as i have seen, despite it taking a long time, you can still use the database while it is still running, you have pretty much 0 risk of losing data and judging by examples I have found so far it seems to do a pretty good job tho not perfect (taking into consideration its limitations: No shrink option for:
- Tables that are part of a cluster
- Tables with LONG columns,
- Certain types of materialized views
- Certain types of IOTs.
- Tables with function-based indexes.
)
Thank you a lot in advance to all of you for your time and help, and i want to apologize in advance if any question comes up as silly or ill informed. I am very new to oracle management and the more i read, the more confused i am. (And sorry in advance for how long this post as grown..)
Elena~
EDIT 1: Changed title to a more specific one since i want to approach table fragmentation specifically