SQL*Loader

- SQL*Loader loads the data from textfile into database, the utility to use for high performance data loads.
- SQL*Loader reads a data file and a description of the data which is defined in the control file.
- During processing, SQL*Loader writes messages to the log file, bad rows to the bad file, and discarded rows to the discard file.
- Control file contains information that describes how the data will be loaded
- Log file contains information about the SQL*loader execution and should be viewed after each SQL*Loader job is complete. Especially interesting is the summary information at the bottom of the log, including CPU time and elapsed time.
- can get help from the command prompt by using $sqlldr help=Y

Example:

create table load
(
emp_id number,
emp_name varchar2(25),
salary varchar2(20)
);


Data file : data.csv

100,"Vikenesh",6000
101,"Raman", 5000
103,"Alan", 8000
104,"Kumar"


Loader File:load.sql

LOAD DATA
INFILE 'data.dat'
INSERT INTO TABLE load
FIELDS terminated by "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
emp_id ,
emp_name ,
salary
)

Load the data into Database

$sqlldr username/password@dbalias.qc control=load.sql data=data.csv

SQL*Loader: Release 10.2.0.1.0 - Production on Tue Apr 13 17:15:10 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Commit point reached - logical record count 3
Commit point reached - logical record count 4

$

- Specify username, password, dbalias name
- Specify the control file and data file name in the command prompt
- If the DB table have already data, use UPDATE instead of INSERT in the loader file, load.sql

Check the Database

SQL> select * from load;

    EMP_ID EMP_NAME                  SALARY
---------- ------------------------- -------------------
       100 Vikenesh                  6000
       101 Raman                     5000
       103 Alan                      8000
       104 Kumar

Loader file will insert the data into the database, where employee id 104 Kumar is the unknown of the salary details.

Maximizing SQL*Loader Performance:

SQL*Loader is flexible and offers many options that should be considered to maximize the speed of data loads. These include:

1. Use Direct Path Loads : The conventional path loader essentially loads the data by using standard insert statements. The direct path loader (direct=true) loads directly into the Oracle data files and creates blocks in Oracle database block format. There are certain cases, however, in which direct path loads cannot be used (clustered tables). To prepare the database for direct path loads, the script $ORACLE_HOME/rdbms/admin/catldr.sql.

2. Disable Indexes and Constraints: For conventional data loads only, the disabling of indexes and constraints can greatly enhance the performance of SQL*Loader.

3. Use a Larger Bind Array: For conventional data loads only, larger bind arrays limit the number of calls to the database and increase performance. The size of the bind array is specified using the bindsize parameter. The bind array's size is equivalent to the number of rows it contains (rows=) times the maximum length of each row.

4. Use ROWS=n to Commit Less Frequently. For conventional data loads only, the rows parameter specifies the number of rows per commit. Issuing fewer commits will enhance performance.

5. Use Parallel Loads: Available with direct path data loads only, this option allows multiple SQL*Loader jobs to execute concurrently based on number of CPU's in ther server( $sqlldr control=first.ctl parallel=true direct=true )

7. Disable Archiving During Load. While this may not be feasible in certain environments, disabling database archiving can increase performance considerably.

8. Use unrecoverable : unrecoverable option (unrecoverable load data) disables the writing of the data to the redo logs. This option is available for direct path loads only.