Re: Temporary Tables - 3 of 3

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 

--
http://www.freelists.org/webpage/oracle-l

Other related posts: