Databases Reference
In-Depth Information
In step 8 we retrieve the execution plan for the
CREATE
TABLE
AS
SELECT
statement, and
the corresponding result can be seen in the following screenshot:
We can see that the execution plans related to both operations are identical in what concerns
the load statement—they access the same rows in the same way. They differ only in the first
part—the
INSERT
statement in the first case, the
CREATE
TABLE
in the latter.
There's more...
We can use the
CREATE
TABLE
AS
SELECT
statement to obtain a copy of a table, eventually
filtering out some records that we don't want to keep in the table.
To further increase the performance of the
CREATE
TABLE
AS
SELECT
statement, we can
use the
NOLOGGING
and/or
PARALLEL
clause. The
NOLOGGING
clause doesn't write redo log
entries for the operation, which is unrecoverable due to this behavior. The
PARALLEL
clause
enables the database to use multiple parallel slave processes to execute the statement.
Please note that using
NOLOGGING
can break other processes
like Streams, Physical StandBy Databases, and GoldenGate
among others. You need to consider all these implications
when planning to use this clause in a production environment.
A typical use of this query is consolidating historic data or deleting them. If we want to delete
a large amount of data from a table, it's better to create a new table by selecting the relevant
record to keep in the table, truncating the old table, and then renaming the new table as the
old one.
