Other Optimizations - Oracle Database 11gR2 Performance Tuning - page 349

Another issue can arise when using direct path insert—remember that conventional load
inserts data in memory—and only the redo log is written to the disk on commit. The database
writer works asynchronously and the direct path insert writes directly to the disk blocks.
Hence, if I/O is a bottleneck in the database, direct path inserts could be slower than the
conventional load counterpart.

See also

F The Using create table as select and Loading data with SQL*Loader and Data Pump
recipes in this chapter

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;

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

5. Drop the MY_SALES table:

DROP TABLE MY_SALES;

6. Create table MY_SALES as a selection from SALES table:

SET TIMING ON
CREATE TABLE MY_SALES AS SELECT * FROM SALES;
SET TIMING OFF