Faster Loads, too?
1. Unload table to file using
Fast Extract (FACT) for Oracle.
2. Sort the file on the longest index field using any
3. Load Oracle using SQL*LOADER and the
4. Create indexes during the load using
the clause SORTED INDEXES in the .ctl file. Or, to create the
indexes after loading, use the CREATE INDEX command with the
Do this all in one pass! See "Integrated
Reorg and ETL Operations" on this page and our Oracle Acceleration
A fast unload step can be a critical component of database reorgs and migrations,
data warehouse ETL operations, reporting, replication, and security. IRI's Fast Extract (FACT) utility for Oracle rapidly
unloads large Oracle tables in parallel to sequential files.
FACT also creates the extract's metadata for optional (and simultaneous): data transformation, loading, protection, test data generation, and format conversion.
FACT speeds large table unloads for Oracle and other database, creating flat files using SQL syntax
and multiple CPUs. FACT uses multiple connection protocols,
protects login information, reformats and splits data, and writes
the metadata for optional jobs like CoSort (SortCL) transforms and reports, SQL*Loader, FieldShield protections, RowGen test data, and NextForm row and data type reformatting.
• Parallel unloads to portable flat files
• Accelerates reorgs, ETL, migrations, replication, archive
• Transform & Load metadata creation
• Works with CoSort, FieldShield and RowGen directly
• Reformats structured data, date formats, and COBOL
• Extracts CMP, LOB and LONG
• Works inside Eclipse Plug-In
Today's Oracle DBAs and data warehouse architects face
increasing table volumes
and processing bottlenecks. Service-level agreement
(SLA) commitments and shrinking production windows require
fast database reorg and data warehouse extract, transform,
and load (ETL) solutions that deliver high performance and
Efficient database reorgs
consist of fast unload, sort and reload operations. Similarly,
in large ETL and database migration environments, data warehouse
experts like Dr. Ralph Kimball recommend that data are staged
in flat files. That is because the fastest sort, join, convert,
aggregation, report, and reload processing occurs in the file
system. Transforming big data within the database or BI layer
is taxing and inefficient. Data stored in proprietary formats
and structured DB/ETL systems are optimized for queries, not for
transforms or loads.
IRI's Fast Extract (FACT) tool rapidly unloads
large Oracle tables to external files, using all supported
combinations of native SQL SELECT features. Unlike Oracle
export and data pump, FACT creates portable flat files. Your 'dump table to file' data is thus quickly
available for any purpose, including: reorgs, transforms, pre-load
sorting, migrations, change and summary reporting, ETL, replication,
testing, and masking.
If you also have the CoSort product's SortCL
tool, you can perform or accelerate all of these processes at once.
But you do not have to use SortCL; i.e. once the Oracle data are in flat
files, you can do anything you want with them.
FACT's extract performance is second to none. Using superior connection
protocols, parallel hints, and a variety of other proprietary techniques,
FACT's unload rate is much faster than Oracle's spool or export functions.
Benchmarks show flat files are produced many times faster through
FACT than through SQL*Plus or ODBC operations. FACT consumes
few system resources, and does not require load balancing.
In addition to speed, FACT includes several useful file layout
• custom date and timestamp formatting
• numeric data re-alignment
• fixed or variable record formatting
• delimiter and frame character choices
• column trimming (width reduction)
• packed decimal to numeric conversion
• varchar length displays
• null number casting
• line-feed character removal
To unload Oracle, run FACT from the command line:
or from the IRI Workbench GUI, an Eclipse Plug-In.
The ini_file is a simple-to-use text or XML file
that identifies the database, extracts with a SQL SELECT command,
and specifies the unload target's format and destination (pipe or
files). FACT runs from the command line shell, batch script, user program or Workbench.
FACT also works with (via metadata creation) the CoSort
SortCL (transformation and reporting) program and Oracle's SQL*Loader (bulk load utility) to consolidate the entire database reorg (or
data warehouse ETL) process into a s ingle-pass
operation, and to perform reporting, replication, conversion,
and row/column security functions (also at the same time).
To summarize, FACT for Oracle is the quickest way to simultaneously:
• perform bulk Oracle unloads in parallel
• produce formatted files from a table
• create SQL*Loader control file metadata
• create CoSort metadata for transforms
roll-ups, delta reports, and pre-load sorts
• create FieldShield metadata for column
encryption, de-identification, and masking
• create RowGen metadata for
safe test data for Oracle, etc.
• create NextForm metadata for converting
the exported data and file types
Integrated Reorg and ETL Operations
With FACT, Oracle DBAs and data warehouse architects can optionally
pipe bulk unloads to complete very fast Oracle reorg or ETL process.
Execute the FACT .ini file together with a CoSort SortCL job specification
file (.scl) that references the .ddf metadata that FACT automatically creates,
and with a SQL*Loader process that uses the control file metadata
that FACT also automatically creates.
This is this operational template:
fact -c ini_file; fact
ini_file | sortcl /spec=transform.scl
This command initiates a rapid table extraction and pipes it to the CoSort
SortCL program for simultaneous field filtering, sorting, aggregation,
conversion, protection, reformatting, replication, and/or reporting
In turn, one of SortCL's output targets (stdout.dat) can be piped, pre-sorted, in index order to SQL*Loader. Many other outputs can optionally be created during this process as well, including structured reports, CSV table replicas, XML files, web-ready reports, BI tool (cube) hand-offs, and so on.
ETL solution offers this level of parallel processing performance,
versatility, or ease of use -- while simultaneously relieving the
database of so much overhead.
FACT is currently available for AIX, HP-UX (PA-RISC and Itanium),
Linux (x86 and IBM zSeries), Solaris (SPARC only), Tru64 Unix, and
Windows 2000, 2003, XP and Vista.
CoSort and its Sort Control Language
(SortCL) program for data transformation is available on these platforms,
as well as many others where you might choose to otherwise process
the FACT-extracted data. See the CoSort product page for
the platforms that CoSort supports.
Licensing and Support
FACT license fees cover perpetual use and depend on the number of CPUs or cores you wish to license. FACT copies
can be licensed alone, or in discounted conjunction with a
CoSort license bundle.
There is currently no charge for the FACT GUI within the IRI Workbench, which also supports the design, modification, sharing, and execution of CoSort, FieldShield and other IRI software.
Annual maintenance charges for FACT,
which is an optional coverage for technical support and software
upgrades, costs 20% of the base license fee, or 15% with a
CoSort license on
the same platform.
FAQ > Oracle Acceleration
Products > FACT for DB2
Products > FACT GUI
Solutions > ETL/DB Acceleration > Oracle
Solutions > Data Transformation
Solutions > Business Intelligence
Solutions > Field Protection (Data Security)
Products > CoSort > SortCL
Products > CoSort > SortCL Metadata
Products > FieldShield (Masking)
Products > RowGen (Test Data)
Products > NextForm (Conversion)
Thank you for sending us a request for information. We will get back to you shortly.