Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

 

 
 

On Table Reorg and Index Rebuild

Expert Oracle Tips by Laurent Schneider

January 31, 2011

 

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.

 
 
 
Get the Complete
Oracle Tuning Details 

The landmark book "Oracle Tuning: The Definitive Reference Second Edition" has been updated with over 1,150 pages of expert performance tuning tips. It's packed with scripts and tools to hypercharge Oracle 11g performance and you can buy it for 40% off directly from the publisher.
 


Download your Oracle scripts now:

www.oracle-script.com

The definitive Oracle Script collection for every Oracle professional DBA

 

 
   

 Copyright © 1996 -2016 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks
 

 

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster



Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA