analyze table 的各種語法及對應工作機制

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@]