Re: Global Temporary Table

On 8 Sep 2004 00:41:22 -0700, lalydba_at_free.fr (Laly) wrote:

>True i can create an index on a temporary table but since we cannot
>gather stats on this table the index are never used.

 Sure you can; only for GTTs which are 'on commit preserve rows', but that's what you said you were using.

SQL> create global temporary table gtt (   2 id number not null primary key
  3 )
  4 on commit preserve rows;

Table created.

SQL> insert into gtt values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(ownname => user, tabname => 'GTT');

PL/SQL procedure successfully completed.

SQL> select num_rows from user_tables where table_name = 'GTT';

  NUM_ROWS

         1  

 There's also the gather_temp option for dbms_stats.gather_schema_stats.

 The trick is to work out what's a typical data set for the table so the stats make some sort of sense, but you can do it.

-- 
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool