Databases Reference
In-Depth Information
See also
F
The
Disk
Tuning
and
Strategies
to
distribute
Oracle
iles
and
Object
striping
recipes in
Chapter
10
,
Tuning I/O
, analyze useful techniques to avoid I/O bottlenecks and to get
maximum advantage from parallel SQL execution
Direct path inserting
In this recipe we will see how to insert many rows in a table using a particular
INSERT
statement to boost performance.
How to do it...
The following steps demonstrates multiple row insertions in the same
INSERT
statement:
1.
Connect to the
SH
schema:
CONNECT sh@TESTDB/sh
2.
Create an empty table
MY_SALES
with the
SALES
table structure:
CREATE TABLE MY_SALES AS SELECT * FROM SALES WHERE ROWNUM < 1;
3.
Insert all the rows from
SALES
table in the newly-created table:
SET TIMING ON
INSERT INTO MY_SALES SELECT * FROM SALES;
COMMIT;
SET TIMING OFF
4.
Empty the
MY_SALES
table:
TRUNCATE TABLE MY_SALES;
5.
Insert all of the rows from the
SALES
table in the newly-created table using direct
path inserting:
SET TIMING ON
INSERT /*+ APPEND */ INTO MY_SALES SELECT * FROM SALES;
COMMIT;
SET TIMING OFF
