On table reorg and index rebuild

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 table missing or one index invalid.

Ok, let’s take a case where table reorg and index rebuild is good.

One of your table was never cleaned up, it grew to 100000000 rows over the last 5 years and you need only the last 2 weeks.

One of your task will be to create a job to clean up your table on a weekly basis to delete rows older than 14 days. This is beyond the scope of this post.

Now 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 is the demo


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 disappeard as the number of extents dropped to 1.

Note 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.

One thought on “On table reorg and index rebuild”

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>