 |
|
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
cust_cluster
(
customer_key number
);
create index
cust_cluster_ind
on cluster
cust_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
customer_new
cluster
cust_cluster(customer_key)
as
select *
from
customer
where
rownum < 1
;
Now, we have an empty cluster table and we are
ready to populate it with the rows from our original customer table.
declare
customer c1%rowtype;
placeholder number;
cursor
c1
is
select
customer_key
from
customer;
begin
/*loop thru customer table*/
for customer in c1
loop
/*Check if this cluster_key is already inserted in cluster*/
begin
select 1
into
placeholder
from
dual
where
exists (
select 'X'
from customer_new
where customer_key=customer.customer_key);
exception
when NO_DATA_FOUND then
/*Not in new table, so insert all rows with this
cluster_key*/
insert into
customer_new
(select * from customer
where
customer_key=customer.customer_key);
commit;
end;
end loop;
end;
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:
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.
|
