How do I parallel direct-path load using Oracle external tables?

Question

Some years ago, Thomas Kyte said this (here):

My favorite use of external tables:

Load this really big file ASAP. In the past, setup the scripts to parallel direct path load. Coordinate the firing of said scripts. Review log files to make sure they all finished. Split up input file or use skip and load to slice the file up. In short, a pain in the butt.

Now:

create table ET parallel;
create table new_table (...) parallel
as
select * from ET;

parallel direct path load made trivial.

Except this isn't valid syntax...

How do I use Oracle's external tables to perform a "parallel direct path load"?

Answer 1

The parallel capabilities of external tables are somewhat limited. As far as I know, you need to either have several files with the same format that can be processed in parallel (see below) or a single file with a fixed-length format:

CREATE TABLE WORKING_HOURS_EXT
( employee_id       NUMBER(8), 
  project_id        VARCHAR2(20),
  start_time        VARCHAR2(25), 
  end_time         VARCHAR2(25)
) 
ORGANIZATION EXTERNAL 
( 
  TYPE ORACLE_LOADER 
  DEFAULT DIRECTORY loader_data_dir 
  ACCESS PARAMETERS 
  ( 
    records delimited by newline 
    fields terminated by ';' 
    ( employee_id, project_id, start_time, end_time
    ) 
  ) 
  LOCATION ('hours01.txt', 'hours02.txt', 'hours03.txt') 
) 
PARALLEL;

ALTER SESSION ENABLE PARALLEL DML;

MERGE INTO WORKING_HOURS a
USING WORKING_HOURS_EXT b ON (...