In another blog posting, I asserted that statistics collected by one session on a Global Temporary table (GTT) would be used by other sessions that reference that table, even though each session has their own physical instance of the table. I thought I should demonstrate that behaviour, so here is a simple test.
We will need two database sessions. I will create a test Global Temporary table with a unique index.
DROP TABLE t PURGE;
TRUNCATE TABLE t;
CREATE GLOBAL TEMPORARY TABLE t
(a NUMBER,b VARCHAR2(1000))
ON COMMIT PRESERVE ROWS;
CREATE UNIQUE INDEX t ON t(a);