Global temporary table tips

Question: When do I use a global temporary table? How
does a global temporary table work to pass data to
subsequent SQL statements?

Answer:Oracle
introduced Global Temporary Tables (GTT) for removing
complex subqueries and allowing us to materialize the
intermediate data that we need to solve a complex problem
with SQL.

A
global temporary table is a "template" for a table, that can
be populated by any session in Oracle. Once the
session terminates, the global temporary table data
disappears.

12c note: Starting in
12c, Oracle will allow you to invoke session-level
dbms_stats to gather statistics specific to your own
global temporary table. Prior to 12c, statistics were shared
from a master copy of the CBO statistics.

A global
temporary table can dramatically improve the performance of
certain SQL self-join queries that summarize data values.
You can use the global
temporary tables (GTT) syntax to improve the speed of
queries that perform complex summarization activities.

The Oracle database codified the global temporary table
starting in Oracle8i, allowing you to replace complex
subqueries with a global temporary table in order to improve
the speed and readability of ccomplex queries that must
store and pass intermediate data between SQL statements.

The best on site "Oracle
training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!

Burleson is the American Team

Note:This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.

Verify
experience!Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.

Errata? Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just e-mail: