Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




Row Re-Sequencing with Index Clusters
Oracle Tips by Burleson

A popular method for row resequencing I mentioned earlier is the creation of an index cluster. The main advantage of an index cluster is that row data is stored in data blocks in the same sequence as the index cluster key. Hence, index range scans that retrieve closely related key values will probably be able to fetch all of the rows in a single disk I/O. This is also popular in cases with a non-unique index whereby all rows with the same key values will be stored on adjacent data blocks.

Oracle provides a method for converting an existing table to a cluster table. As an example, consider the following table:

create table
customer (
   customer_key      number,
   customer_stuff    stuff_type

Let’s assume that we have investigated the performance of SQL against this table, and virtually all index range scans are using the customer key. Here is how we can convert this table into a cluster table. First, we define the cluster and the index for the cluster:

create cluster
      customer_key number

create index
on cluster

Next, we use CTAS to copy in the new table definition for the cluster. Note that we used the where rownum < 1 clause to prevent the copying of the rows. This is because we will want to recopy the rows into the cluster in their proper order.

create table

   select *
   rownum < 1

Now, we have an empty cluster table and we are ready to populate it with the rows from our original customer table.

customer    c1%rowtype;
placeholder number;

/*loop thru customer table*/
for customer in c1
  /*Check if this cluster_key is already inserted in cluster*/
   select 1

      exists (
         select 'X'
         from customer_new
         where customer_key=customer.customer_key);
    when NO_DATA_FOUND then
      /*Not in new table, so insert all rows with this cluster_key*/
      insert into
         (select * from customer
             where customer_key=customer.customer_key);
end loop;

rename customer     to customer_old;
rename customer_new to customer;

-- Finally, transfer Referential Integrity (RI) constraints
-- and non-cluster indexes

Row Resequencing with CTAS

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, thereby preventing future chaining. 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.      Re-enable 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 re-sequence 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 in the sections that follow, 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 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 6-6.

Figure 6: Using CTAS with order by

As you 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 you 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 in Figure 6--7.

Figure 7: Using CTAS with an index hint

The syntax for CTAS with an index hint appears here:

create 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 DBAs choose this method over the order by approach because the run time performance of traversing an index is generally faster than using the PARALLEL clause and then sorting the entire result set.

Next, let’s take a look at the Oracle table storage parameters and see how they affect the performance of SQL statements.

This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



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

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter