ORACLE TABLE REORGANIZATION

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>);

 PART2-REBUILDING THE TABLE BY SHRINKING ITS FREE SPACE

I hope this article helped you. Your suggestions/feedback are most welcome.
Keep learning... Have a great day!!!