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.
