Home » RDBMS Server » Performance Tuning » Index for temporary table
Index for temporary table [message #125352] Fri, 24 June 2005 11:48 Go to next message
liux99
Messages: 29
Registered: June 2005
Junior Member
I got a temporary table A.
The java program will parse a text file and stored data into A first, then will call a stored procedure which will match the data in A with another table B. To speed up the stored procedure, the fields of A appeared in the where clause are indexed.

My questions:

For tables designed for frequent insert and delete, does the index really help? What happens to the index when you do a insert or delete? Does Oracle do a reorg internally?

Thanks
Re: Index for temporary table [message #125361 is a reply to message #125352] Fri, 24 June 2005 12:53 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
The purpose of indexes is to speed up queries. Oracle maintains indexes for you, when changes are made such as inserts and deletes, but there is definitely overhead in doing so.
Re: Index for temporary table [message #125389 is a reply to message #125352] Fri, 24 June 2005 16:22 Go to previous messageGo to next message
srinivas4u2
Messages: 66
Registered: June 2005
Member
Hi,
For DMl intensive tables don't create indexes, As the same operation needs to be carried out in the Index. So, try to create indexes on columns involved in select statements.

Hope it helps!
Re: Index for temporary table [message #125393 is a reply to message #125389] Fri, 24 June 2005 16:31 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
As you have inserts (and implicit deletes when you commit or whatever) and selects, you need to benchmark it. If it's a single process inserting at a time, you may find it quickest (least expensive with machine resources) to drop indexes on the temp table (global temporary table I assume), load the data and then build the index.
Previous Topic: Archival of Production Database
Next Topic: Logical database design
Goto Forum:
  


Current Time: Tue Sep 29 10:28:05 CDT 2020