-
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.
Jeff Hunter
-
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
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|