Solved

Creating and Referring to Temporary Tables in Oracle 9i from MDAC

Posted on 2004-03-28
9
12,617 Views
Last Modified: 2012-08-13
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.

0
Comment
Question by:xassets
9 Comments
 
LVL 4

Expert Comment

by:barthdev
ID: 10697952
Hi xassets,

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?
0
 
LVL 6

Assisted Solution

by:musdu
musdu earned 200 total points
ID: 10697964
Hi,

1-This is right syntax.

CREATE GLOBAL TEMPORARY TABLE MyTable AS SELECT...

2-You can join it with permanent tables.

3-It is possible. (The definition of a temporary table is visible to all sessions, but the data in a temporary table is visible only to the session that inserts the data into the table.)

4-You can create a temporary table using syntax CREATE GLOBAL TEMPORARY TABLE. There's no difference.

regards.

-------------

    *   If the TRUNCATE statement is issued against a temporary table, only the session specific data is trucated. There is no affect on the data of other sessions.
    * Data in temporary tables is automatically delete at the end of the database session, even if it ends abnormally.
    * Indexes can be created on temporary tables. The content of the index and the scope of the index is that same as the database session.
    * Views can be created against temporary tables and combinations of temporary and permanent tables.
    * Tempoarary tables can have triggers associated with them.
    * Export and Import utilities can be used to transfer the table definitions, but no data rows are processed.
    * There are a number of restrictions related to temporary tables but these are version specific.


0
 
LVL 4

Accepted Solution

by:
barthdev earned 200 total points
ID: 10697980
In order flesh out your question about syntax a little further, the default action for a temporary table is to delete rows on commit. Because Oracle forces a commit after creating a table, the rows will be deleted by default.

======================
SQL> create global temporary table mytemp as
  2  select username from user_users;

Table created.

SQL> desc mytemp
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USERNAME                                  NOT NULL VARCHAR2(30)

SQL> select * from mytemp;

no rows selected

======================

However, if I use the ON COMMIT PRESERVE ROWS clause, the rows will be held until I create a new session. BUT the table definition is STILL THERE.

======================
SQL> create global temporary table mytemp on commit preserve rows as
  2  select username from user_users;

Table created.

SQL> desc mytemp
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USERNAME                                  NOT NULL VARCHAR2(30)

SQL> select * from mytemp;

USERNAME
------------------------------
BARTH

SQL> conn barth@prod
Enter password: ********
Connected.
SQL> select * from mytemp;

no rows selected

======================

Does that make sense?
0
 
LVL 4

Author Comment

by:xassets
ID: 10698028
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 ?


0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 23

Assisted Solution

by:seazodiac
seazodiac earned 100 total points
ID: 10698419
@xassets:

A. So am I right in saying that a temp table is visible to a sql statement without a prefix ?

Yes, that's true, you don't need to prefix the temp table.


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 ?


There is no such thing called normal temp table.
In oracle, temporary table is created by "CREATE GLOBAL temporary table" .

0
 
LVL 4

Expert Comment

by:barthdev
ID: 10698471
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
0
 
LVL 4

Author Comment

by:xassets
ID: 10698507
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 ?  


0
 
LVL 4

Expert Comment

by:barthdev
ID: 10698840
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
0
 
LVL 4

Author Comment

by:xassets
ID: 10698878
OK thanks for all your help
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now