 |
|
Oracle 11g
tuning: Oracle 11g and Expert Systems Technology
Oracle Tips by Burleson Consulting |
Oracle 11g and Expert Systems Technology
Oracle 11g - Reorganizing Tables with
Oracle Utilities
There are several Oracle utilities available
to the Oracle administrator to use for reorganizing tables. These
include the CREATE TABLE AS SELECT (CTAS) SQL statement and Oracle’s
export/import utilities. Most experienced Remote DBA’s will use CTAS
rather then export/import because of the improved speed and
reliability of CTAS. However, CTAS is not well suited for
reorganizing large numbers of tables that are interrelated by
referential integrity constraints. Few Remote DBA’s use Oracle’s
SQL*Loader utility to reorganize table because of the slow speed.
|
Method |
Benefits |
Limitations |
|
CREATE TABLE AS SELECT (CTAS) using ORDER BY
or INDEX hint |
Very fast reorganization, re-sequencing 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 |
Now let’s take a look at using export/import
to reorganize tables.
Using Create Table As Select (CTAS) to
Improve Table Performance
The CTAS statement is the most elegant
method for reorganizing an table or moving the table to
another tablespace. Instead of spending hours setting up parameter
files and job steps, you can copy and rename the table in three
simple SQL statements. The CTAS statement can be used to change
storage parameters for a table (INITIAL, NEXT, FREELISTS) and also
change the physical sequence of the table rows. CTAS has the
following syntax:
create
table xxx_new
tablespace new_tablespace_name
storage (initial new_initial next new_next
freelists new_freelist_number )
as
select * from xxx
order by primary_index_key_values;
There are several way to execute CTAS to
reorganize table; many of the options depend upon the version of
Oracle and the particular configuration of the Oracle database.
Parallel CTAS
Running CTAS in parallel can dramatically
speed up table reorganization. As a rule of thumb, the
parallel option is used only on database servers that have
multiple CPUs (for example, SMP processor CPUs), but there will be
some performance improvement when invoking parallelism, even on a
uni-processor CPU. Note that the UNRECOVERABLE clause can be used in
conjunction with the parallel clause, or you can run UNRECOVERABLE
CTAS without using parallelism. Here is an example of a parallel
CTAS:
create
table customer_sorted
tablespace customer_copy
storage (initial
500m
next
50m
maxextents
unlimited
)
parallel (degree 4)
as
select *
from
r3.customer
order by
cust_name,
cust_address
Using CTAS with an INDEX hint
This is an excellent way of reorganizing a
table to physically re-sequence the rows in the table. It is
commonly known that Oracle deliberately omitted the ORDER BY clause
in their implementation of CREATE TABLE AS SELECT. This is because
of Oracle’s early philosophy (pre-release 7.3.4 on AIX) that the
physical sequence of rows within a table should not matter to the
performance of the system. Unfortunately, this is not the case. As
any DB2 professional is aware, “clustering” the rows in a table in
the same order as the primary key index can greatly improve the
performance of the queries.
Note: Some releases of Oracle prior to 7.3.4
may support ORDER BY with CTAS, but for AIX 7.3.3 and before
generate a syntax error when ORDER BY is used with CTAS.
Oracle does still allow the use of INDEX “hints” to request an
ordered copy of the table. Of course, in , we would always use the
primary key index name in the INDEX hint. The primary key index in
always ends with a zero, such that the primary key index for the
CUSTOMER table would be CUSTOMER___0.
Here is an examples of INDEX hint with CTAS;
a detailed approach is described later in this chapter.
create
table customer_sorted
tablespace customer_copy
storage (initial
500m
next
50m
freelists
30
maxextents
unlimited
)
as
select /*+ index(customer customer___0)
*/
*
from
r3.customer
;
CTAS with ORDER BY
In some Oracle releases you can add the
ORDER BY clause to the CTAS statement to physically resequence the
table rows. Unlike CTAS with an index hint, the ORDER BY method can
be run in parallel since a full-table scan will be invoked.
Following the gathering of the table rows, all rows will be sorted
in the PTEMP tablespace before populating the new table.
create
table customer_sorted
tablespace customer_copy
storage (initial
500m
next
50m
maxextents
unlimited
)
as
select *
from
r3.customer
order by
customer_last_name;
CTAS UNRECOVERABLE
Please note that the UNRECOVERABLE option
has been deprecated and replaced with the NOLOGGING option.
 |
If you like Oracle tuning, see the
book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning
tips and scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
