On Table Reorg and Index Rebuild

By Laurent Schneider

Before you start reading : do not rebuild all your indexes and reorganize all your tables every Sunday morning. One day you may find one of your tables missing or one index invalid.

Ok, let�s take a case where table reorg and index rebuild is good. One of your tables was never cleaned up, and it grew to 100,000,000 rows over the last 5 years. You only need the last 2 weeks.

One of your tasks will be to create a job to clean up your table on a weekly basis to delete rows older than 14 days. That is beyond the scope of this post, but let's say that you have deleted more than 99% of your rows, and you want to reorganize your table and rebuild the index to gain disk space and performance.

Here follows a demonstration of how to do that. 

SQL> DROP TABLE t1;

Table dropped.

SQL> 
SQL> CREATE TABLE t1
2 (
3 r NUMBER,
4 txt VARCHAR2 (4000),
5 y NUMBER
6 );

Table created.

SQL> 
SQL> CREATE INDEX i1
2 ON t1 (r);

Index created.

SQL> 
SQL> INSERT INTO t1
2 WITH t
3 AS ( SELECT *
4 FROM DUAL
5 CONNECT BY LEVEL < 1001)
6 SELECT ROWNUM r, LPAD ('X', 100, '.') txt, MOD (ROWNUM, 2) y
7 FROM t, t;

1000000 rows created.

SQL> 
SQL> DROP TABLE t2;

Table dropped.

SQL> 
SQL> CREATE TABLE t2
2 (
3 r NUMBER,
4 txt VARCHAR2 (4000),
5 y NUMBER
6 )
7 PARTITION BY HASH (r)
8 (PARTITION T2_P1);

Table created.

SQL> 
SQL> CREATE INDEX i2
2 ON t2 (r)
3 LOCAL (PARTITION i2_p1);

Index created.

SQL> 
SQL> INSERT INTO t2
2 WITH t
3 AS ( SELECT *
4 FROM DUAL
5 CONNECT BY LEVEL < 1001)
6 SELECT ROWNUM r, LPAD ('X', 100, '.') txt, MOD (ROWNUM, 2) y
7 FROM t, t;

1000000 rows created.

SQL> 
SQL> DROP TABLE t3;

Table dropped.

SQL> 
SQL> CREATE TABLE t3
2 (
3 r NUMBER,
4 txt VARCHAR2 (4000),
5 y NUMBER
6 )
7 PARTITION BY RANGE (r)
8 SUBPARTITION BY HASH (r)
9 SUBPARTITION TEMPLATE (SUBPARTITION s1 )
10 (PARTITION T3_P1 VALUES LESS THAN (maxvalue));

Table created.

SQL> 
SQL> CREATE INDEX i3
2 ON t3 (r)
3 LOCAL (PARTITION i3_p1
4 (SUBPARTITION i3_p1_s1));

Index created.

SQL> 
SQL> INSERT INTO t3
2 WITH t
3 AS ( SELECT *
4 FROM DUAL
5 CONNECT BY LEVEL < 1001)
6 SELECT ROWNUM r, LPAD ('X', 100, '.') txt, MOD (ROWNUM, 2) y
7 FROM t, t;

1000000 rows created.

SQL> 
SQL> COMMIT;

Commit complete.

SQL> 
SQL> SELECT segment_name,
2 segment_type,
3 partition_name,
4 sum(bytes),
5 count(*)
6 FROM user_extents
7 WHERE segment_name IN ('T1', 'T2', 'T3', 'I1', 'I2', 'I3')
8 group by
9 segment_name,
10 segment_type,
11 partition_name
12 ORDER BY segment_name, partition_name;

SEGMENT_NA SEGMENT_TYPE PARTITION_ SUM(BYTES) COUNT(*)
---------- ------------------ ---------- -------------- --------------
I1 INDEX 16,777,216 31
I2 INDEX PARTITION I2_P1 16,777,216 31
I3 INDEX SUBPARTITION I3_P1_S1 16,777,216 31
T1 TABLE 134,217,728 87
T2 TABLE PARTITION T2_P1 134,217,728 16
T3 TABLE SUBPARTITION T3_P1_S1 134,217,728 16

I created 3 tables, T1, T2 which is partitioned, T3 which is subpartitioned. There is a slight difference in the number of extents between partitioned and non-partitioned table, but this ASSM, so it is fine.

SQL> DELETE FROM t1
2 WHERE r > 1;

999999 rows deleted.

SQL>
SQL> COMMIT;

Commit complete.

SQL>
SQL> DELETE FROM t2
2 WHERE r > 1;

999999 rows deleted.

SQL>
SQL> COMMIT;

Commit complete.

SQL>
SQL> DELETE FROM t3
2 WHERE r > 1;

999999 rows deleted.

SQL>
SQL> COMMIT;

Commit complete.

SQL>
SQL> SELECT segment_name,
2 segment_type,
3 partition_name,
4 sum(bytes),
5 count(*)
6 FROM user_extents
7 WHERE segment_name IN ('T1', 'T2', 'T3', 'I1', 'I2', 'I3')
8 group by
9 segment_name,
10 segment_type,
11 partition_name
12 ORDER BY segment_name, partition_name;

SEGMENT_NA SEGMENT_TYPE PARTITION_ SUM(BYTES) COUNT(*)
---------- ------------------ ---------- -------------- --------------
I1 INDEX 16,777,216 31
I2 INDEX PARTITION I2_P1 16,777,216 31
I3 INDEX SUBPARTITION I3_P1_S1 16,777,216 31
T1 TABLE 134,217,728 87
T2 TABLE PARTITION T2_P1 134,217,728 16
T3 TABLE SUBPARTITION T3_P1_S1 134,217,728 16

I deleted the completed table but one row, however the size of the table and the number of extents did not change.

SQL> ALTER TABLE t1 MOVE;

Table altered.

SQL> 
SQL> ALTER INDEX I1 REBUILD;

Index altered.

SQL> 
SQL> ALTER TABLE t2 MOVE PARTITION T2_P1;

Table altered.

SQL> 
SQL> ALTER INDEX I2 REBUILD PARTITION I2_P1;

Index altered.

SQL> 
SQL> ALTER TABLE t3 MOVE SUBPARTITION T3_P1_S1;

Table altered.

SQL> 
SQL> ALTER INDEX I3 REBUILD SUBPARTITION I3_P1_S1;

Index altered.

SQL> 
SQL> SELECT segment_name,
2 segment_type,
3 partition_name,
4 sum(bytes),
5 count(*)
6 FROM user_extents
7 WHERE segment_name IN ('T1', 'T2', 'T3', 'I1', 'I2', 'I3')
8 group by
9 segment_name,
10 segment_type,
11 partition_name
12 ORDER BY segment_name, partition_name;

SEGMENT_NA SEGMENT_TYPE PARTITION_ SUM(BYTES) COUNT(*)
---------- ------------------ ---------- -------------- --------------
I1 INDEX 65,536 1
I2 INDEX PARTITION I2_P1 65,536 1
I3 INDEX SUBPARTITION I3_P1_S1 65,536 1
T1 TABLE 65,536 1
T2 TABLE PARTITION T2_P1 8,388,608 1
T3 TABLE SUBPARTITION T3_P1_S1 8,388,608 1

Now I have reorganized my tables and rebuilt my indexes.

The sized dropped to 64K or 8M and the fragmentation disappeared as the number of extents dropped to 1.

Note that you cannot rebuild a whole partitioned index (ORA-14086) nor reorganize a whole partitioned table (ORA-14511). You need to loop through each partition or subpartition.