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

Home » Products » CoSort Fast Extract (FACT)

FAst extraCT (FACT) for Oracle

CoSort's Fast Extract (FACT) tool rapidly unloads large Oracle tables in parallel to portable flat files. FACT also creates the extract's metadata for simultaneous transformations and loading, making it 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 the metadata for CoSort transformation and report layouts, SQL*Loader operations, 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

The CoSort Fast Extract (FACT) tool rapidly unloads large Oracle tables to external files, using all supported combinations of native SQL SELECT features. The data is 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 CoSort'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. And unlike Oracle export and data pump, FACT creates portable flat files. 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 use FACT to unload Oracle from the command line, enter:

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 CoSort's 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 single-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
• produce differently-formatted data files from a table
• create CoSort metadata for transforms, reports, pre-sorts
• create metadata for bulk loads using SQL*Loader
• create RowGen metadata for generating Oracle test data

Integrated Reorg and ETL Operations

With FACT, 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 CoSort's SortCL program for simultaneous field filtering, sorting, aggregation, conversion, protection, reformatting, replication, and/or reporting (transform.scl). In turn, the SortCL output is piped, pre-sorted, in index order to SQL*Loader. 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 description for a complete list of CoSort-supported platforms.

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.

CoSort (IRI, Inc.)