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.

6 Answers
6

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:

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:

"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.

The index would exist for all sessions (so could impact their processing and potentially cause problems for them if it is a unique index and they expect non-unique data, or even if it just slows down their inserts/updates). Also, adding/dropping an index would take out a brief lock on the table Equally you can gather stats on a temporary table, but they would be assumed to apply to all queries on that table which may or may not be appropriate to your situation.
–
Gary MyersJun 3 '09 at 2:58

1

>Gary No, the index is stored in exactly the same way as the table - separately in each session, so you can't get collisions between sessions even if the index is unique. Obviously adding/dropping indexes takes a lock on the table, but how often do you add/drop indexes? Yes, stats apply globally to temporary tables, which is why sometimes you need to use the CARDINALITY hint when querying GTTs.
–
Jeffrey KempJun 3 '09 at 7:26

Ok, Gary and Jeff, you both understand the question, but seem to have differing opinions.
–
EvilTeachJun 3 '09 at 12:31

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.

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.

I am only asking about indexes. I understand about the statistics. The temp table is likely to be used by more than once process at a time. Putting stats on the table can have a negative impact.
–
EvilTeachJun 3 '09 at 12:33

I will test the use of the cardinality hint. Thank you very much +1
–
EvilTeachJun 3 '09 at 12:34