| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Oracle temporary tables?
On Mon, 27 Mar 2000 18:04:43 +0200, you wrote:
>Why waste time creating the tables every time you need them as you do with
>SQL Server and Sybase? In my opinion, Oracle's solution is better. There's
>two types of temporary tables in Oracle8i: Data is removed when the
>transaction or session ends.
>
>You would use an INSERT INTO TEMPORARY_TABLE_1 SELECT ... ; construction to
>fill data into your temporary tables.
>
>HTH.
>
>Finn
>
>David Spaisman <davedba_at_intercall.net> wrote in message
>news:38DEBB89.F959E1B2_at_intercall.net...
>> Hello:
>>
>> I just noted that Oracle 8.1.5 has provided for temporary tables. It did
>> some research and found it that the tables are permanaent but the data
>> is transient -- create global temporary table...
>>
>> In Sql Server, I have used true temporary tables (#Table_name)that are
>> deleted when the stored procedure is
>> finished running. By using temporary tables under mssql, I have been
>> able to break down complex sql code(multiple table joinss) as
>> individual table selects into a temporary table.
>>
Just to further this discussion -- why waste the time creating the tables at all. In almost every case -- a single query that does the complex joins runs faster then using temporary tables. Temporary tables are a crutch used by SQLServer to overcome issues with "large complex queries". Time after time -- a single query will beat a series of "small queries" to help the optmizer do its job.
If you really want "transient" temporary tables, use inline views that select rownum. For exmaple, lets say you joined t1, t2, t3, and t4 together. In sqlserver you did t1 to t2 into a temp table, then that with t3 and t4. In Oracle you would:
select *
from ( select t1.*, t2.*, rownum r from t1, t2 where <.....> ) a,
t3,
t4
The rownum in the inline view will make it so the inline view cannot have anything pushed into it -- it'll in effect be resolved "off to the side" and then joined in with the rest of the query.
In general tho -- forget the temporary table. I think you'll find many "large and complex" queries aren't so large and complex.
>> I don't understand how create global tables can be used in a similar
>> fashion.
>>
>> 1) Can it be?
>>
Yes they could. using the same example above. YOu have t1 and t2 -- they will be pushed into a temp table at run time. Their structure is well known -- at compile time (you are building a procedure that references t1 and t2 -- they exist when you build the procedure). So you would:
create global temporary table t1_t2 as select t1.*, t2.* from t1, t2 where 1=0;
(that builds a table that structurally looks like t1 joined with t2).
Now, create the procedure and code:
insert into t1_t2 select t1.*, t2.* from t1, t2 where .....;
instead of coding the sqlserver
select t1.*, t2.* into #tmp_table .....
>> 2) Are there any other facilities in Oracle(other than create table as
>> select ...) to accommodate temporary tables?
>>
you can
create global temporary table x ( c1 number, c2 date, .... );
and so on. create table as select is just an easy way to get a table that looks like some result set.
>> Any information you can provide will be greatly appreciated. Thanks.
>>
>> David Spaisman
>>
>>
>>
--
http://osi.oracle.com/~tkyte/
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Mar 27 2000 - 10:48:19 CST
 |
 |