CoSORT/Innovative Routines International (IRI), Inc. announced a high-performance data extraction solution for the Oracle RDBMS (v7-9.2) called CoSORT's FAst extraCT (FACT) 1.1.

FACT 1.1 for Oracle uses parallel unload technology to produce portable flat files and the optional meta data for CoSORT sort control language (SortCL) transformations and Oracle (SQL*Loader) reloads. In addition, FACT extracts, SortCL transformations and Oracle loads can all be combined in a single command-line ETL operation.

"FACT and CoSORT make use of sequential file streams (flat records) for high-volume data warehouse staging," according to IRI Business Development VP David Friedland. "FACT is a breakthrough for pulling table data into flat files – not only for Oracle users, but for CoSORT users who relied on slower SQL SELECT statements or pricier third-party extraction tools."

Data warehousing experts like Dr. Ralph Kimball support using sequential (flat) files to improve high-volume data warehouse performance. Kimball has concluded that "most data staging activities are sequential" and recommended that data staging be done in "flat files before loading it into a relational database." Moreover, according to Executive Information Systems' CEO and Chief Scientist Dr. Joseph Firestone, "since almost all processing in data staging is sorting and merging, it wastes time and resources to convert to another format [for] transformation."

CoSORT's flat-file staging program, SortCL, is also far faster than SQL ORDER BY and JOIN commands and per Oracle's Server Utilities Guide, "pre-sorting improves the performance of direct path loads and queries and minimizes the temporary storage requirements during the load." Oracle users speed their SQL*Loader operations up to 90 percent (10X) by pre-CoSORTing flat file(s) on their longest index (primary key) field for the incoming table.

FACT runs on the command line with an 'ini' file that defines qualified or unqualified table extractions. The ini file specifies the table and flat-file output format and names optional destinations for SortCL data definition file (.ddf) and SQL*Loader Control File (.ctl) meta data. SortCL meta data lays out the format(s) of one (or more) input/source and output/target file(s). SortCL meta data can be used in SortCL job specification files (.scl) for simultaneous data transformations, including: conditional selection and filtering, sort/merge, match/join, data-type conversion, aggregation, cross-calculation, sequencing and/or report (re)formatting. SortCL specifications use explicit, SQL-familiar syntax and get run from the command line, batch scripts, cross-platform Java GUI, or application programming interface (API) call. The SQL*Loader meta data similarly facilitates pre-CoSORTed, flat-file bulk reloads into Oracle tables.

FACT can be licensed independently of CoSORT for pure extraction requirements. FACT's low 4-5 figure perpetual use license fees are based on hardware sizing and quantity, not Oracle usage or users.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access