Can anyone help me on this question??... For GTT Tables while defining stats which one of the below Query works better?
collect stats on tablename column ColumnNM/Index
Collect stats on temporary tablename column ColumnNM/Index
After defining the stats using 1st query and loading the data, I checked the table with HELPSTATS command.... but I couldn't see any stats!
So I used the 2nd query and checked.Then I saw the stats for the tables.
My question is : When I am using the index columns or some key columns in some join conditions from this GTT for loading data into another table how should I define stats for this GTT as stats helps in performance improvement. Does both the query work fine ? or the 2nd query is better?
The second form (with TEMPORARY keyword) collects statistics on your session's materialized instance of the GTT.
For a GTT, the first form only defines (dummy) statistics on the base definition of the GTT. Those stats definitions are inherited when a new instance is materialized; you can then use "re-collect" syntax with the TEMPORARY keyword to collect the actual stats:
COLLECT STATS ON TEMPORARY tablename;