Is it safe to put an index on an Oracle Temporary Table?

Question

I have read that one should not analyze a temp table, as it screws up the table statistics for others. What about an index? If I put an index on the table for the duration of my program, can other programs using the table be affected by that index?

Does an index affect my process, and all other processes using the table? or Does it affect my process alone?

None of the responses have been authoritative, so I am offering said bribe.

Answer 1

Does an index effect my process, and all other processes using the table? or Does it effect my process alone?

I'm assuming we are talking of GLOBAL TEMPORARY tables.

Think of a temporary table as of multiple tables that are created and dropped by each process on the fly from a template stored in the system dictionary.

In Oracle, DML of a temporary table affects all processes, while data contained in the table will affect only one process that uses them.

Data in a temporary table is visible only inside the session scope. It uses TEMPORARY TABLESPACE to store both data and possible indexes.

DML for a temporary table (i. e. its layout, including column names and indexes) is visible to everybody with sufficient privileges.

This means that existence of the index will affect your process as well as other processes using the table in sense that any process that modifies data in the temporary table will also have to modify the index.

Data contained in the table (and in the index too), on the contrary, will affect only the process that created them, and will not even be visible to other processes.

IF you want one process to use the index and another one not to use it, do the following:

• Create two temporary tables with same column layout
• Index on one of them
• Use indexed or non-indexed table depending on the process

Answer 2

I assume you're referring to true Oracle temporary tables and not just a regular table created temporarily and then dropped. Yes, it is safe to create indexes on the temp tables and they will be used according to the same rules as a regular tables and indexes.

[Edit] I see you've refined your question, and here's a somewhat refined answer:

From:

Oracle® Database Administrator's Guide
10g Release 2 (10.2)
Part Number B14231-02

"Indexes can be created on temporary tables. They are also temporary and the data in the index has the same session or transaction scope as the data in the underlying table."

If you need the index for efficient processing during the scope of the transaction then I would imagine you'll have to explicitly hint it in the query because the statistics will show no rows for the table.

Answer 3

You're asking about two different things, indexes and statistics. For indexes, yes, you can create indexes on the temp tables, they will be maintained as per usual.

For statistics, I recommend that you explicitly set the stats of the table to represent the average size of the table when queried. If you just let oracle gather stats by itself, the stats process isn't going to find anything in the tables (since by definition, the data in the table is local to your transaction), so it will return inaccurate results.

e.g. you can do:

exec dbms_stats.set_table_stats(user, 'my_temp_table', numrows=>10, numblks=>4)

Another tip is that if the size of the temporary table varies greatly, and within your transaction, you know how many rows are in the temp table, you can help out the optimizer by giving it that information. I find this helps out a lot if you are joining from the temp table to regular tables.

e.g., if you know the temp table has about 100 rows in it, you can:

SELECT /*+ CARDINALITY(my_temp_table 100) */ * FROM my_temp_table