Reorganizing Oracle Tables
Oracle Tips by Burleson Consulting
For additional information, also see:
Using the SQL*PLus table copy; or
There are a number of reasons why the Remote DBA
should monitor and periodically reorganize tables. Foremost, the
internal structure of Oracle tables is constantly changing because of
update activities. Since most applications are oriented toward
heavy online transaction processing, the internal structures of tables
typically become less optimal over time. Reorganization substantially
improves table performance.
The specific reasons for reorganizing a table
are presented here in their order
Chained rows can be coalesced Any rows
that chained as a result of row expansion will be coalesced. This
does not include any table that contains RAW or LONG RAW columns,
since these columns may often chain across blocks, especially with
small Oracle block sizes.
Table rows can be physically resequenced in
primary index order By resequencing the table rows to be in the
same physical order as the primary index, you can greatly improve
the speed of an application. The data can quickly be accessed by
queries that use the primary-key index with far less I/O since the
adjacent rows reside on contiguous blocks. This is a very important
Oracle tuning technique that can reduce system I/O by more than 50
percent for some queries.
Free space within the data blocks can be
coalesced This is because tables with two freelist groups and 20
freelists, for example, maintain a total of 42 separate freelists,
each with its own set of free blocks. Thus, these tables frequently
extend even though there is a lot of unused space in the table. A
coalesce operation will help any operations that require full table
scans of the tables.
NOTE: Tables with a high number of extents are not
on this list. It is an urban legend that tables with hundreds of
extents need to be reorganized, and this myth has been perpetuated
because high extents are often accompanied with high chained rows. In
practice, studies by Remote DBA gurus such a Mike Ault have shown that tables
with a high number of extents will often perform faster than tables
with a single extent. This is because the rows are more widely
distributed along the tablespace. Mike Ault notes “This is only true
for SELECTS. For operations such as INSERT, UPDATE and DELETE, there
may be performance hits with high extents. In one test using INSERT
and UPDATE a highly fragmented table took much longer than with a
non-fragmented table. The test was done using fresh built tables with
Now that we have examined the causes of table
reorganizations, let’s examine some of the methods that are used to
reorganize tables. There are several methods available to the Oracle
Remote DBA to use for reorganizing tables. These include:
The create table as select (CTAS) SQL statement
Oracle’s export/import utilities
Oracle’s SQL*Loader utility
This table summarizes the benefits of each of these
create table as select (CTAS) using
order by or index hint
Very fast reorganization, resequencing of
the physical rows to reduce I/O
Disk required for duplicate tablespaces
Imports tables with referential integrity
constraints in the
Very slow, no row clustering possible,
difficult for tablespaces
Unload, sort flat file, SQL*Loader
Allow unloaded file to be sorted prior to
Somewhat slow, relies
on flat files
CAUTION: There are several dangerous third-party
products that claim to perform tablespace-level reorganizations, and
many of these tools are fraught with bugs and are unreliable. The
experienced Remote DBA will rely on standard Oracle utilities.
Isolating Large Tables into Separate Tablespaces
In any large database, the Remote DBA should isolate
large tables into their own tablespaces to better manage the growth of
the table and to make reorganizations easier. The goal is to quickly
move the table into a new tablespace with a minimum of service
interruption. Let’s begin by reviewing the Oracle utilities, their
benefits, and their limitations. If you carefully identify and
segregate Oracle tables into dedicated tablespaces, the remaining
objects will not require reorganization.
We have repeatedly stressed that it is very
important to segregate highly volatile tables into separate
tablespaces. There are several compelling reasons for segregating your
large tables into separate tablespaces. The most important benefit
from segregation is improved manageability of the table. If a large,
active table resides in its own separate tablespace, you can control
the I/O by moving the tablespace to different disks. The other reason
for table segregation is improved space usage.
Having too many tables residing in a single
tablespace can make reorganization risky. It is far better to isolate
the active tables and selectively reorganize the remaining tables as
required. As we explained in the previous section, you’ll most often
reorganize tables that have unbalanced multiple freelists, poor
clustering factor of the table to the primary-key index, or chained
Using CTAS to Reorganize a Table
Basically, the create table as select (CTAS)
statement copies the selected portion of the table into a new table.
If you select the entire table with an order by clause or an
index hint, it will copy the rows in the same order as the primary
index. In addition to resequencing the rows of the new table, the CTAS
statement coalesces free space and chained rows and resets freelists,
thereby providing additional performance benefits. You can also alter
table parameters, such as initial extents and the number of freelists,
as you create the new table. The steps in a CTAS reorganization
Define a separate tablespace to hold the reorganized table.
Disable all referential integrity constraints.
Copy the table with CTAS.
Reenable all referential integrity constraints.
Rebuild all indexes on the new table.
The main benefit of CTAS over the other methods
is speed. It is far faster to use CTAS to copy the table into a new
tablespace (and then recreate all RI and indexes) than it is to use
the export/import method. Using CTAS also has the added benefit of
allowing the rows to be resequenced into the same order as the primary
index, thereby greatly reducing I/O. Within CTAS, there are two
general reorganization methods.
Two Alternatives for Using CTAS
It is always recommended that you resequence
the table rows when performing a table reorganization with CTAS
because of the huge I/O benefits. You can use the CTAS statement in
one of two ways. Each of these achieves the same result, but they do
it in very different ways:
The approach you choose depends on the size of
the table involved, the overall processing power of your environment,
and how quickly you must complete the reorganization.
The details of each CTAS approach are discussed
more fully next, but in either case, when you create the new table,
you can speed the process by using the Oracle nologging option
(this was called unrecoverable in Oracle7). This skips the
added overhead of writing to the redo log file. Of course, you cannot
use the redo logs to roll forward through a nologging
operation, and most Remote DBAs take a full backup prior to using CTAS with
nologging. Let’s examine the two methods and see their
Using CTAS with the ORDER BY Clause
When using CTAS with the ORDER BY
clause, you are directing Oracle to perform the following operations,
as shown in Figure 10-13.
Figure 10-91: Using CTAS with ORDER BY
As we can see, the full table scan can be used
with Parallel Query to speed the execution, but we still have a large
disk sort following the collection of the rows. Because of the size of
most tables, this sort will be done in the TEMP tablespace.
Here is an example of the SQL syntax to perform
a CTAS with ORDER BY:
storage (initial 500m
parallel (degree 11)
as select * from customer
order by customer_number;
Using CTAS with ORDER BY can be very slow without the
PARALLEL clause. A parallel full table scan reads the original table
quickly (in nonindex order).
As we know from Oracle Parallel Query, the CTAS
operation will cause Oracle to spawn to multiple background processes
to service the full table scan. This often makes the ORDER BY approach
faster than using the index-hint approach to CTAS. The choice to use
PARALLEL depends on the database server. If your hardware has multiple
CPUs and many (perhaps hundreds of) processes, using PARALLEL is
likely to be significantly faster. However, if your hardware
configuration has a relatively modest number of processes (such as the
four specified in the example), the index-hint approach is likely to
Using CTAS with an Index Hint
The CTAS with an index hint executes quite
differently than CTAS with ORDER BY. When using an index hint, the
CTAS begins by retrieving the table rows from the original table using
the existing index. Since the rows are initially retrieved in the
proper order, there is no need to sort the result set, and the data is
used immediately to create the new table, as shown below.
The syntax for CTAS with an index hint appears
storage (initial 500m
as select /*+ index(customer customer_primary_key_idx) */ *
When this statement executes, the database traverses the
existing primary-key index to access the rows for the new table,
bypassing the sorting operation. Most Oracle Remote DBAs choose this method
over the ORDER BY approach because the runtime performance of
traversing an index is generally faster than using the PARALLEL clause
and then sorting the entire result set.
Now that we see how CTAS works for table
reorganizations, let’s explore a method for reorganizing many tables
with CTAS by submitting parallel reorganization tasks.
Reorganizing Multiple Tables with CTAS
Many Oracle Remote DBAs who use the CTAS method
segregate the important tables into their own tablespace and define
duplicate tablespaces for the table. For example, a customer table
might reside in tablespace cust1 until reorganization, when it would
move to cust2. Later, during the next reorganization, it would move
back to the cust1 tablespace.
When you are reorganizing from one tablespace
to another, you should always keep the backup copy of the table and a
back-out procedure. The original table can remain online in cases of
data inconsistency, and you will never need to perform a full restore
if the table becomes corrupt. The only real cost of this duplicate
tablespace method is the disk space required to duplicate major
The following code shows the SQL syntax needed
to reorganize a table by copying it from one tablespace to another and
changing the table-storage parameters as needed. In addition to the
CTAS statement (in this example using the index-hint approach), we see
the setup preceding the statement and the renaming and index-creation
steps that follow it.
This SQL script creates a new customer table in
a new tablespace (cust2) using the CTAS statement with an index hint.
Because the new table’s rows are physically sequenced in the same
order as the primary index, data retrieval for contiguous elements
will occur faster and with less I/O. The next time the table is
reorganized, it can be copied back to the original tablespace (cust1).
set timing on;
create table new_customer
storage (initial 900m
select /*+ index(customer customer_key_idx) */
* from owner.customer;
rename customer to old_customer;
rename new_customer to customer;
create index customer___0 . . . . . ;
Now that we see how a single script works, let’s expand
on this and see how to submit multiple CTAS reorganizations.
Multiplexing Table Reorganization with CTAS
If you have several tables to reorganize, you can save
time by running the jobs simultaneously. When you process the table
reorganizations in parallel, the total time required to reorganize all
the tables is no more than the time required for the largest table.
For example, if you need to reorganize 100 gigabytes of table data in
a single weekend, the parallel approach is the only way to go.
Below is a Korn shell script you can use to
execute the reorganization. The script uses the UNIX nohup
command to submit simultaneous CTAS reorganizations at the same time.
# Written by Donald Keith Burleson
# usage: nohup don_reorg.ksh > don_reorg.lst 2>&1 &
# Ensure that running user is oracle . . . . .
oracle_user=`whoami|grep oracle|grep -v grep|wc -l`;
if [ $oracle_num -lt 1 ]
then echo "Current user is not oracle. Please su to oracle and
# Ensure that Oracle is running . . . . .
oracle_up=`ps -ef|grep pmon|grep -v grep|wc -l`;
if [ $oracle_num -lt 1 ]
then echo "ORACLE instance is NOT up. Please start Oracle and
# Submit parallel CTAS reorganizations of important tables
nohup reorg.ksh CUSTOMER >customer.lst 2>&1 &
nohup reorg.ksh ORDER >order.lst 2>&1 &
nohup reorg.ksh ITEM >item.lst 2>&1 &
nohup reorg.ksh LINE_ITEM >line_item.lst 2>&1 &
nohup reorg.ksh PRODUCT >product.lst 2>&1 &
We will revisit the
CTAS method for table reorganizations when we discuss row resequencing
later in this chapter. Next, let’s look at row chaining and see how
the Remote DBA can reduce I/O by ensuring that all rows reside on a single
Warnings about using NOLOGGING
option with CTAS
We must always remember to take a full-backup
after doing database maintenance with the NOLOGGING option.
If you loose lost the datafile containing the
table, you will get the following message and your data will be
ORACLE data block corrupted (file # 6, block # 8)
ORA-01110: data file 6: '/u02/oracle/app/oradata/customer.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
This is an excerpt from "Oracle9i
High Performance tuning with STATSPACK" by Oracle Press.
If you like Oracle tuning, you may enjoy the new book "Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts.
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today.