CoSORT: Speeds Oracle and Flat-File Queries

Very large database (VLDB) administrators know that they can improve Oracle performance by sorting data externally via CoSORT on offloaded table data. The Oracle utility operations that benefit most from this external (reorg) approach are unloads, transforms (sorts, aggregations, etc.), and reloads. Oracle's internal SQL functions benefit, too; i.e. index creation and queries work faster on pre-sorted data. And, with the storage space and time saved by offloading these jobs (especially large sorts), DBAs can afford to create and maintain more tables – in a variety of orders – which speeds queries.

CoSORT can not only improve Oracle query performance, but it can also replicate SQL query functions on flat files outside the database. CoSORT's sort control language (SortCL) program features a variety of familiar data manipulation capabilities akin to SELECT WHERE, DISTINCT, ORDER BY, GROUP BY, JOIN, etc. For example, CoSORT's SortCL uses conditional /INCLUDE and /OMIT statements to filter both sequential input sources and output targets. DISTINCT is similar to /NODUPLICATES, ORDER BY is a /KEY, GROUP BY may be a /SUM, /AVERAGE, and so on.

In the following SQL example, the Student ID, name, age, and grade point average columns from a table of Students are returned, after being ordered in descending GPA order - and secondarily by Name (where GPAs are equal):

SELECT DISTINCT sid, name, age, GPA FROM Students ORDER BY GPA Desc, Name

An equivalent query of the extracted table in CoSORT's SortCL program might read:

/INFILE=Students /SPEC=Students.ddf # field medatata reference /SORT /KEY=(GPA, Descending) /KEY=(Name, Ascending)

See also: SQL vs. SortCL script examples for aggregates and joins.

While SELECT queries are faster on ordered tables, they are faster still in the CoSORTed flat file context. In addition, SortCL puts many more transformation and presentation functions at your disposal, so that you can (simultaneously) produce multiple targets (reports) in multiple formats.