FAst extraCT (FACT) for Oracle, Unload-Only
Comparison Benchmarks with SQL*Plus SPOOL

SELECT * to flat file, 64-bit, HP-UX B.11.11, Oracle 9.2, 50-byte VARCHAR.

# of Rows	SPOOL	FACT
20,000,000	25m 18s	4m 12s
50,000,000	1h 03m 31s	8m 37s
100,000,000	2h 07 m09s	17m 09s

Hardware: HP9000 L2000-44, 4 PA 8500 CPUs @ 440MHz, 8GB RAM

---

Oracle 10g Enterprise Edition Rlease 10.1.0.2.0. 64-bit prod. w/partitioning, etc.

3,000,000 Rows, 6 columns
NUMBER, NUMBER(10,3), CHAR(10), VARCHAR2(10), DATE, TIMESTAMP

Platform / CPUs	SPOOL	FACT
HP-UX rx1620 / 2	7m 00s	42s
SunFire V210 / 2	7m 17s	1m 07s
Linux Xeon2.4 /4	4m 46s	1m 29s

The FACT option "OUTFORMAT" was set to "FIXED" to produce the same flat
(SAM) output file as Oracle's SPOOL prodcued. If OUTFORMAT were set to
"VARIABLE,", the size of the SAM file would be reduced and the performance
would be better. In these tests, FACT and SPOOL both produced a 288MB file.

The following SQL script was used to extract data via SPOOL:

set timing on
set heading off
set feedback off
set termout off
set trimspool on
set linesize 10000
set pagesize 0
set space 1
set echo off
set verify off
set numwidth 21
set colsep '|'
alter session set nls_date_format='YYYYMMDD';
alter session set nls_timestamp_format='YYYYMMDDHH24MISSFF6';

spool spool_table.sam
select * from tinsert;
spool off

These Unix commands were used to test both utilities.
$ time sqlplus id/pw@inst < spool.sql > /dev/null
$ time ./fact test.ini

---

Extract-Transform-Load Benchmark

fact | sortcl | sqlldr 00h:18m:00s

vs.

Oracle insert into 01h:38m:58s
(select * ... order by)

Software Versions: FACT v1.12, CoSort v8.1, and Oracle 9i SQL*Plus
Source Data: ~ 50 million, 50-byte rows (2.32 GB) sorted on 1 key
Test Hardware: ia64 hp server rx5670, 2 x1GhZ CPUs, 32GB RAM, HP-UX 11.23