Fast Extract (FACT) for Oracle Speeds Database Unloads Fast Extract (FACT)

Home » Products » Fast Extract (FACT)

Rapid Extraction of Large Oracle Tables

» Products

Fast Extract (Oracle)

Brochure

Benchmarks

White Paper

Need 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 CoSort interface.

3. Load Oracle using SQL*LOADER and the argument DIRECT=TRUE.

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 NOSORT option.

Do this all in one pass! See "Integrated Reorg and ETL Operations" on this page and our Oracle Acceleration FAQ.

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.

Summary

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.

Features/Benefits

• 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

Introduction

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 database availability.

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.

Description

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 options, including:

• 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:

fact ini_file

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 generating
   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 /spec=fact.ddf |
sqlldr control=fact.ctl direct=true

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 (transform.scl). 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.

No other ETL solution offers this level of parallel processing performance, versatility, or ease of use -- while simultaneously relieving the database of so much overhead.

Platform Availability

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.

IRI, The CoSort Company