xassets
asked on
Creating and Referring to Temporary Tables in Oracle 9i from MDAC
Whats the correct way of creating and referring to temporary tables in oracle
1. I would like to use
Create Temporary Table TableName As select...
is that the right syntax ?
2. and then refer to it in a query which joins to permanent tables...
select a.field1, b.field2
from table1 a inner join temporary.table2 b on a.field1 = b.field1
3. If I want to refer to a temp table from another connection is that possible ?
4. Whats the difference between a global temp table and a normal one ?
Whats the right syntax and best practice ? We wouldn't know in advance what the users temporary tablespace is called.
1. I would like to use
Create Temporary Table TableName As select...
is that the right syntax ?
2. and then refer to it in a query which joins to permanent tables...
select a.field1, b.field2
from table1 a inner join temporary.table2 b on a.field1 = b.field1
3. If I want to refer to a temp table from another connection is that possible ?
4. Whats the difference between a global temp table and a normal one ?
Whats the right syntax and best practice ? We wouldn't know in advance what the users temporary tablespace is called.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
OK we're nearly there. I'll share points. I read the oracle article and its as I expected.
Why create temporary tables? The queries are (far) too complex for oracle and performance goes right down the drain, so we create temp tables to speed up the query times. Also we need to left join in summarised data from child tables, its too complex and difficult in one query. There's no question that temporary tables is the right solution. A query may draw data from 30 to 50 tables, all fully normalised with int PK's/FK's.
At the moment this app generates a sequential unique table name and creates the "temp" table in the default tablespace. Thats not good because each user has to have create table permissions (very bad).
My report writer uses a different user to build reports, but that software is being replaced soon so no problem if a table only lasts for the duration of a session. The report writer is only a small part of this app.
A. So am I right in saying that a temp table is visible to a sql statement without a prefix ?
e.g
create temporary table temptable2 as
select field1, field2, sum(amt) as amt2 from table3 where field4='ASD' group by field1, field2
select a.field1, b.field2, b.amt2
from publictable1 a inner join temptable2 b on a.field1 = b.field1
B. Am I right in saying that a global temp table is the same as a normal temp table ? or is it that a global exists for the duration of a session and a non-global temp table exists for the duration of a transaction ? If they're the same, why specify global ?
Why create temporary tables? The queries are (far) too complex for oracle and performance goes right down the drain, so we create temp tables to speed up the query times. Also we need to left join in summarised data from child tables, its too complex and difficult in one query. There's no question that temporary tables is the right solution. A query may draw data from 30 to 50 tables, all fully normalised with int PK's/FK's.
At the moment this app generates a sequential unique table name and creates the "temp" table in the default tablespace. Thats not good because each user has to have create table permissions (very bad).
My report writer uses a different user to build reports, but that software is being replaced soon so no problem if a table only lasts for the duration of a session. The report writer is only a small part of this app.
A. So am I right in saying that a temp table is visible to a sql statement without a prefix ?
e.g
create temporary table temptable2 as
select field1, field2, sum(amt) as amt2 from table3 where field4='ASD' group by field1, field2
select a.field1, b.field2, b.amt2
from publictable1 a inner join temptable2 b on a.field1 = b.field1
B. Am I right in saying that a global temp table is the same as a normal temp table ? or is it that a global exists for the duration of a session and a non-global temp table exists for the duration of a transaction ? If they're the same, why specify global ?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi xassets,
I fully understand and *agree with* the decision to use temporary tables. And I see that you have many queries that need temporary tables. Is it possible to create a *generic* temporary table or several temporary tables that may be reused instead of creating them on-the-fly?
It is the ON COMMIT PRESERVE ROWS clause that differentiates between transaction-specific or session-specific for temporary tables.
The temporary table definition will continue to exist until it is dropped.
SQL> drop table mytemp;
And this is important to understand, session-specific temporary tables cannot be dropped within the session that they are created.
Barth
I fully understand and *agree with* the decision to use temporary tables. And I see that you have many queries that need temporary tables. Is it possible to create a *generic* temporary table or several temporary tables that may be reused instead of creating them on-the-fly?
It is the ON COMMIT PRESERVE ROWS clause that differentiates between transaction-specific or session-specific for temporary tables.
The temporary table definition will continue to exist until it is dropped.
SQL> drop table mytemp;
And this is important to understand, session-specific temporary tables cannot be dropped within the session that they are created.
Barth
ASKER
thanks Barth
The software creates them on the fly right now, and it drops them as soon as the final query is run.
Do you think there's a big performance overhead in creating and dropping temp tables ?
The software creates them on the fly right now, and it drops them as soon as the final query is run.
Do you think there's a big performance overhead in creating and dropping temp tables ?
Hi xassets,
I don't have a ready measurement to indicate the difference between creating and dropping temp tables or just inserting rows into an existing temp table. But all of my developer training and experience tells me that creating and dropping physical objects in any database is expensive.
You could probably get something like that using the SQL trace facility.
In all actuality, temp tables are created in the same manner as normal tables except that they don't preserve data across transactions/sessions.
Regards,
Barth
I don't have a ready measurement to indicate the difference between creating and dropping temp tables or just inserting rows into an existing temp table. But all of my developer training and experience tells me that creating and dropping physical objects in any database is expensive.
You could probably get something like that using the SQL trace facility.
In all actuality, temp tables are created in the same manner as normal tables except that they don't preserve data across transactions/sessions.
Regards,
Barth
ASKER
OK thanks for all your help
I think that you may misunderstand the *temporary* nature of TEMPORARY tables. Standard practice would involve creating one for your application to reuse unless the definition must change. It is called temporary because the data is transaction-specific or session-specific.
Let me answer your first question with two more questions. Why do you need to create a temporary table with your application? Is it possible to insert data into a temporary table that already exists on the database?
You cannot refer to a temp table from another connection because the data in a temporary table is visible only to the session that inserts the data into the table.
May I recommend that you read an excerpt from the "Oracle9i Database Concepts" guide to better understand temporary tables.
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/c11schem.htm#16097
You also reference temporary tablespaces which have no relationship to temporary tables in the sense that you need to know a user's setting.
How may I clarify further?