GLOBAL TEMPORARY TABLES and STATISTICS for BASE GTT and MATERIALISED GTT instance.

Database
Enthusiast

GLOBAL TEMPORARY TABLES and STATISTICS for BASE GTT and MATERIALISED GTT instance.

Given statistics can be held on both:

  1. the materialised instance and
  2. the base schema in the data dictionary

A) When does the base GTT acquire derived, sample and full statistics on its columns. 

Further, given user A and user B have an instance of the same GTT running in a query. 

----------- session 1

CREATE GLOBAL TEMPORARY TABLE b_gtt AS (SELECT a FROM t1) WITH NO DATA PRIMARY INDEX (a) ON COMMIT PRESERVE ROWS;

--default is to not copy stats from base table, but can't seem to specifiy "WITH STATISTICS"

INSERT INTO b_gtt SELECT a FROM t2;

COLLECT STATISTICS USING SAMPLE ON b_gtt COLUMN (a);

---------- session 2

INSERT INTO b_gtt SELECT a FROM t3;

B) will session 2's b_gtt use base table stats of t1, sample stats from t2 or derived stats from t3?