Fast data copy with “Create Table Select From” in PL/SQL

plsql-create-table-select-oracleAlthough 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).



5 Comments

  • Arne 13 August, 2009, 13:35

    Nice , works perfectly!

  • Bill Bartmann 2 September, 2009, 1:30

    This site rocks!

  • Cinta 29 October, 2012, 11:01

    I have a question.

    create table tablename as select...
    

    is not working when function is been used in select statement like

    create table tablename as (select TO_CHAR(field1,'YYYY-MM-DD') from table2).
    

    im getting the follwoing error

    ORA-00998: must name this expression with a column alias

    plz let me know if there is any other alrenative create statement accepting the above scenario.
    Kindly help me on this

    • Viral Patel 5 November, 2012, 16:51

      Hi, You need to assign an alias name to TO_CHAR function. For example:

      create table tablename as (select TO_CHAR(field1,'YYYY-MM-DD') as somedate from table2)
      

      This will work.

  • PPalomino 21 June, 2013, 12:35

    Hi, is fine but is not working when there is LONG types fields in existing_table_name.

Leave a Reply

Your email address will not be published. Required fields are marked *

Note

To post source code in comment, use [code language] [/code] tag, for example:

  • [code java] Java source code here [/code]
  • [code html] HTML here [/code]

Current day month ye@r *