FAst extraCT (FACT) is a high-performance data delivery tool for Oracle DBAs requiring faster reorgs and queries, and for high-volume data warehouse extract, transform, and load architects.

CoSORT's FAst extraCT (FACT) for Oracle is the quickest way to:

  • perform bulk unloads
  • produce differently-formatted data files from a table
  • create the metadata for data warehouse transformations
    in CoSORT's SortCL and/or bulk loads via SQL*Loader.

FACT consumes only a small amount of system resources - there is no need for load balancing! FACT utilises unique and highly efficient I/O techniques, rather than usurping memory like in other offload and ETL tools.

FACT produces SortCL data definition files (.ddf) and SQL*Loader control files alongside the extraction. With FACT, Oracle users unload tables with the command: fact ini_file where ini_file is a simple-to-use text file that specifies the details of the table, its extraction method, and its target format and destination (file or pipe). Each simple FACT statement can be issued from the command line, within a batch script, or from within an application program.

FACT for Oracle enables fast extraction of table contents from an Oracle Database (currently versions 7-10) using all supported combinations of Oracle-native features, including:

  • parallel extraction of a partitioned table
  • extraction using indices
  • conditional extraction
  • high-speed fetching using hints

Business Purpose

Today's Oracle DBAs and data warehouse architects face ever-increasing storage volumes and processing bottlenecks. Strict service-level agreement (SLA) commitments and shrinking production windows require world-class database reorg and data warehouse extract, transform, and load (ETL) solutions that deliver high performance and database availability.

The leaders in the field already leverage CoSORT's popular and powerful sort control language (SortCL) program to transform these huge table volumes in a single pass, and pre-sort to load them quickly.

Such large volumes of data must be staged in flat files, as data warehouse experts like Dr. Ralph Kimball recommend. The fastest sort, join, convert, aggregation, report, and reload processing occurs in flat files. Data stored in proprietary formats and structured DBMS/ETL systems are optimized for queries, not for transformations or loads.

Because of these realities and the importance of speed, high-volume Oracle users can now turn to another high-performance CoSORT solution -- called FACT -- to rapidly unload huge, and specific table data into portable flat-file formats.

FACT also works hand-in-hand (via metadata creation) with CoSORT's SortCL (transform) program and Oracle's SQL*Loader (load) to consolidate the entire database reorg (and data warehouse ETL) process into a single pass operation!

Integrated ETL Operations

With FACT, data warehouse architects can optionally pipe together a complete and incredibly fast Oracle reorg or ETL processes by combining the .ini with a CoSORT SortCL job specification file (.scl) that references the .ddf created by FACT, and with a SQL*Loader process that uses the control file metadata. Consider this command template:

fact ini_file | sortcl /spec=transform.scl /spec=fact.ddf | sqlldr control=fact.ctl direct=true
This command-line statement initiates a rapid extraction and pipes it to CoSORT's sort control language (SortCL) program for simultaneous filtering, sorting, and reformatting (transform.scl). In turn, the CoSORT/SortCL output is piped in index order to SQL*Loader. No other ETL solution approaches this level of parallel processing performance, versatility, or ease of use.

Platform Availability

FACT is currently available for AIX, HP-UX (PA-RISC and Itanium), Linux (x86 and IBM zSeries), Solaris (SPARC only), and Tru64 UNIX. 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.

For further details please visit http://www.easirun.com.au