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 OFF
   

6. Inspect the execution plan for the INSERT statement in step 3:

   EXPLAIN PLAN FOR
    INSERT /*+ APPEND */ INTO MY_SALES
    SELECT * FROM SALES;
   SELECT * FROM TABLE(
    DBMS_XPLAN.DISPLAY(null,null,'TYPICAL -BYTES'));
   

7. Drop the MY_SALES table:

   ...