Introduction
Re-organizing activity is time consuming process in a large databases. Even it gives lot of improvement in the Production Databases, still most of the people will skip the same, since it is lengthy and hectic process. Some of the application if it works with “Rule” based optimization, it can give lots of performance impact.
MOVE is easy. That moves an object from one tablespace (and set of data files) to another tablespace (and another set of data files). That may or may not decrease the size of the table. It would be used when you need to temporarily or permanently relocate an object, which is what would need to happen if you need to shrink a data file.
COALESCE is only related to index-organized tables or hash partitioned tables. It does the same thing for an index-organized table that coalescing an index does, it merges the contents of an index block.
SHRINK SPACE tells Oracle to physically move rows around in a table (this is why row movement must be enabled for the table) in order to release free space in the table back to the tablespace.
Before get into the re-organizing of 10g new features, I would like to light on some information of Fragmentations.
There are 2 types of Fragmentation.
HONEY COMB FRAGMENTATION
Honeycomb Fragmentation is a type of fragmentation in which the free extents lie adjacent to each other.
Honecomb fragmentation can be removed by a process called coalescing. ‘Alter tablespace tablespacename coalesce;’s
BUBBLE FRAGMENTATION
Bubble fragmentation, a type of fragmentation in which the free extents are separated from each other by used space. It is bit difficult to remove, but it can be done, re-create or re-organizing or extent management.
Before 10g
1) Schema Export, Schema drop, Create New Schema, Create New Tablepsaces and import.
2) Alter table move (to another tablespace, or same tablespace).
alter table table_name move; This would do the table re-organising.
New approach of Oracle 10g
10g give us a new way of reorganizing the data.
Shrink command: This command is only applicable for tables whose tablespace SEGMENT_SPACE_MANAGEMENT set to Auto (ASSM).
ALTER TABLE SHRINK is a two phase operation:
1st. step: reads table from the back and deletes first row found , then read table from the front and uses first free slot to reinsert it.
This is reiterated until the position for reading from back and reading from fron tis the same. Then the segtment is compact, right. This is DML and indexes are maintained.
Before using this command, you should have row movement enabled.
alter table table_name enable row movement;
There are 2 ways of using this command.
Choice 1 Break in two parts: In first part rearrange rows and in second part reset the HWM.
Part 1: Rearrange (All DML's can happen during this time)
sql>alter table table_name shrink space compact;
Part 2: Reset HWM (No DML can happen. but this is fairly quick, in fact goes unnoticed.)
sql>alter table table_name shrink space;
Choice 2. All in one go:
sql>alter table table_name shrink space; (Both rearrange and restting HWM happens in one statement)
The above mentioned methods are online re-organising and would not cause any impact.
Oracle 10g describes the reorganization operations that are possible using the ONLINE clause in the SQL CREATE/ALTER INDEX and TABLE statements.
Sql>ALTER TABLE dept MOVE ONLINE;
(Parallel operations not supported )
sql>CREATE INDEX dept.pidx_dept ON dept(dept_no) ONLINE;
(Parallel operations supported)
(Supported index types: IOT secondary, reverse key, functional, bitmap, key compressed)
sql>ALTER INDEX dept.pidx_dept REBUILD ONLINE;
(Parallel operations supported)
sql>ALTER INDEX dept.pidx_dept COALESCE;
(Parallel operations supported)
Unused Space Reclaiming
Using life time of the database, many updates and deletes to the data can occur, and this may result in space inside the database not being used efficiently. In Oracle Database 10g, this space can be reclaimed for tables, index organized tables, indexes, partitions and materialized views, provided the objects are stored in tablespaces using automatic segment space management. Space is reclaimed online, and in-place, thus eliminating expensive database downtime and additional storage requirements. The space is retrieved by adding the clauses SHRINK SPACE to the ALTER TABLE, ALTER INDEX, ALTER MATERIALIZED VIEW and ALTER MATERIALIZED VIEW LOG commands.
The space is being reclaimed, indexes are maintained and are completely usable once the process has finished. An optional CASCADE clause provides the ability to reclaim space for all dependent objects. Therefore reclaiming space on the table, would also reclaim space on all the indexes on that table.