SQL> CREATE TABLE a AS SELECT * FROM all_objects;
CREATE TABLE b AS SELECT * FROM all_objects;
CREATE TABLE c AS SELECT * FROM all_objects;
CREATE TABLE d AS SELECT * FROM all_objects;
Table created.
SQL>
Table created.
SQL>
Table created.
SQL>
Table created.
SQL> CREATE UNIQUE INDEX unq_a ON etl.a(OBJECT_ID);
CREATE UNIQUE INDEX unq_b ON etl.b(OBJECT_ID);
CREATE UNIQUE INDEX unq_c ON etl.c(OBJECT_ID);
CREATE UNIQUE INDEX unq_d ON etl.d(OBJECT_ID);
Index created.
SQL>
Index created.
SQL>
Index created.
SQL>
Index created.
SQL> SELECT table_name,empty_blocks,num_rows from all_tables where table_name IN ('A','B','C','D');
C
D
A
B
SQL> SELECT table_name,column_name,num_distinct,low_value,high_value,NUM_NULLS FROM all_tab_columns WHERE TABLE_NAME IN ('A','B','C','D');
A OWNER
A OBJECT_NAME
A SUBOBJECT_NAME
A OBJECT_ID
A DATA_OBJECT_ID
A OBJECT_TYPE
A CREATED
A LAST_DDL_TIME
A TIMESTAMP
A STATUS
A TEMPORARY
A GENERATED
A SECONDARY
B OWNER
B OBJECT_NAME
B SUBOBJECT_NAME
B OBJECT_ID
B DATA_OBJECT_ID
B OBJECT_TYPE
B CREATED
B LAST_DDL_TIME
B TIMESTAMP
B STATUS
B TEMPORARY
B GENERATED
B SECONDARY
C OWNER
C OBJECT_NAME
C SUBOBJECT_NAME
C OBJECT_ID
C DATA_OBJECT_ID
C OBJECT_TYPE
C CREATED
C LAST_DDL_TIME
C TIMESTAMP
C STATUS
C TEMPORARY
C GENERATED
C SECONDARY
D OWNER
D OBJECT_NAME
D SUBOBJECT_NAME
D OBJECT_ID
D DATA_OBJECT_ID
D OBJECT_TYPE
D CREATED
D LAST_DDL_TIME
D TIMESTAMP
D STATUS
D TEMPORARY
D GENERATED
D SECONDARY
SQL> analyze TABLE a COMPUTE statistics FOR TABLE;
Table analyzed.
SQL> SELECT table_name,empty_blocks,num_rows from all_tables where table_name IN ('A','B','C','D');
C
D
A 97 65203
B
SQL> SELECT table_name,column_name,num_distinct,low_value,high_value,NUM_NULLS FROM all_tab_columns WHERE TABLE_NAME IN ('A','B','C','D');
A OWNER
A OBJECT_NAME
A SUBOBJECT_NAME
A OBJECT_ID
A DATA_OBJECT_ID
A OBJECT_TYPE
A CREATED
A LAST_DDL_TIME
A TIMESTAMP
A STATUS
A TEMPORARY
A GENERATED
A SECONDARY
B OWNER
B OBJECT_NAME
B SUBOBJECT_NAME
B OBJECT_ID 65204 C103
C3434B47 0
B DATA_OBJECT_ID
B OBJECT_TYPE
B CREATED
B LAST_DDL_TIME
B TIMESTAMP
B STATUS
B TEMPORARY
B GENERATED
B SECONDARY
C OWNER
C OBJECT_NAME
C SUBOBJECT_NAME
C OBJECT_ID
C DATA_OBJECT_ID
C OBJECT_TYPE
C CREATED
C LAST_DDL_TIME
C TIMESTAMP
C STATUS
C TEMPORARY
C GENERATED
C SECONDARY
D OWNER
D OBJECT_NAME
D SUBOBJECT_NAME
D OBJECT_ID
D DATA_OBJECT_ID
D OBJECT_TYPE
D CREATED
D LAST_DDL_TIME
D TIMESTAMP
D STATUS
D TEMPORARY
D GENERATED
D SECONDARY
SQL> SELECT table_name,empty_blocks,num_rows from all_tables where table_name IN ('A','B','C','D');
C 97 65205
D
A 97 65203
B
SQL> SELECT table_name,column_name,num_distinct,low_value,high_value,NUM_NULLS FROM all_tab_columns WHERE TABLE_NAME IN ('A','B','C','D');
A OWNER
A OBJECT_NAME
A SUBOBJECT_NAME
A OBJECT_ID
A DATA_OBJECT_ID
A OBJECT_TYPE
A CREATED
A LAST_DDL_TIME
A TIMESTAMP
A STATUS
A TEMPORARY
A GENERATED
A SECONDARY
B OWNER
B OBJECT_NAME
B SUBOBJECT_NAME
B OBJECT_ID 65204 C103
C3434B47 0
B DATA_OBJECT_ID
B OBJECT_TYPE
B CREATED
B LAST_DDL_TIME
B TIMESTAMP
B STATUS
B TEMPORARY
B GENERATED
B SECONDARY
C OWNER
C OBJECT_NAME
C SUBOBJECT_NAME
C OBJECT_ID 65205 C103
C3434B48 0
C DATA_OBJECT_ID
C OBJECT_TYPE
C CREATED
C LAST_DDL_TIME
C TIMESTAMP
C STATUS
C TEMPORARY
C GENERATED
C SECONDARY
D OWNER
D OBJECT_NAME
D SUBOBJECT_NAME
D OBJECT_ID
D DATA_OBJECT_ID
D OBJECT_TYPE
D CREATED
D LAST_DDL_TIME
D TIMESTAMP
D STATUS
D TEMPORARY
D GENERATED
D SECONDAR
SQL> analyze TABLE d COMPUTE statistics;
Table analyzed.
SQL> SELECT table_name,column_name,num_distinct,low_value,high_value,NUM_NULLS FROM all_tab_columns WHERE TABLE_NAME IN ('A','B','C','D');
select table_name,index_name,blevel,leaf_blocks,distinct_keys,
avg_leaf_blocks_per_key avg_leaf_blocks,avg_data_blocks_per_key avg_data_blocks,clustering_factor,num_rows
A OWNER
A OBJECT_NAME
A SUBOBJECT_NAME
A OBJECT_ID
A DATA_OBJECT_ID
A OBJECT_TYPE
A CREATED
A LAST_DDL_TIME
A TIMESTAMP
A STATUS
A TEMPORARY
A GENERATED
A SECONDARY
B OWNER
B OBJECT_NAME
B SUBOBJECT_NAME
B OBJECT_ID 65204 C103
C3434B47 0
B DATA_OBJECT_ID
B OBJECT_TYPE
B CREATED
B LAST_DDL_TIME
B TIMESTAMP
B STATUS
B TEMPORARY
B GENERATED
B SECONDARY
C OWNER
C OBJECT_NAME
C SUBOBJECT_NAME
C OBJECT_ID 65205 C103
C3434B48 0
C DATA_OBJECT_ID
C OBJECT_TYPE
C CREATED
C LAST_DDL_TIME
C TIMESTAMP
C STATUS
C TEMPORARY
C GENERATED
C SECONDARY
D OWNER 46 414C494B5049
584442 0
D OBJECT_NAME 32790 2F31303030653864315F4C696E6B6564486173684D617056616C75654974
794362437253756253616D706C696E67547970653137305F54 0
D SUBOBJECT_NAME 2819 5030
575248245F5741495453545F313034353334373436305F39373134 53172
D OBJECT_ID 65206 C103
C3434B49 0
D DATA_OBJECT_ID 17396 80
C3434B49 47770
D OBJECT_TYPE 35 434C5553544552
57494E444F572047524F5550 0
D CREATED 4514 786A0A12123321
786B0B1C0E2637 0
D LAST_DDL_TIME 5534 78660A010D2A32
786B0B1C0E2637 0
D TIMESTAMP 5173 313939302D30382D32363A31313A32353A3030
323030372D31312D32383A31333A33373A3534 0
D STATUS 2 494E56414C4944
56414C4944 0
D TEMPORARY 2 4E
59 0
D GENERATED 2 4E
59 0
D SECONDARY 1 4E
4E 0
52 rows selected.
SQL> SQL> SQL> 2 3 from all_indexes where table_name in ('A','B','C','D');
B UNQ_B 1 135 65204 1 1 2222
65204
A UNQ_A 1 135 65203 1 1 2221
65203
D UNQ_D 1 135 65206 1 1 2211
65206
C UNQ_C 1 135 65205 1 1 2222
65205
总结:
analyze TABLE a COMPUTE statistics FOR TABLE;
对表做统计分析,仅仅影响all_tables中的相关数据
analyze TABLE b COMPUTE statistics FOR ALL indexed columns;
对索引列做统计分析,仅仅影响all_tab_columns中索引列相关数据
analyze TABLE c COMPUTE statistics FOR TABLE FOR ALL indexed columns;
对表和索引列做统计分析,影响all_tables中对应表名的相关数据及all_tab_columns中对应 索引列的相关数据
analyze TABLE d COMPUTE statistics;
分析表的所有列和表的整体信息,影响all_tables中对应表名的相关数据及all_tab_columns中属于表d的 所有列的相关数据
[@more@]