Using create table as select

In this recipe we will see how to create a table as the result of a selection from other tables or views in the database.

How to do it...

The following steps demonstrate how to use use selection to create a table:

  1. Connect to the SH schema:
    CONNECT sh@TESTDB/sh
    
  2. Create the empty table MY_SALES, and copy the SALES table structure:
    CREATE TABLE MY_SALES AS SELECT * FROM SALES WHERE ROWNUM < 1;
    
  3. Insert all the rows from the SALES table into MY_SALES using direct path inserting :
    SET TIMING ON
    INSERT /*+ APPEND */ INTO MY_SALES SELECT * FROM SALES;
    SET TIMING OFF
    
  4. Drop the MY_SALES table:
    DROP TABLE MY_SALES;
    
  5. Create table MY_SALES as a selection from SALES table:
    SET TIMING ON CREATE TABLE MY_SALES AS SELECT * FROM SALES; SET TIMING ...

Get Oracle Database 11gR2 Performance Tuning Cookbook now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.