Re: views all_tables and dba_tables

From: Igor Racic <igor.racicC_at_freesurf.fr_no_C>
Date: Sat, 18 Dec 2004 17:26:29 +0000
Message-ID: <41c45953$0$21968$626a14ce@news.free.fr>

tb wrote:
> I executed "analyse table B compute statistics for all indexed columns."
> Oracle 8.1.7.3

tb,

You did just indexed columns and indexes. No table (and no other columns, but that probably does not surprise you). Without "for all indexed columns" will do all things.

SQL> create table t ( a number, b number );

Table created.

SQL> create index ti on t ( a );

Index created.

SQL> analyze table t compute statistics for all indexed columns;

Table analyzed.

   1 select 'TABLE', table_name, last_analyzed from user_tables where table_name='T'

   2 union all
   3 select 'INDEX', index_name, last_analyzed from user_indexes where index_name = 'TI'

   4 union all
   5* select 'COLUMN', column_name, last_analyzed from user_tab_columns where table_name = 'T';

'TABLE TABLE_NAME                     LAST_ANALYZED
------ ------------------------------ -------------------
TABLE  T
INDEX  TI                             18/12/2004 16:49:48
COLUMN A                              18/12/2004 16:50:12

COLUMN B SQL> analyze table t compute statistics;

Table analyzed.

SQL> select 'TABLE', table_name, last_analyzed from user_tables where table_name='T'

   2 union all
   3 select 'INDEX', index_name, last_analyzed from user_indexes where index_name = 'TI'

   4 union all
   5 select 'COLUMN', column_name, last_analyzed from user_tab_columns where table_name = 'T'

   6 /

'TABLE TABLE_NAME                     LAST_ANALYZED
------ ------------------------------ -------------------
TABLE  T                              18/12/2004 17:19:42
INDEX  TI                             18/12/2004 17:19:42
COLUMN A                              18/12/2004 17:19:42
COLUMN B                              18/12/2004 17:19:42

Also, if you insist 'all indexed columns' you would do

SQL> analyze table t compute statistics for table for all indexes for all indexed columns;

Table analyzed.

SQL> select 'TABLE', table_name, last_analyzed from user_tables where table_name='T'

   2 union all
   3 select 'INDEX', index_name, last_analyzed from user_indexes where index_name = 'TI'

   4 union all
   5 select 'COLUMN', column_name, last_analyzed from user_tab_columns where table_name = 'T';

'TABLE TABLE_NAME                     LAST_ANALYZED
------ ------------------------------ -------------------
TABLE  T                              18/12/2004 17:23:50
INDEX  TI                             18/12/2004 17:23:50
COLUMN A                              18/12/2004 17:23:50
COLUMN B                              18/12/2004 17:19:42

BTW, Oracle advice is to collect stats with dbms_stats package instead of analyze command.

Igor Received on Sat Dec 18 2004 - 11:26:29 CST