In our project, to create massive reports in oracle, we use some constant table for intermediate results. For example, to generate one report, we run several queries and fill out the table; in the last step, we join the intermediate table with huge application tables. These staging tables are cleared for the next run of the report. We have few performance issues.
These staging tables are transactional and have no statistics. It is a good idea to join them with application tables that are separated and have up-to-date statistics.
We need these results stored in the staging tables to be available on request from the user interface, so we cannot use oracle temporary tables.
Any thoughts on what can be done will be appreciated.
source
share