When using tables with large
amounts of rows and especially after a lot of rows have been deleted from such
a table, reorganizing the table may improve the performance of the table.
Oracle knows two types of table reorganizations.
·
Rebuilding
the table by means of recreating it.
1. Export/Import
2.Alter table Move
3.CTAS method (Create table table_name2 as Select *from table_name1)
·
Rebuilding
the table by shrinking its free space ( Oracle 10g and up )
Below
the two methods are explained.
PART 1: REBUILDING
THE TABLE BY MEANS OF RECREATING IT.
There are two ways of
rebuilding a table by recreating it. The first option is to export all the data
into a file using the export utility.
After that truncate (of recreate) the table and reload the data back into it.
The disadvantage here however is a long downtime.
Another method is moving the table either to another tablespace or within the
same tablespace.
The advantage here is that the downtime will me much less, but it also has some
disadvantages:
·
The
tablespace needs to be able to store this second copy of the table
·
The
tables indexes, any depending objects like packages, procedures can become
INVALID and might need to be rebuild
·
There
will be a table-level exclusive lock on the table involved. Any application
using that table will be affected by this lock.
STEPS:
1. Collect
all the details
By moving the table to another tablespace temporarily, the DBA is also able to
reorganize the tablespace.
Take
the sizes of tables , associate indexes and take the invalid object information
, Check for table,index status
Example:
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME,
BYTES/1024/1024/1024 FROM DBA_SEGMENTS WHERE SEGMENT_NAME IN
(<TABLENAMES>);
SELECT OWNER, INDEX_NAME, INDEX_TYPE, TABLE_NAME, STATUS FROM
DBA_INDEXES WHERE TABLE_NAME IN (<TABLENAMES>);
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME,
BYTES/1024/1024/1024 FROM DBA_SEGMENTS WHERE SEGMENT_NAME IN
(<INDEXNAMES>);
SELECT LAST_ANALYZED FROM DBA_TABLES WHERE TABLE_NAME IN
(<>);
SELECT COUNT(*) FROM DBA_OBJECTS WHERE STATUS=’INVALID';
2. Export
table statistic
Exporting
the tables stats is very important; else, tables will start gathering the stats
from beginning for the whole table and sometimes that will affects the
performance of the queries respect to these tables. Once the reorg has been completed
we need to import the stats back to the table, so that the tables will use the
old stats while querying the table data
(a)
Create Stats Table to store the statistics
Exporting
the stats to one of the table say ‘MY_STATS_TABLE’ as temporary table. Once the
stats has been imported you can drop the table
EXEC DBMS_STATS.CREATE_STAT_TABLE(OWNNAME
=>'<OWNERNAME>’,STATTAB =>'<TABLENAME>’, TBLSPACE
=>'<TABLESPACENAME>’);
Example:
EXEC DBMS_STATS.CREATE_STAT_TABLE(OWNNAME =>’SCOTT’,STATTAB
=>’MY_STATS_TABLE’, TBLSPACE =>’USER’);
(b)
Export the stats to the above created table
EXEC
DBMS_STATS.EXPORT_TABLE_STATS(‘<OWNERNAME>’,'<TABLENAME>’,NULL,'<STATS
TABLENAME>’,NULL,TRUE);
Example:
EXEC
DBMS_STATS.EXPORT_TABLE_STATS(‘SCOTT’,’EMP’,NULL,’MY_STATS_TABLE’,NULL,TRUE);
3. Perform
Table & associated index reorg
(a)
Table Reorg:
For
Non-LOB Tables:
ALTER TABLE <USERNAME>.<TABLE NAME> MOVE;
Example:
ALTER TABLE SCOTT.EMP MOVE;
For
Non-LOB Tables:
ALTER TABLE <USERNAME>.<TABLE_NAME> MOVE LOB
(COLUMN_NAME) STORE AS (TABLESPACE NEWTABLESPACE);
Example:
ALTER TABLE SCOTT.EMP MOVE LOB (PHOTO) STORE AS TABLESPACE DATA;
(b)
Rebuild the associated indexes:
If the
table has associated indexes which we got it from the step-1 queries, we need to
perform the rebuild as they were become INVALID once after the reorg of the
parent table.
For
Normal Indexes:
ALTER INDEX <USERNAME>.<INDEX NAME> REBUILD ONLINE;
Example:
ALTER INDEX SCOTT.EMP_IDX REBUILD ONLINE;
For
Bitmap indexes:
ALTER INDEX <USERNAME>.<INDEX NAME> REBUILD;
Example:
ALTER INDEX SCOTT.EMP_BIT_IDX REBUILD;
4. Import
table stats
EXEC
DBMS_STATS.IMPORT_TABLE_STATS(‘<USERNAME>’,'<TABLENAME>’,NULL,'<STATS
TABLENAME>’,NULL,TRUE);
Example:
EXEC
DBMS_STATS.IMPORT_TABLE_STATS(‘SCOTT’,’EMP’,NULL,’MY_STATS_TABLE’,NULL,TRUE);
5. Repeat
step 1
6. Please
collect the invalid object count
SELECT COUNT(*) FROM DBA_OBJECTS WHERE STATUS=’INVALID';
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS FROM DBA_OBJECTS
WHERE STATUS=’INVALID';
7. Run
utlrp.sql (If necessary)
SQL> @?/rdbms/admin/utlrp.sql
8. Again
Collect the Invalid Object information
9. Execute the below query and compare the values before and after the
the reorg, you will be surprised
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME,
BYTES/1024/1024/1024 FROM DBA_SEGMENTS WHERE SEGMENT_NAME IN
(<TABLENAMES>);
I
hope this article helped you. Your suggestions/feedback are most welcome.
Keep
learning... Have a great day!!!
