Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2004-03-28
9
Medium Priority
?
12,650 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 800 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 800 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
LVL 23

Assisted Solution

by:seazodiac
seazodiac earned 400 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

722 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