need to understand how to create table
using CTAS. Can you show an example a create table
The create table using CTAS command is very
The Create table as select (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. Create table as select (CTAS) has the following
new_initial next new_next freelists new_freelist_number )
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.
Create table with
Running a create table as
select (CTAS) in parallel can dramatically speed up SAP
table reorganization. As a rule of thumb, the parallel
option is used only on SAP 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:
parallel (degree 4)
with CTAS using 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.
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 allow the use
of the INDEX "hint" to request an ordered copy of the table.
Here is an example of INDEX hint with CTAS.
create table vbap_sorted
storage (initial 500m
select /*+ index(vbap vbap___0) */
See these related notes on create table with CTAS:
Oracle Training from Don Burleson
The best on site
training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!
Burleson is the American Team
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
and include the URL for the page.
Copyright © 1996 - 2020
All rights reserved by
is the registered trademark of Oracle Corporation.