O'Reilly logo

Oracle Database 11gR2 Performance Tuning Cookbook by Ciro Fiorillo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required