Re: Temporary Tables - 3 of 3
- From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
- To: Rajesh.Rao@xxxxxxxxxxxx
- Date: Sun, 28 Aug 2005 14:04:04 -0600
Unless you are still using Oracle 8i, you CAN gather statistics on GTTs:
14:01:15 ora92.scott> create global temporary table gt on commit preserve rows
14:01:37 2 as select * from dba_objects where rownum <= 1000
14:01:45 3 /
Table created.
14:01:46 ora92.scott> commit;
Commit complete.
14:01:50 ora92.scott> select count(0) from gt;
COUNT(0)
----------
1000
1 row selected.
14:01:59 ora92.scott> exec
dbms_stats.gather_table_stats(user,'gt',method_opt=>'for all columns size 1');
PL/SQL procedure successfully completed.
14:02:10 ora92.scott> @tblstats gt
avg
TABLE_NAME free used fl
log rows blks empty row LAST_ANALYZED pool G U
------------------------------ ---- ---- --- --- ------------
---------- ------- ------ -------------------- -------- - -
GT 10 40 1
NO 1,000 26 0 82 2005-08-28 14:02:37 DEFAULT Y N
1 row selected.
14:02:15 ora92.scott>
At 09:03 AM 8/28/2005, Rajesh.Rao@xxxxxxxxxxxx wrote:
The main disadvantage with global temporary tables, is that you cannot
gather statistics for them (unless a hint is provided), and hence, the
optimizer might not choose an optimal plan, especially when you join GTT's
with permanent tables in your transactions. Second, is that if you have
Regards
Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
