Oracle - To Global Temp or NOT to Global Temp

Question

So let's say I have a few million records to pull from in order to generate some reports, and instead of running my reports of the live table, I create a temp where I can then create my indexes and use it for further data extraction.

I know cached tables tend to be quicker / faster seeing as the data is stored in memory, but I'm curious to know if there are instances where using a physical temp table is better than Global Temporary Tables and why? What kind of scenario would one be better than the other when dealing with larger volumes of data?

Answer 1

For reporting, temporary tables are helpful in that data can only be seen by the session that created it, meaning that you shouldn't have to worry about any concurrency issues.

With a non-temporary table you need to add a session handle/identifier to the table in order to distinguish between sessions.

Answer 2

I use Subquery Factoring before I consider temp tables. If there's a need for reuse in various functions or procedures, I turn it into a view (which can turn into a materialized view depending on the data returned).

According to asktom:

...temp table and global temp table are synonymous in Oracle.

Answer 3

Global Temporary Tables in Oracle are not like temporary tables in SQL Server. They are not cached in memory, they are written to the temporary tablespace.

If you are handling a large amount of data and retaining it for a reasonable amount of time - which seems likely as you want to build additional indexes - I think you should use a regular table. This is even more the case if your scenario has a single session, perhaps a background job, working with the data.

Answer 4

The primary difference between ordinary (heap) tables and global temp tables in Oracle is their visibility and volatility:

Once rows are committed to an ordinary table they are visible to other sessions and are retained until deleted.

Rows in a global temp table are never visible to other sessions, and are not retained after the session ends.

So the choice should primarily be down to what your application design needs, rather than just about performance (not to say performance isn't important).

Answer 5

The contents of an Oracle temporary table are only visible within the session that created the data and will disappear when the session ends. So you will have to copy the data for every report.

Is this report you are doing a one time operation or will the report be run periodically? Copying large quantities of data just to run a report does not seem a good solution to me. Why not run the report on the original data?

If you can't use the original tables you may be able to create a meterialized view so the latest data is available when you need it.