Reports

[krishnaindia2007]

While preparing reports I am creating a temporary table and then taking data from that temporary table. Does it have any effect on performance if we take data directly from the base table for generating reports?

[susarlasireesha]

One of the shortcomings of relational databases is their inability to create intermediate result sets. One technique that is used within all relational databases is the creation of interim (temporary) tables to improve the speed of their SQL queries.

Temporary tables are generally used as a mechanism to pre-join tables, or pre-aggregate intermediate data. As such, the use of temporary tables is more effective in highly normalized databases. A data warehouse with lot’s of denormalization may not benefit from queries that use temporary tables.

The prudent use of temporary tables can dramatically improve Oracle SQL performance. To illustrate the concept, consider the following example from the DBA world. In the query that follows, we want to identify all users who exist within Oracle who have not been granted a role. We could formulate the query as an anti-join with a noncorrelated subquery as shown here:

select
username
from
dba_users
where
username NOT IN
(select grantee from dba_role_privs);

On a large database, this query runs in about 18 seconds. Now, we rewrite the same query to utilize temporary tables by selecting the distinct values from each table.

drop table temp1;
drop table temp2;

create table
temp1
as
select
username
from
dba_users;

create table
temp2
as
select distinct
grantee
from
dba_role_privs;

select
username
from
temp1
where
username not in
(select grantee from temp2);

With the addition of temporary tables to hold the intermediate results, this query runs in less than three seconds, a 6-fold performance increase. Again, it is not easy to quantify the reason for this speed increase, since the DBA views do not map directly to Oracle tables, but it is clear that temporary table show promise for improving the execution speed of certain types of Oracle SQL queries.

Temporary tables are also useful in cases where we need to compare two ranges of dates in a single table.