Fast Data Copy With “Create Table Select From” In PL/SQL

Although I work mainly in Java/J2EE, recently I had a requirement in Oracle to take temporary backup of a table and compare the data of original table with this backup table. So I had to copy the data from table A to a temp table TA.

First I tried creating temp table normally and copied the data using normal INSERT INTO SELECT query:

INSERT INTO TA SELECT * FROM A

This approach took a lot of time to execute.

Then I tried creating same temp table with different syntax. I tried CREATE TABLE SELECT FROM syntax of PL SQL for creating as well as copying the data into temp table:

CREATE TABLE TA AS SELECT * FROM A

The second approach was amazingly fast. The above sql can be generalized to create table as follow:

CREATE TABLE new_table_name [ ( column [, ...] ) ] AS SELECT [ ( column [, ...] ) ] FROM existing table_name

The SELECT statement can be in a complex form where data is been retrieved from several tables. For the new table, column names can be specified by including the column names in a comma-delimited list. Very important point to take note is that there should be the same number of columns specified in the column list preceding the AS clause (optional, if specified) for new table as the number of target columns that are returned by the select statement. If the optional list of columns in parentheses of new table contains different number of rows than the rows the SELECT statement returns, the following error message will be displayed:

ERROR: CREATE TABLE/AS SELECT has mismatched column count

In its simplest form, CREATE TABLE AS statement will create a complete backup of an existing table including all columns and data simply by using the statement CREATE TABLE new_table_name AS SELECT * FROM existing_table_name.

I googled a little to know why exactly the CREATE TABLE SELECT FROM approach is faster then normal INSERT INTO SELECT FROM and came to this blog entry which is saying:

The SELECT INTO statement is very fast, for one reason: the command isn’t logged for backup purposes. More precisely, the command can be inside a transaction and any rollback command will correctly undo its effects. However, the new values aren’t permanently stored in the log file, therefore after this command you can only perform a complete database backup (incremental backup raise errors).

Ahmed Hazzaf