Data Loader…
Fastest Data Loading
In this paper I am going to explain you about data loading Techniques
- Sql Loader
- Direct Load Insert
- Guidelines for Sql Loader and Direct Load
Sql Loader
Sql Loader loads data from external files into oracle database tables
Direct Load Insert
It can be used to copy data within same oracle database. Bypass the buffer cache and speed up the process by writing data directly into the data files.
Direct Load inserts only supports INSERT … SELECT syntax only. ‘Insert values(…)’ wont support. You could see parallel and Serial Inserts in the Direct Load Insert Method.
Data will be inserted beyond high water mark of the table segment or each partition segment. High water mark is the level in which block have never been written to that area.
Serial Direct Load Insert
Example:
INSERT /*+ APPEND */ INTO emp
NOLOGGING
SELECT * FROM sarith.emp;
COMMIT;
Parallel Direct Load Insert
Each parallel execution server allocates a new temporary segment. When a statement executes, the parallel execution coordinator merges the new temporary segments into the primary table segment.
Example
ALTER SESSION ENABLE PARELLEL DML;
INSERT /*+PARALLEL(sarith.emp,3) */
INTO sarith.emp NOLOGGING
SELECT * FROM hr.emp_bad;
ALTER SESSION command must be executed at the beginning of the transaction
3 Indicates the number of parallel process
SQL Loader
Sql Loader loads data from external files into oracle database Data files
- It can use more than one input files
- Several input can be merged for loading
- Input field can be fixed or Variable
- Data can be loaded from diff types of media like Disk, Tape, named Pipes
- Data can be Loaded into several tables in one Run
- Column value can be auto generated
- Data Can be loaded directly into data file
Example
$sqlldr scott/tiger \
>control = example.ctl \
>log=example.log direct=y
An example for Control File
-- This is a sample control file
LOAD DATA
INFILE 'sample.dat'
BADFILE 'sample.bad'
DISCARDFILE 'sample.dsc'
APPEND
INTO TABLE emp
WHEN (57) = '.'
TRAILING NULLCOLS
(hiredate SYSDATE,
deptno POSITION(1:2) INTEGER EXTERNAL(2)
NULLIF deptno=BLANKS,
job POSITION(7:14) CHAR TERMINATED BY WHITESPACE
NULLIF job=BLANKS "UPPER(:job)",
mgr POSITION(28:31) INTEGER EXTERNAL
TERMINATED BY WHITESPACE, NULLIF mgr=BLANKS,
ename POSITION(34:41) CHAR
TERMINATED BY WHITESPACE "UPPER(:ename)",
empno POSITION(45) INTEGER EXTERNAL
TERMINATED BY WHITESPACE,
sal POSITION(51) CHAR TERMINATED BY WHITESPACE
"TO_NUMBER(:sal,'$99,999.99')",
comm INTEGER EXTERNAL ENCLOSED BY '(' AND '%'
":comm * 100"
)
Infile can be a CSV file or any file that must have a proper format.
