SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Temporary intermediate table

766915Apr 15 2010 — edited Apr 15 2010

In our project to generate massive reports in oracle we use some permanent table to hold intermediate results. For example to generate one report we run few queries and populate the table, at the final step we join the intermediate table with huge application tables. These intermediate tables are cleared for next report run. We have few concerns in performance areas.

1. These intermediate tables are transactional and don't have statistics. Is it good idea to join these with application tables which are partitioned and have up to date statistics.
2. We need these results stored in the intermediate tables to be available across requests from UI hence we are not in a position to use oracle provided temporary tables.

Any thoughts on what could be done would be appreciated.

Btw we use Oracle 10g