Archives of the TeradataForum

Message Posted: Wed, 12 May 2004 @ 19:47:59 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

<-- Anonymously Posted: Wednesday, May 12, 2004 15:37 -->

Global temp tables are useful for:

1. creating temporary data that you may use more than once over the life of your session

2. data that is re-created on a regular basis within some procedure you use

3. simplifying SQL in a query (For example you may wish to use a global temp table to hide a messy looking join that might otherwise be included in your "main" SQL to populate a derived table).

Other stuff:

1. The data dictionary is aware of their existence

2. They are not materialized until you populate them.

3. They use temporary space rather than perm space.

4. If none of the above things matter to you, a derived table will probably suit you better

5. From what I've read it looks as though you can collect stats on them, but I haven't had any success doing that. I get an error message 5343 saying I can't collect stats on a materialized global temp table, but stats collected on an empty table are useless at best and misleading to the optimizer at worst.

6. One helpful thing you can do for yourself is use a naming convention so that you will know that a table is a global temp table.

7. I don't really know how well they join with other data, due to the stats issue I mentioned

	<Prev	Next>		<<First	<Prev	Next>	Last>>