AIG Sun America
AIG SunAmerica, a member of American International Group, Inc., is a financial services company specializing in retirement savings and investment products and services. In 2001, SunAmerica commenced in a project to replace the company's legacy system with an Oracle, web-based solution. One of the major tasks in the project was to reengineer the 100+ interfaces currently being produced by the legacy system. Hummingbird's ETL tool called Genio was used to develop the interfaces for the new system.
Athena Group was asked to perform a POC to determine if performance and scalability could be improved if they were to be written in PL/SQL. The POC proved the power of Oracle's processing power as the extraction time went from 14 hours down to 5 minutes. However, the process of rewriting the interfaces was far more complicated than simply taking the SQL code from Genio and placing it in PL/SQL.
Athena Group took thousands of lines of Genio code and re-engineering them into PL/SQL. The main goals/improvements were as follows:
- Eliminate the process of moving data from Oracle to an ETL engine for processing, thus keeping all of the extraction and transformation processing within the Oracle engine. This provided SunAmerica with a more scalable and reliable ETL solution.
- Eliminate/Reduce Control structures (i.e. loops) as much as possible. This reduced the processing time considerably when working with large amounts of data.
- Write most of the extraction and transformation in SQL, which increases complexity but vastly increases performance.
- Provide SQL hints to compensate for changes in execution plans tend to change when moving from one environment to the next. This provides stable and accurate levels of performance in all environments regardless of outside influencing factors.