ITPub博客

首页 > 数据库 > Oracle > analyze table 的各种语法及对应工作机制

analyze table 的各种语法及对应工作机制

原创 Oracle 作者:flysky0814 时间:2007-11-28 13:51:25 0 删除 编辑

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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/11423276/viewspace-985713/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-03-31

  • 博文量
    53
  • 访问量
    382410