CODEWITHSUNDEEP
oracle-databaseetl
ZeusNet
ZeusNet

Reputation: 720

Advantages of temporary tables in Oracle

I've tried to figure out which performance impacts the use of temporary tables has on an Oracle database. We want to use these tables in our ETL process to save temporary results. At this time we are using physical tables for this purpose and truncating this tables at the beginning of the ETL process. I know that the truncate process is very expensive and therefore I thought if it would be better to use temporary tables instead.

Have anyone of you experiences if there is a performance boost by using temporary tables in this scenario?

There were only some answers on this question regarding to the SQL Server like in this question. But I don't know if these recommendations also fit for the Oracle db.

It would be nice if anyone could list the advantages and disadvanteges of this feature and also point out in which scenarios this feature could be applicable.

Thanks in advance.

Upvotes: 1

Views: 981

Answers (1)

II ARROWS
II ARROWS

Reputation: 518

First of all: truncate is not expensive, a delete with no condition is very expensive. Second: do your temporary table have indexes? What about external keys? That could affect performance.

The temporary table works more or less like Sql Server (of course the syntax is different, like global temporary table), and both are just table. You won't get any performance gain with temporary tables against normal table, they are just the same: they have a definition on DB, can have indexes, and are logged. The only difference is that temporary table are exclusive to your session (except for global table) and that means if multiple scripts from multiple sessions refer to the same table, every one is reading/writing a different table and they cannot locking each other (in this case you could gain performance, but I think it's rarely the case).

Upvotes: 1

Related Questions