Remote DBA Plans
Remote DBA Service

Reorganizing Oracle Tables

Oracle Tips by Burleson Consulting

For additional information, also see:

Using the SQL*PLus table copy; or

Using dbms_redefinition

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
of importance:

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 import.”

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

Method	Benefits	Limitations
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
Export/import	Imports tables with referential integrity constraints in the proper order	Very slow, no row clustering possible, difficult for tablespaces
Unload, sort flat file, SQL*Loader	Allow unloaded file to be sorted prior to reload	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 rows.

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 include:

1. Define a separate tablespace to hold the reorganized table.

2. Disable all referential integrity constraints.

3. Copy the table with CTAS.

4. Reenable all referential integrity constraints.

5. 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:

Use CTAS in conjunction with the ORDER BY clause.
Use CTAS in conjunction with a “hint” that identifies the index to use.

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 respective differences.

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:

create table new_customer
   tablespace customer_flip
      storage (initial              500m
               next                 50m
               maxextents           unlimited)
   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 be faster.

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 here:

Xcreate table new_customer
   tablespace customer_flip
      storage (initial             500m
                 next               50m
                 maxextents         unlimited)
   as select /*+ index(customer customer_primary_key_idx) */ *
   from customer;

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 reorganized tablespaces.

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

connect owner/passwd;
set timing on;

create table new_customer
tablespace CUST2
   storage (initial         900m
            next            50m
            maxextents      unlimited
            )
unrecoverable
as
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.

master_reorg.ksh

#!/bin/ksh
# 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`;
oracle_num=`expr $oracle_user`
if [ $oracle_num -lt 1 ]
then echo "Current user is not oracle. Please su to oracle and retry."
exit
fi

# Ensure that Oracle is running . . . . .
oracle_up=`ps -ef|grep pmon|grep -v grep|wc -l`;
oracle_num=`expr $oracle_up`
if [ $oracle_num -lt 1 ]
then echo "ORACLE instance is NOT up. Please start Oracle and retry."
exit
fi

#************************************************************
# 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 data block.

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 unrecoverable:

ORA-01578: 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.