Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: Enterprise
Expertise: Advanced
May 16, 2000

Performance Enhancements Using Temporary Tables

A temporary table has a definition or structure like that of a regular table, but the data it contains exists for only the duration of a transaction or session. Oracle8i allows you to create temporary tables, which can be bound to a session or to a transaction.

You can use temporary tables to improve performance when running an otherwise complex and expensive query. To do this, you can cache the values from a more complex query in temporary tables, then run SQL statements, such as joins, against those temporary tables. Thus, the performance of your queries can improve drastically.

You can create indexes on temporary tables. Another benefit of using temporary tables is that they do not automatically allocate a segment when they are created. Instead, segments are allocated when the first INSERT (or CREATE TABLE AS SELECT) is performed. This means that if a SELECT, UPDATE, or DELETE is performed before the first INSERT, the table appears to be empty. The data in these tables get automatically deleted at the end of the transaction, or a session depending upon the type of the temporary table declared. The syntax for creating session-specific temporary tables is:

 CREATE GLOBAL TEMPORARY TABLE . . .
		[ON COMMIT PRESERVE ROWS]
The syntax for creating transaction-specific temporary tables is:
 CREATE GLOBAL TEMPORARY TABLE . . .
		[ON COMMIT DELETE ROWS]
Jayanta Sengupta
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap