CoSORT: Faster Extraction from Oracle

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 free the RDBMS for queries, away from resource-consuming tasks.

There are two recommendations for faster extraction; one involving unecumbered unloads using a standard SQL SELECT command in this reorg context*; the other using a new and integrated CoSORT unload product called FAst extraCT (or FACT) for Oracle.

CoSORT's FACT uses a simple configuration file to direct the unloading of one or more large Oracle tables using SQL syntax. FACT creates a portable extract from one or more tables using a conditional or unconditional SQL query, generating either a flat file or streaming flat-file data – providing the input for a high-performance sorting and transformation engine like CoSORT. As the volume of data increases, FACT extracts several times faster than the SQL SPOOL command (for example) and other available methods for writing Oracle table data to text files.

In addition to performing high-speed extraction, FACT also works hand-in-hand (via metadata creation) with CoSORT's sort control (transformation) language (SortCL) program and Oracle's SQL*Loader (load) to consolidate the entire database reorg (and data warehouse ETL) process into a single command and I/O pass! Click here more details on Oracle ETL via FACT, CoSORT and SQL*Loader.

*Again, even without a tool like CoSORT's FAst extraCT (FACT), database extracts can be accelerated by removing the qualifying clauses from your SQL SELECT statements. You can improve the performance of database unloads up to 40% by dumping the entire table with SELECT * from table, and leaving the de-duping, ordering, grouping, and so on, to one or more (much faster) CoSORT operations on the flat file.