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_at_jpmchase.com 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