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.
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)
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.
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:
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:
(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!!!
Keep learning... Have a great day!!!
