Oracle global temporary tables - maximum number of records?

Question

Is there a limit on how many records can go to a global temporary table, before it affects performance, and does it affect performance? I am talking here about millions of records that might be inserted to a global temporary table.

Answer 1

there is no hard limit to the number of records in a global temporary table. Data will eventually be written to disk and therefore will be subject to read/write speed when you insert/update your data or query the table. You would expect performance (i.e data modification and data access) to be roughly of the same level as a regular table -- a little faster since there is less redo generated.

You can add indexes to a global temporary table to ease retrieval of a subset of rows (it will obviously slow down insert and consume more temp space, this is a trade-off)

Answer 2

As with any table, of course the volume of data will have an impact on performance. GTTs may need indexes like any other table. One thing that is different is the optimiser statistics - by default there aren't any so Oracle makes a guess. See this Tom Kyte article on how to provide the optimiser with accurate statistics after loading the GTT.