Temporary Tables in Oracle 8i

5/14/99, Guy Bayes

Introduction

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:

• 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 method?

The material in this document was based primarily on the following sources:

1. Oracle 8i Developer's Guide:


http://technet.oracle.com/doc/server.815/a68003/01_03sch.htm#7794
 

2. Oracle Concepts Manual "Temporary Tables"


http://technet.oracle.com/doc/server.815/a67781/c08schem.htm#16097
 

3. Oracle Migration Workbench for Microsoft SQL Server Reference Guide


http://technet.oracle.com/doc/workbench/C5.htm
 

4. Data Warehousing and Materialized Views

Hyperlinks in this document link to online documentation on Oracle's Tech-Net web site at:

         http://technet.oracle.com ,

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 Tables:

Sybase temporary tables have the following desirable properties we wish to duplicate in Oracle:

1. Security: They can be created, on the fly, by any user query, without any Create Table, or other special privilege.
2. Simplicity: They are managed by the system, and are automatically dropped when the session that created them ends.
3. 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.
4. Transparency: They accomplish all these properties completely behind the scenes, without any participation by the developer.

General Strategy:

Create a package of PL/SQL procedures which will do the following:

1. Take as an argument the DDL string to create a temp table
2. Parse this string, and extract relevant information
3. Create in a temporary database the passed table name as
4. TEMP_table_name_SESSIONID_TIMESTAMP
5. Log the table and session id with the DB
6. Create an alias for the user to the new table as the originally
7. passed table name
8. When the session that created the table terminates, drop it

Pros:

• 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.

Cons:

• 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 at:

http://technet.oracle.com/doc/workbench/C5.htm#Definition of temp_table_catalog

Feasibility Testing:

The following code runs and seems to function correctly under Oracle 8.0.5.0.0 It dynamically creates tables mytable_xxxxxx with the session id appended

DECLARE

THIS_SESSION NUMBER;

TABLE_NAME varchar2(100) := 'mytable';

cid INTEGER;

BEGIN

    cid := DBMS_SQL.OPEN_CURSOR;

    THIS_SESSION := userenv ('sessionid');

    dbms_sql.parse(cid, 'CREATE TABLE '|| table_name || '_' || to_char(THIS_SESSION) || ' (salary int) ', dbms_sql.v7);

    DBMS_SQL.CLOSE_CURSOR(cid);

END;
 
 

Proposal II: Nesting in the From Clause

General Strategy:

Use Oracles extended SQL syntax to simulate temp tables by nesting in the FROM clause:

Explanation:

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:

Example:

Say we have this process:

SELECT A.*, B.name, B.id

INTO #Temp1

FROM TableA A, TableB, b

WHERE condition1

go

SELECT C.*, D.name_code

INTO #Temp2

FROM #Temp1 C, TableD D

WHERE condition2

go

SELECT E.*, F.org_code

INTO FinalTable

FROM #Temp2 E, TableF F

WHERE condition3

go

In oracle SQL, we can simulate it as:

SELECT E.*, F.org_code

INTO FinalTable

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

WHERE condition3

Pros:

• 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 tablespaces needed.
• Simplicity: No need to define any temporary tablespaces, or temporary tables at all. No extra procedures or functions needed.
• Robustness: No possibility of memory leaks due to temp tables not getting dropped correctly. No dynamic SQL needed.

Cons:

• 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:

SELECT *

INTO #TEMP1

FROM TableA

WHERE condition1

go

UPDATE #TEMP1

SET update

WHERE condition2

go

SELECT *

FROM #TEMP1

...

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.

Feasibility Testing:

The following code runs and seems to function correctly under Oracle 8.0.5.0.0

SELECT count(*)
FROM (SELECT salary FROM DATA WHERE salary <500);

Proposal III:

General strategy:

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.

Explanation:

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 this:

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.

Example:

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;

Pros:

• Simplicity: Syntax is an easy translation from Sybase No extra procedures or functions involved in utilizing this method.
• 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 to users
• 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:

• How do Global Temp Tables impact efficiency?
• How robust are Global Temp Tables?

General Strategy:

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 structure:

Explanation:

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.

Pros:

• 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, etc.
• 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

PARALLEL

BUILD IMMEDIATE

REFRESH FORCE

AS

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(+);