Oracle Temporary Tables
Using temporary tables with Dictionary Views
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 (against a complex view) as shown here:
username NOT IN
(select grantee from dba_role_privs);
This query runs in 18 seconds. As you may remember from Chapter 12, these anti-joins can often be replaced with an outer join. However, we have another option by using CTAS. Now, we rewrite the same query to utilize temporary tables by selecting the distinct values from each table.
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× 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.
Using Temporary Tables
If the amount of data to be processed or utilized from your PL/SQL procedure is too large to fit comfortably in a PL/SQL table, use a GLOBAL TEMPORARY table rather than a normal table. A GLOBAL TEMPORARY table has a persistent definition but data is not persistent and the global temporary table generates no redo or rollback information. For example if you are processing a large number of rows, the results of which are not needed when the current session has ended, you should create the table as a temporary table instead:
The “on commit preserve rows” clause tells the SQL engine that when a transaction is committed the table should not be cleared.
The global temporary table will be created in the users temporary tablespace when the procedure populates it with data and the DIRECT_IO_COUNT will be used to govern the IO throughput (this usually defaults to 64 blocks).
Services: - Oracle Temporary Tables Homework | Oracle Temporary Tables Homework Help | Oracle Temporary Tables Homework Help Services | Live Oracle Temporary Tables Homework Help | Oracle Temporary Tables Homework Tutors | Online Oracle Temporary Tables Homework Help | Oracle Temporary Tables Tutors | Online Oracle Temporary Tables Tutors | Oracle Temporary Tables Homework Services | Oracle Temporary Tables