If your application is generating a tremendous amount of IO writing, and your IO subsystem just can't handle the requirements, global temporary tables (available in Oracle8i), may provide the solution. When I first heard about global temporary tables, I was so intrigued by Oracle's claim that no redo was generated that I decided to perform some experiments. I found that the redo reduction results are simply amazing and very significant.

Most applications use what I call interim tables (sometimes incorrectly referred to as temporary tables), which exist in the middle between Oracle temporary segments and data segments. Interim tables typically hold data only during batch processing operations that have limited or no recovery requirements. Doesn't it seem silly then to create, copy, buffer, and write all that redo? And if you are used to using large PL/SQL tables (or arrays), you know that a session can exceed its available heap memory limits. Global temporary tables address these questions by having specific characteristics of both temporary segments and data segments. For example, while a global temporary table contains rows and can have associated indexes, like temporary segments, the amount of redo generated is significantly less than with traditional data segments.

While developers have some amount of control over the use of interim segments, many applications, along with their massive redo generation requirements, are simply transitioned over to the DBAs, who are told to somehow just make them perform. Global temporary tables give the DBA a near-application-level tool, which can be used to possibly avoid significant and painful application changes.