Temporary Tables in Oracle 8i
5/14/99, Guy Bayes
Most data base management systems, including
Sybase, Informix, and MS SQL Server, have built-in constructs for the creation
and management of temporary relation tables. Oracle 8i does not directly
support these structures. This shortcoming can seriously complicate migration
from the above systems to Oracle.
This paper is intended primarily to present,
discuss, and evaluate ways of duplicating Sybase temporary tables in Oracle
8i. Four primary methods will be examined. The value of these methods will
be judged on the following criteria:
We will evaluate these issues in the following
fashion. First, we present the qualities of a Sybase temporary table which
we attempting to duplicate. Then we will present four methods to simulate
temporary tables in Oracle8i. We outline the general strategy, then describe
the method in detail. The methods pros and cons will be evaluated using
the above criteria. Whenever possible, code examples will be provided.
Also, whenever possible, feasibility testing will have been executed using
the code examples provided, the results of these tests will also be included.
Finally, unresolved or answered research questions will be detailed.
Functionality: To what degree does
this method duplicate the functionality of Sybase temporary tables?
Simplicity: How simple is the method
to use and maintain, both from a user and developer perspective?
Transparency: How transparent is the
conversion from Sybase to oracle, to what extent does the manner in which
the method is used duplicate temporary tables in Sybase?
Efficiency: How efficient is the method,
compared to Sybase temp tables?
Robustness: How robust and reliable
is the method?
Security Issues: How secure is the
method? What types of permissions need to be granted to a user to implement
The material in this document was based
primarily on the following sources:
Oracle 8i Developer's Guide:
Oracle Concepts Manual "Temporary Tables"
Oracle Migration Workbench for Microsoft SQL
Server Reference Guide
Data Warehousing and Materialized Views
Hyperlinks in this document link to online
documentation on Oracle's Tech-Net web site at:
which requires a registered username and
password. You may obtain a username and password by selecting the "Membership"
link from the home page of the site.
Properties of Sybase Temporary
Sybase temporary tables have the following
desirable properties we wish to duplicate in Oracle:
Proposal I: Dynamically Creating
Temporary Tables in Oracle
Security: They can be created, on the
fly, by any user query, without any Create Table, or other special privilege.
Simplicity: They are managed by the
system, and are automatically dropped when the session that created them
Functionality and Robustness: They
avoid cross session naming conflict. Temp table names do not have to be
unique inside a database, only inside a session. Two concurrent sessions
in the same database can both create temporary tables with the same name
and different schemas without any conflict arising.
Transparency: They accomplish all these
properties completely behind the scenes, without any participation by the
Create a package of PL/SQL procedures which
will do the following:
This is similar to the process used to migrate
from MS SQL to Oracle. Most of the code is already available, once you
figure out what it does. This requires dynamic SQL, which is supported
by the DBMS_SQL package provided with Oracle.
Take as an argument the DDL string to create
a temp table
Parse this string, and extract relevant information
Create in a temporary database the passed
table name as
Log the table and session id with the DB
Create an alias for the user to the new table
as the originally
passed table name
When the session that created the table terminates,
Transparency: Almost completely transparent
to the user
Functionality: Should behave identically
to Sybase temp tables. Uniqueness of tables guaranteed due to uniqueness
of session id.
Security: No security issue, the only
privileges that need to be granted are to the temporary tablespace.
Simplicity: Significant amount of coding
to implement package, even with sample code. Since the table name is generated
at runtime, dynamically, the program is not domain independent. You cannot
predetermine what table name you will generate, nor can you reference it
from another process without somehow passing the session id.
Robustness and Efficiceny: There exists
the possibility of memory leaks if temp tables don't get dropped. However,
timestamping and possibly an automated once a week cleanup could alleviate
this. Dynamic SQL can generate runtime errors and is less efficient then
normal SQL. However, since the only time we would use it is to create the
table, efficiency shouldn't be an issue.
Example code for such a packet can be found
The following code runs and seems to function
correctly under Oracle 22.214.171.124.0 It dynamically creates tables mytable_xxxxxx
with the session id appended
TABLE_NAME varchar2(100) := 'mytable';
cid := DBMS_SQL.OPEN_CURSOR;
THIS_SESSION := userenv
'CREATE TABLE '|| table_name || '_' || to_char(THIS_SESSION) || ' (salary
int) ', dbms_sql.v7);
Proposal II: Nesting in
the From Clause
Use Oracles extended SQL syntax to simulate
temp tables by nesting in the FROM clause:
Oracle supports significant expansion on
the standard syntax of SQL. One of these expansions is to allow nested
subqueries in the "FROM" clause of SQL. These subqueries can be used to
simulate nested tables as follows:
Say we have this process:
SELECT A.*, B.name, B.id
FROM TableA A, TableB, b
SELECT C.*, D.name_code
FROM #Temp1 C, TableD D
SELECT E.*, F.org_code
FROM #Temp2 E, TableF F
In oracle SQL, we can simulate it as:
SELECT E.*, F.org_code
FROM Tablef F, (SELECT C.*, D.name_code
FROM TableD D, (SELECT A.*, B.name, B.id
FROM TableA A, TableB B
WHERE condition1) C
WHERE condition2) E
Efficiency: More efficient since it
eliminated unneeded file I/O. Nesting in the WHERE clause is to be avoided
as it adds time complexity, but FROM clause nestings don't.
Functionality: When applicable, should
generate identical results to the temp table query. No uniqueness issue.
Somewhat Transparent: Easy to convert
from one style to the other.
Security: No security issues, no temporary
Simplicity: No need to define any temporary
tablespaces, or temporary tables at all. No extra procedures or functions
Robustness: No possibility of memory
leaks due to temp tables not getting dropped correctly. No dynamic SQL
Transparency: Code is quite a bit different,
requiring all DML"s to be converted to this style. Not transparent to our
users, would require they rewrite their queries.
Simplicity: Code is less readable and
the process less readily apparent. Nesting quickly becomes cumbersome if
a large number of nestings required
Functionality: Some types of temp table
usage's CANNOT be simulated this way. For example, any process which creates
a temp table, modifies it, and then revisits or branches from it cannot
be easily simulated by this process:
would be difficult to simulate. You would
have to perform all the updates on the end result table. In some cases
this would not be sufficient.
The following code runs and seems to function
correctly under Oracle 126.96.36.199.0
FROM (SELECT salary FROM DATA WHERE salary
Oracle 8i supposedly supports a "Global
Temporary Table" syntax. While this is not the same as a Sybase temp table,
it may be useful for our processes.
A Global Temp Table is not the same thing
as a Sybase temp table. For all practical purposes, it is a globally defined
table with rows associated with the session id's. Thus, each session can
only see and interact with the rows it has created, and a truncate table
will only drop those rows associated with the session id that called the
truncate. Similarly, the table can only be dropped and recreated if no
active sessions have rows currently in it.
In addition, Oracle will "clean up" the
temp table, either after the transaction or session has completed, depending
on how you create it. No memory leaks,
Thus, you don't have to worry about multiple
instances of the same process using the same temp table, the sessionid
will handle that.
The main difference in functionality is
Global Temp tables with the same name must
have the exact same schema. There is only 1 table, really.
So, if we wanted to use this option, we
would have to enforce a unique temp table naming scheme. As long as all
script names are unique, we could simply prefix the temp table name with
the script name.
For script ld_ldgr_entr_gt.sh, Sybase temp
table #temp1 would have to be renamed:
CREATE GLOBAL TEMPORARY TABLE ld_ldgr_entr_gt_temp1
startdate DATE, enddate DATE, cost NUMBER)
ON COMMIT PRESERVE ROWS;
Simplicity: Syntax is an easy translation
from Sybase No extra procedures or functions involved in utilizing this
Functionality: Will generate identical
results to Sybase temp tables
Robustness: No dynamic SQL
Security: Easy security, merely need
to grant access to a temporary table space
Functionality: Serious uniqueness issue.
Would require a universal naming scheme for all temp tables. This could
be solved by prefixing all temp tables with the name of the script.
Transparency: Not completely transparent
Efficiency: May not be as efficient,
since multiple processes may use the same table. These temp tables could
quickly grow very large.
Robustness: This is a brand new 8i
feature, not available in previous versions of Oracle 8 and may be buggy.
Not done, not supported by current development
platform (Oracle 8.0.3).
Research issues unresolved:
Proposal IV: Materialized
How do Global Temp Tables impact efficiency?
How robust are Global Temp Tables?
Avoid the need for temp tables by reworking
how datagroups are created. Instead of creating denormilzied tables, use
materialized views to provide the same type of data
Up until now, we have been using datagroup
builds to physically create denormalizied data tables. We've been doing
this instead of using views because, in Sybase, views are computed dynamically,
at query time, and thus are very inefficient for large amounts of data.
Oracle supports a "materialized" view structure which precomputes and physically
stores the joins needed to support a view.
Transparency: Transparent to user
Simplicity: Process is much simpler.
Rather then writing SQL to do the joins, you just define the view
Efficiency: Process should be just
as efficient. The materialized view can be refreshed incrementally, indexed,
Functionality: Should make temp tables
unnecessary in many cases. Will also allow considerable increases in functionality
MV's can for instance, gather statistics on what kind of queries are being
executed against them and then can be further tuned to support these queries.
You can specify parallel execution, many other parameters. They provide
much better support for advanced querying, like OLAP, Data Cubes, etc.
Functionality: Process is more abstracted,
so it may be that if some functionality is not directly supplied, it might
be difficult to achieve. MV's are only a partial solution, since some processes
will still require temp tables.
Simplicity: More tuning is required
to get the MV"s to work correctly
Efficeincy: May be less efficient to
perform a view refresh then a datagroup build in the event that a full,
rather then incremental build is required.
CREATE MATERIALIZED VIEW detail_fact_mv
SELECT f.rowid "fact_rid", s.store_key,
s.store_name, f.dollar_sales, f.unit_sales, f.time_key
FROM fact f, time t, store s
WHERE f.store_key = s.store_key(+) AND
f.time_key = t.time_key(+);