Analyze Table

Hi,

I want to know wheather analyze table will analyze all indexes in that table or wheather we have to explicitely analyze indexes.

Analyze table tbl_name compute statistics;
or
Analyze table tbl_name estimate statistics 1000 rows;


Thanks in advance

Paresh

COMPUTE will analyze any indexes on that table.

Both forms:

Analyze table ... comute statistics;
and
Analyze table ... estimate statistics sample 1000 rows;

will compute / estimate statistics for all indexes on this table.

1) COMPUTE

SQL> analyze table train.emps delete statistics;
Table analyzed.

SQL> select TABLE_NAME, num_rows, avg_row_len from all_tables where owner ='TRAIN' and table_name='EMPS';
select index_name, table_name, blevel, leaf_blocks from all_indexes where owner ='TRAIN' and table_name='EMPS';

TABLE_NAME NUM_ROWS AVG_ROW_LEN
------------------------------ ---------- -----------
EMPS

SQL>
INDEX_NAME TABLE_NAME BLEVEL LEAF_BLOCKS
------------------------------ ------------------------------ ---------- -----------
EMPS_PK EMPS
IDX_DEP_ID_EMP EMPS
IDX_FIRST_NAME EMPS
IDX_JOB_ID_EMP EMPS
IDX_U_LAST_NAME EMPS

SQL> analyze table train.emps compute statistics;

Table analyzed.

SQL> select TABLE_NAME, num_rows, avg_row_len from all_tables where owner ='TRAIN' and table_name='EMPS';

TABLE_NAME NUM_ROWS AVG_ROW_LEN
------------------------------ ---------- -----------
EMPS 49902 68

SQL> select index_name, table_name, blevel, leaf_blocks from all_indexes where owner ='TRAIN' and table_name='EMPS';

INDEX_NAME TABLE_NAME BLEVEL LEAF_BLOCKS
------------------------------ ------------------------------ ---------- -----------
EMPS_PK EMPS 1 190
IDX_DEP_ID_EMP EMPS 1 44
IDX_FIRST_NAME EMPS 2 732
IDX_JOB_ID_EMP EMPS 1 40
IDX_U_LAST_NAME EMPS 2 356
2) ESTIMATE SAMPLE
SQL> analyze table train.emps delete statistics;

Table analyzed.

SQL> select TABLE_NAME, num_rows, avg_row_len from all_tables where owner ='TRAIN' and table_name='EMPS';

TABLE_NAME NUM_ROWS AVG_ROW_LEN
------------------------------ ---------- -----------
EMPS

SQL> select index_name, table_name, blevel, leaf_blocks from all_indexes where owner ='TRAIN' and table_name='EMPS';

INDEX_NAME TABLE_NAME BLEVEL LEAF_BLOCKS
------------------------------ ------------------------------ ---------- -----------
EMPS_PK EMPS
IDX_DEP_ID_EMP EMPS
IDX_FIRST_NAME EMPS
IDX_JOB_ID_EMP EMPS
IDX_U_LAST_NAME EMPS

SQL> analyze table train.emps estimate statistics sample 1000 rows;
Table analyzed.

SQL> select TABLE_NAME, num_rows, avg_row_len from all_tables where owner ='TRAIN' and table_name='EMPS';

TABLE_NAME NUM_ROWS AVG_ROW_LEN
------------------------------ ---------- -----------
EMPS 49902 68

SQL> select index_name, table_name, blevel, leaf_blocks from all_indexes where owner ='TRAIN' and table_name='EMPS';

INDEX_NAME TABLE_NAME BLEVEL LEAF_BLOCKS
------------------------------ ------------------------------ ---------- -----------
EMPS_PK EMPS 1 190
IDX_DEP_ID_EMP EMPS 1 44
IDX_FIRST_NAME EMPS 2 735
IDX_JOB_ID_EMP EMPS 1 40
IDX_U_LAST_NAME EMPS 2 358

Thanks a lot

Paresh