FAst extraCT (FACT) for Oracle Accelerates Database Unloads CoSort Products

Home » Products » CoSort Fast Extract (FACT)

Rapid Extraction of Large Oracle Tables

Fast Extract (FACT) rapidly unloads large Oracle tables in parallel to portable flat files. FACT also creates the extract's metadata for optional, simultaneous transformations and loading. FACT can be a critical component of database reorgs and migrations, data warehouse ETL operations, reporting, replication, and protection.

Summary

FACT speeds large Oracle table unloads while leveraging SQL syntax and multiple CPUs. FACT works over different connection protocols, protects login information, reformats and splits data, and writes metadata for CoSort transformation and report layouts, SQL*Loader operations, FieldShield column protections, and RowGen test data populations.

Features/Benefits

• Very fast Oracle unloads
• Accelerates reorgs, ETL
• Speeds database migrations
• Automatic metadata creation
• Feeds CoSort, RowGen jobs
• Reformats, logs performance
• Extracts CMP, LOB and LONG

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 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 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 function. Benchmarks show flat files are produced many times faster through FACT than through SQL*Plus or ODBC operations. FACT consumes only a small amount of system resources, without the need for 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

where 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). You can execute FACT from the shell, a batch script, or program.

FACT also works hand-in-hand (via metadata creation) with 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 asingle-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 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 Oracle test data

Integrated Reorg and ETL Operations

With FACT, Oracle DBAs and data warehouse architects can optionally pipe together a complete and very fast Oracle reorg or ETL process.

Execute the .ini file together with a CoSort SortCL job specification file (.scl) that references the .ddf 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, incluing 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.

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