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.
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 which are tablespace
with auto segment space management (ASSM).
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)
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.
Seventeen years of experience in the IT Industry in Software, MIS,development, design and database administration. Worked for more than 12 years as Oracle Database Administrator. Working with FunDtech India Ltd as Asst. Vice President - ORACLE TECHNOLOGY and DBA for the last six years. Strengths Include: Database Administration on 11g,10g,9i,8i, 8.0 and 7.x in Unix, Windows NT and Novel Netware Environment. Conducted design reviews with an aim to enhance performance in production scenario. Has also carried out Design and Development in Oracle and Developer 2000 and Forms Reports 10g.
