Can we have temp tables ?

Like SQLServer should we be able to create temp tables,use and delete them ?

Sure, why not!!

Halo,

When I installed oracle8i it created starter database.So,to put temp stuff do,I need to create one temp database ? I don't know very much about Oracle.Still,I am in learning stage.

Thanks.

---Radha

Temp tables are new to Oracle 8i. While I have not actually played with the Oracle version of a temp table, I did read up on their implementation quickly a few months back. From what I could tell, their implementation is a far cry from SQLServer's. In SQLServer, you can do a create table in the middle of a proc and fill it in another proc that you call, then index it, then delete it - all without dynamic SQL. Furthermore, each user has their own completely separate copy of the temp table, as the table name itself is modified as the connection identifier of the user is appended directly to the name. In Oracle, you only appear to get one table across users. This means that you can't create and drop them on the fly without affecting others. The same applies for indexes.

When using Oracle 7.x, we got around Oracle's lack of temp table by creating permanent tables. Each of these tables had the session id as the first column of the PK so each user's data could be easily distinguished. This gave us the functionality we absolutely required, but with absolutely none of the 'niceties' that SQLServer provides. What I remember from reading the 8.x docs, Oracle's implementation of 'temp' tables is very similar to this. You must still create a permanent table, but by designating it as 'temporary', they appear to add the session id 'behind the scenes' to the table and every query and DML against the table. This is an extremely limiting implementation, IMHO.

If I am reading this completely wrong or remembering it wrong, I would love to be corrected by the more enlightened members of the forums here. I really, really really miss that functionality from SQLServer. It was extrememly powerful and flexible.

- Chris

All,

We are looking at HP, Sun, and IBM as possible vendors for our data warehouse platform as DG is marching slowly towards the sunset.

I'd like to hear from those of you out there running a warehouse and see what platform and config you're running on and what the size of your warehouse is. We are currently running on an 8-way DG box w/ 4GB of memory and about a 500GB database. What do you like/dislike about the platform? Basically any information and advice which will help in the selection process.

Thanks!

Joe

I'd prefer Sun over others. It's a very stable platform. I've also heard of AIX being used for data warehouse apps.

In my previous company, we ran a 1TB datawarehouse on a Sun E5500 with 6x400 Mhz CPU's and 6GB of RAM. Performance was very acceptable.

In response to SQLserver temporary tables:

Why do not try to use Oracle collections (tables, binary indexed tables and all OO stuff) if one needs really dinamic structures like to create/drop/alter temporary table.

Oracle has TABLE operator to select from collections, so kind of SQL can be used to manipulate them, liketemp tables - anyone knows about performance of this feature?

I use "GLOBAL TEMPORARY TABLE " in 8i. No problem.

LND

Could you provide some pointers for the Oracle Collections ?

What is GLOBAL TEMPORARY TABLE ? IS it a reserved word in the Oracle ?

Thanks.

The script for creatiung temporary table is:

CREATE GLOBAL TEMPORARY TABLE EMP_TMP
( EMPNO NUMBER,
EMPNAME VARCHAR2(50))
ON COMMIT DELETE ROWS;

A session / transaction can insert data into this temp table. Once the transaction ends, the rows are no longer available for SELECT. Note ON COMMIT DELETE ROWS clause.
The other benefit is:
The inserted rows by one session/transaction can not be availble for another session.
You do not need to commit after inserting rows into TEMP table.