peformance efficiency in global temporary tables vs. normal tables - oracle 10g.

Question

I am using large number of global temporary tables for generating huge reports against an Oracle 10g database. Each report consists of 4 to 5 global temporary tables(GTT) per say. But as far as I understand the concept of GTT's, the data is created on the fly per each session for different set of parameters.

For example, in my scenario, 20 users generates the report for, say last month of sales data and it can lead to upto 1000 executions on total per day. But if we assume that the user queries most recent sales data more frequently, than how can we use some cache memory to store for a range of sales data that is queried more frequently like a internet browser does??. Also any other suggestions for fine tuning the GTT's would be very helpful.

Answer 1

It sounds like you are over-using GTTs. They are not normally needed very often in Oracle queries - in contrast to SQL Server where (I have read) it is more common and appropriate to use temporary tables. Without knowing your requirements in detail it is hard to recommend an approach, but materialized views are one way of "caching" query results once and using them many times.