Home » SQL & PL/SQL » SQL & PL/SQL » oracle temp tables or normal tables (oracle 10g)
oracle temp tables or normal tables [message #394881] Mon, 30 March 2009 12:39 Go to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
Hi

I have to create a table tab_new from tab holding 1 month data from tab and status.

Now tab is used in an application in various ways.

inserts.updates,deletes and parallel hints used in it as well.

There are two ways in which i can do that.

I can create a table as below

create table tab_new as 
(Select * from tab where ID in 
(SELECT ID 
  FROM status 
  where DATE > ADD_MONTHS(SYSDATE,-1))) ; 


or i create a new table as tab_new

and then do a

insert into tab_nsw
as
select * from ****


Can you tell me are both the above process the same.Will the temprary table perform the same way as a normal table.

Kindly let me know.

Thanks.

Re: oracle temp tables or normal tables [message #394882 is a reply to message #394881] Mon, 30 March 2009 12:44 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Are you using "tab" and "tab_new" as an example, because TAB is a view in SYS user.

And I didn't quite understand why you asked if the temporary table performs the same way. From what you showed in this posting, it is not a temporary table.
Re: oracle temp tables or normal tables [message #394968 is a reply to message #394882] Mon, 30 March 2009 23:08 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
no by tab i meant any table xyz and tab_new as xyz_new.

nothing to do with the oracle defined tab.

thanks
Re: oracle temp tables or normal tables [message #394969 is a reply to message #394881] Mon, 30 March 2009 23:16 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
>Will the temprary table perform the same way as a normal table.
Which is the temporary table?
What did you own benchmark test show?
Re: oracle temp tables or normal tables [message #394970 is a reply to message #394969] Mon, 30 March 2009 23:18 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
didnt get your last qn.

xyz_new is the new temp table created from xyz.



Re: oracle temp tables or normal tables [message #394985 is a reply to message #394970] Mon, 30 March 2009 23:43 Go to previous messageGo to next message
ora_baby
Messages: 89
Registered: May 2008
Member
http://www.dba-oracle.com/t_temporary_tables_sql.htm
http://www.java2s.com/Code/Oracle/Table/Temporary-Table.htm
Re: oracle temp tables or normal tables [message #395051 is a reply to message #394970] Tue, 31 March 2009 03:31 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What you have created is a perfectly ordinary table.

A temporary table is a special type of Oracle table that stores data only for a transaction or a session, and generates no Redo information.

Your perfectly ordinary table will behave just the same as all the other perfectly ordinary tables in your schema.
Previous Topic: Execute .lib at in Oracle
Next Topic: Mail with partially text and partially html
Goto Forum:
  


Current Time: Sat Dec 03 14:20:46 CST 2016

Total time taken to generate the page: 0.17240 seconds