Conventional Path Load

The default load mode. sqlldr reads rows, builds an array of bind values, and submits ordinary INSERT statements through the SQL layer — exactly as your application would.

What it gives you

• Triggers fire (BEFORE INSERT, AFTER INSERT, FOR EACH ROW)
• All constraints checked (PK, FK, CHECK, NOT NULL)
• Indexes maintained row-by-row
• Works on all table types (clustered, IOT, partitioned, materialised view containers)
• Online: other sessions keep reading and writing the table

What it costs

Conventional path is the slow lane. Each insert traverses the SQL parse → execute → buffer-cache → redo path. Expect 5–10× lower throughput than direct path.

Example

-- conventional.ctl
OPTIONS ( ROWS=10000, BINDSIZE=8388608, READSIZE=8388608 )
LOAD DATA
INFILE 'orders.csv'
APPEND
INTO TABLE sales.orders
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
  order_id     INTEGER EXTERNAL,
  customer_id  INTEGER EXTERNAL,
  order_date   DATE "YYYY-MM-DD",
  amount       DECIMAL EXTERNAL,
  status       CHAR(1)
)

sqlldr userid=hr/hr control=conventional.ctl   # DIRECT not specified → conventional

How rows turn into INSERTs

1. sqlldr allocates a bind array of size BINDSIZE bytes
2. Reads rows from the data file via a READSIZE-byte buffer
3. Fills the bind array
4. Issues INSERT INTO sales.orders VALUES (:1,:2,:3,:4,:5) with the array
5. Commits every ROWS rows
6. Repeats until EOF

The INSERT is path-by-path through Oracle: parse, optimise, execute, redo, undo, indexes, triggers — everything.

Tuning levers

Lever	Effect
BINDSIZE (bytes)	Bigger array = fewer round-trips; Oracle caps it where row × array exceeds it
READSIZE (bytes)	Should match BINDSIZE; otherwise one of them throttles
ROWS	Commit interval. Fewer commits = less log; too few risks losing work on crash
COLUMNARRAYROWS	Override the array size (rows) when bytes math is awkward

sqlldr userid=hr/hr control=orders.ctl bindsize=33554432 readsize=33554432 rows=20000

When to prefer conventional

• Triggers must fire (audit, sequence-from-trigger, derived columns)
• Foreign keys must be checked at load time
• Tables involved are clustered (direct doesn't support clusters)
• Loading a small amount (< ~100k rows) — direct's setup overhead isn't worth it
• Using INSERT mode (not APPEND) into an empty table you can't truncate

Best practices

• Disable secondary indexes during the load and rebuild after — biggest single win for conventional path
• Set ROWS high (10k–50k) so you commit infrequently but not so high that a crash loses too much work
• BINDSIZE = READSIZE; both should be at least 8 MB on modern systems
• Watch redo generation in v$sysstat — conventional path generates a lot of it
• Drop disable-validate to disable-novalidate on FKs during load to skip validation, then re-enable