Innovative Routines International (IRI), Inc., The CoSort Company

Oracle ETL & Reorg Optimization + Test Data

Challenges:

You have a bottleneck in:

• Unloading or loading large tables
• Routine utility operations (reorgs)
• Complex queries or outer joins
• Data warehouse integration or staging
• Database migration or replication
• Test data population

You are most likely dealing with advanced performance diagnostics and remediation (tuning), and/or throwing more hardware at the problem.

Solutions:

IRI offers three metadata-compatible products to help:

1) Fast Extract (FACT) for Oracle speeds unloads. FACT dumps data out of Oracle in parallel into flat files you can use to stage, replicate, and report on table data. This is also an important first step in data warehouse process and reorg acceleration. And, flat files are the way to go in volume.

2) CoSort Version 9's SortCL tool works on flat files outside of Oracle to accelerate high-volume:

• transforms (sorts, joins, aggregates)
• delta reports (changed data capture)
• data protection (e.g. column encryption)
• index pre-sorts (on the longest table key)

By pre-sorting, CoSort improves the efficiency and performance of SQL*Loader operations, and thus reorgs and queries.

Run 1 and 2 together ...
FACT can work through metadata and pipes with CoSort's SortCL tool to perform fast reorg, replication, encryption, ETL, and BI operations, all in one I/O pass. In the CoSort phase, you can:

• Further filter and format the extracts
• Pre-sort on index keys for direct path loads
• Join and aggregate multiple tables (files)
• Capture and segment new (changed) data
• Replicate and migrate (convert) your data
• Generate custom detail and summary reports
• Encrypt, de-ID or mask sensitive fields

Oracle's internal SQL functions benefit, too; i.e. index creation and queries work faster on large pre-CoSorted tables. With the space and time saved by offloading transforms and accelerating SQL*Loader, you can create and maintain more tables -- in multiple query orders.

3) To rapidly populate Oracle or ETL operations with safe data, consider using IRI's high-volume test data generator, RowGen. RowGen uses your data models to automatically generate the test data for an entire database with referential integrity.