Direct Path Load

Direct path bypasses the SQL layer entirely. Instead of executing INSERT statements through the buffer cache, sqlldr formats Oracle data blocks directly and writes them above the table's high-water mark via OS I/O. The result is 5–20× higher throughput than conventional path.

How it works

Data file → sqlldr parse → format data blocks → write above HWM → update HWM
                                                   ↓
                                         (skips: SQL parse, buffer cache,
                                          undo segments, trigger execution,
                                          FK lookups, row-by-row index insert)

Indexes are not maintained row-by-row. Instead, sqlldr builds a separate index key stream, merges it with the existing index, and rebuilds each index once at the end of the load.

Enabling direct path

# CLI flag
sqlldr userid=hr/hr control=orders.ctl DIRECT=TRUE

# Or in the control file OPTIONS block
OPTIONS ( DIRECT=TRUE )
LOAD DATA
INFILE 'orders.csv'
APPEND
INTO TABLE sales.orders
...

UNRECOVERABLE

Skips redo log generation for the data blocks (index redo is still written). Makes the load even faster; marks the loaded segments as requiring backup before they can be media-recovered.

UNRECOVERABLE                -- must appear BEFORE LOAD DATA
LOAD DATA
INFILE 'big_fact.csv'
APPEND
INTO TABLE dw.fact_sales
...

After an UNRECOVERABLE load, take an incremental or full backup before relying on those segments for recovery.

Index handling

State	What sqlldr does
Index in a valid state	Merges new keys, rebuilds index at end of load
Index already marked UNUSABLE	Skips index maintenance; index stays UNUSABLE
SKIP_INDEX_MAINTENANCE=TRUE	Marks all indexes UNUSABLE and skips maintenance — you rebuild manually
SORTED INDEXES (idx_name)	Tells sqlldr the data arrives pre-sorted on that index; avoids a sort step

# Skip index maintenance — rebuild yourself after the load
sqlldr userid=hr/hr control=orders.ctl direct=true skip_index_maintenance=true

# Rebuild afterwards
ALTER INDEX sales.orders_pk REBUILD NOLOGGING PARALLEL 4;
ALTER INDEX sales.orders_date_ix REBUILD NOLOGGING PARALLEL 4;

Parallel direct path

Multiple sqlldr sessions load simultaneously into the same table, each writing to its own space above the HWM:

# Session 1
sqlldr userid=hr/hr control=orders_p1.ctl direct=true parallel=true

# Session 2 (concurrent)
sqlldr userid=hr/hr control=orders_p2.ctl direct=true parallel=true

With PARALLEL=TRUE:

• The table must be in NOLOGGING mode or redo explodes
• Indexes are left UNUSABLE after the load — rebuild them after all sessions finish
• Use APPEND load mode (not REPLACE or TRUNCATE) in every session

-- Prepare table for parallel load
ALTER TABLE sales.orders NOLOGGING;
ALTER INDEX sales.orders_pk UNUSABLE;

-- After all sessions finish
ALTER TABLE sales.orders LOGGING;
ALTER INDEX sales.orders_pk REBUILD NOLOGGING PARALLEL 4;

Restrictions (what direct path cannot do)

Restriction	Reason
Triggers do not fire	No SQL execution path
FK constraints not checked	No row-by-row insert through SQL
Clustered tables	Cluster structure requires SQL-layer placement
Object tables	OID assignment requires SQL layer
Tables with active transactions	HWM cannot be updated safely
REPLACE / TRUNCATE with active queries	Table lock required
IOTs (Index-Organised Tables) with overflow	Overflow placement requires SQL path

If any of these apply, fall back to conventional path (drop DIRECT=TRUE).

Locking behaviour

Direct path acquires a full table lock (share) for the duration of the load. Other sessions can read the pre-load data but cannot write. With PARALLEL=TRUE the lock is held until the load finishes across all sessions.

Example — full direct path load

-- fast_orders.ctl
OPTIONS ( DIRECT=TRUE, ROWS=0, SKIP_INDEX_MAINTENANCE=TRUE )
UNRECOVERABLE
LOAD DATA
INFILE '/data/orders_2024.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=dw_load/pw control=fast_orders.ctl log=orders_load.log

# After load completes
sqlplus dw_load/pw <<EOF
ALTER INDEX sales.orders_pk        REBUILD NOLOGGING;
ALTER INDEX sales.orders_date_ix   REBUILD NOLOGGING;
ALTER INDEX sales.orders_status_ix REBUILD NOLOGGING;
EOF

Best practices

• Always specify SKIP_INDEX_MAINTENANCE=TRUE when loading large volumes — rebuilding once is far cheaper than per-key merging
• Use UNRECOVERABLE only when you will take a backup immediately after; document this in runbooks
• Keep PARALLEL=TRUE sessions loading disjoint data ranges to avoid hot-block contention at the HWM
• Set ROWS=0 for direct path — commits are irrelevant since data is written block-by-block; the load is atomic per segment extent
• After a failed direct path load the table's HWM may advance even though no data is visible — run ALTER TABLE … SHRINK SPACE or export/import to reclaim it