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
Code language: SQL (Structured Query Language) (sql)
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
Code language: SQL (Structured Query Language) (sql)
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
Code language: SQL (Structured Query Language) (sql)
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).
Get our Articles via Email. Enter your email address.

You may also like...

7 Comments

  1. Arne says:

    Nice , works perfectly!

  2. This site rocks!

  3. Cinta says:

    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

    • 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.

  4. PPalomino says:

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

  5. MWaters says:

    Is there a way to use the create table as command and include all the indexes that go with the table? Or must those be created in a separate command?

  6. anonymous says:

    good article

Leave a Reply

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