 |
|
Get the Complete
Oracle Utility Information
The landmark book
"Advanced Oracle
Utilities The Definitive Reference" contains over 600 pages of
filled with valuable information on Oracle's secret utilities.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
|
March 22, 2005
Article Review
Load Your Data Faster
By Roger Schrag
SQL> UPDATE RMOUG (www.rmoug.org),
summer 2003 issue
In this article on showing the various ways to
load data into Oracle tables, Schrag offers up a simple, makes sense
approach (6 meg of data in 100k rows) and shows real-world timings
for his tests of the various Oracle data loading techniques. Schrag
offers results and insights into each of the Oracle approaches:
Oracle
Server Data Load
Method Seconds CPU seconds
SQL*Loader direct path
load
9 3
Bulk row
inserts
14 7
CTAS from an external
table 15 8
Insert (with append) from
external table 15 8
SQL*Loader conventional
path 81 12
Single row inserts without
indexes 130 35
Single rows
inserts 172 52
These results confirm the conventional wisdom
about Oracle data loading techniques, but the novel tests using
external tables for data loading earn Schrag four stars.
The only missing pieces (and perhaps topics to
cover in a
follow-up article) would be:
1. Data Pump:
Timings for the new Oracle 10g data pump (impdp) which allegedly has
faster data loading speeds.
2. OCI:
Timings for a native C program using Oracle Call Interface (OCI).
3. Storage:
It would also be interesting to see if the type of storage has an
effect on the data loading timings, as suggested by Texas Memory
Systems tests with super-fast data loading with solid-state disks
(RAM-SAN).
4. Parallelism:
It would be great to see if these data load speeds scale with
parallelism, both parallel hints (SQL inserts) and parallelized,
concurrent loads into the same target table.
Overall, this is a comprehensive and satisfying
article, with plain English explanations and easy-to-understand
methods.
