The global temporary table emulation is provided to get
benefit of the Oracle GLOBAL TEMPORARY TABLES, by sharing the same
table structure with multiple SQL sessions, reducing the cost of the
CREATE TABLE statement execution. However, this emulation does not
provide the same level of Informix® compatibility
as the default emulation, and must be used carefully.
How does the global emulation work?
- Informix CREATE TEMP
TABLE and SELECT INTO TEMP statements are automatically converted
to ORACLE "CREATE GLOBAL TEMPORARY TABLE". The original table name
is kept, but it gets a "TEMPTABS" schema prefix, to share the
underlying table structure with other database users.
- The Global Temporary Tables are created with the "ON COMMIT PRESERVE
ROWS" option, to keep the rows in the table when a transaction ends.
- The Global Temporary Tables are created in a specific schema called
"TEMPTABS". If the table exists already, error ORA-00955 will
just be ignored by the database driver. This allows to do several
CREATE TEMP TABLE statements in your programs with no SQL error, to
emulate the Informix behavior.
This works fine as long as the table name is unique for a given structure
(column count and data types must match).
- Once the Global Temporary Table has been created, all other SQL
statements performed in the current SQL session are parsed to convert
the original table name to TEMPTABS.original-tablename.
- When doing a DROP TABLE temp-table statement in the program,
the database driver converts it to a DELETE statement, to remove all
data added by the current session. A next CREATE TEMP TABLE or SELECT
INTO TEMP will fail with error ORA-00955 but since this error is ignored,
it will be transparent for the program. We can't use TRUNCATE TABLE
because that would required at least DROP ANY TABLE privileges for
all users.
- When the BDL program disconnects from the database (for example,
when it ends or when a CLOSE DATABASE instruction is executed), the
tables that have not been dropped by the program with an explicit
DROP TABLE statement will be automatically cleaned by Oracle.
Prerequisites when using the global emulation
- You must create a database user (schema) dedicated to this emulation, with the name
"TEMPTABS":
CREATE USER temptabs IDENTIFIED BY pswd;
- All database users must have sufficient privileges to use Global Temporary Tables in the
TEMPTABS schema: If you want programs to create Global Temporary Table on
the fly, you must grant a CREATE ANY TABLE + CREATE ANY INDEX system privilege to all
users. But this means that all users will be able to create/drop tables in any schema
(Here Oracle (10g) is missing some fine-grained system privilege to create/drop
tables in a particular schema). You better "prepare" the database by creating the
Global Temporary Table with the TEMPTABS user (do not forget to specify ON COMMIT
PRESERVE ROWS option), and give INSERT, UPDATE, DELETE and SELECT object privileges
to PUBLIC, for example:
CREATE GLOBAL TEMPORARY TABLE temptabs.mytable
( k INT PRIMARY KEY, c CHAR(10) ) ON COMMIT PRESERVE ROWS;
CREATE UNIQUE INDEX temptabs.ix1 ON temptabs.mytable ( c );
GRANT SELECT, UPDATE, INSERT, DELETE ON temptabs.mytable TO PUBLIC;
For testing purpose, consider using a user with DBA privileges, to simplify
the configuration.
Limitations of the global emulation
- Global Temporary Tables are shared by multiple users/sessions.
In order to have the global emulation working properly
with your application, each temporary table name must be unique
for a given table structure, for all programs. You must for example
as tmp1. It is recommended to use table names as follows:
CREATE TEMP TABLE custinfo_1 (
cust_id INTEGER,
cust_name VARCHAR(50)
);
CREATE TEMP TABLE custinfo_2 (
cust_id INTEGER,
cust_name VARCHAR(50),
cust_addr VARCHAR(200)
);
CREATE TEMP TABLE custinfo_2 (
cust_id INTEGER,
cust_name VARCHAR(50),
cust_addr VARCHAR(200)
);
- Tokens matching the original table names are converted to unique
names in all SQL statements. Make sure you are not using the
temp table name for other database objects, like columns. The
following example illustrates this limitation:
CREATE TABLE tab1 ( key INTEGER, tmp1 CHAR(20) );
CREATE TEMP TABLE tmp1 ( col1 INTEGER, col2 CHAR(20) );
SELECT tmp1 FROM tab1 WHERE ...
Creating indexes on temporary tables with global emulation
- Indexes created on temporary tables get also the TEMPTABS schema
prefix.
- When executing a DROP INDEX statement on a temporary table in
a program, the database driver just ignores the statement.
SERIALs in temporary table creation with global emulation