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 fromuser_indexeswhere table_name in ('T1','T2','T3','T4');
TABLE INDEX_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS AVG_DATA_BLOCKS CLUSTERING_FACTOR NUM_ROWS
----- ------------------------------ ---------- ----------- ------------- --------------- --------------- -----------------
T4 PK_T4_IDX 1 79 37804 1 1 549 37804
T3 PK_T3_IDX 1 79 37803 1 1 549 37803
T2 PK_T2_IDX 1 79 37802 1 1 549 37802
T1 PK_T1_IDX 1 79 37801 1 1 549 37801
SQL>
现在我们分别对这4个表做不同形式的analyze table处理：
analyze table t1 compute statistics for
table;
analyze table t2 compute statistics for all columns;
analyze table
t3 compute statistics for all indexed columns;
analyze table t4 compute
statistics;
SQL> analyze table t1 compute statistics for table;
Table analyzed.
SQL> analyze table t2 compute statistics for all columns;
Table analyzed.
SQL> analyze table t3 compute statistics for all indexed columns;
Table analyzed.
SQL> analyze table t4 compute statistics;
Table analyzed.
SQL>
再回头看看这是的oracle数据库对于各种统计信息：
--这是对于表的统计信息
select table_name,num_rows,blocks,empty_blocks from user_tables where table_name in ('T1','T2','T3','T4');
TABLE NUM_ROWS BLOCKS EMPTY_BLOCKS
----- ---------- ---------- ------------
T1 37801 514 125
T2
T3
T4 37804 514 125
据此，我们可以据此得出结论,只有我们在analyze table命令中指定了for
table或者不指定任何参数的时候,oracle数据库才会给我们统计基于表的统计信息
--这是对于表中字段的统计信息
SQL> select table_name,column_name,num_distinct,low_value,high_value,density from user_tab_columns where table_name in ('T1','T2','T3','T4');
TABLE COLUMN_NAME NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY
----- ------------------------------ ------------ --------------- --------------- ------------
T1 OBJECT_NAME
T1 SUBOBJECT_NAME
T1 OBJECT_ID
T1 DATA_OBJECT_ID
T1 OBJECT_TYPE
T1 CREATED
T1 LAST_DDL_TIME
T1 TIMESTAMP
T1 STATUS
T1 TEMPORARY
T1 GENERATED
T1 SECONDARY
T1 NAMESPACE
T1 EDITION_NAME
T2 OBJECT_NAME 36920 2F3130303033323 73756E2F7574696 0
3645F44656C6567 C2F7265736F7572
617465496E766F6 6365732F4C6F636
36174696F6E4861 16C654E616D6573
T2 SUBOBJECT_NAME 84 5030 575248245F57414 0
95453545F333238
323839373733325
F30
T2 OBJECT_ID 37802 C103 C309581E 0
T2 DATA_OBJECT_ID 2447 C103 C309581E 0
T2 OBJECT_TYPE 41 434C5553544552 57494E444F57 0
T2 CREATED 516 787108180C2624 787505070A1D0B 0
T2 LAST_DDL_TIME 604 787108180C2624 787505070A1D0B 0
T2 TIMESTAMP 571 313939372D30342 323031372D30352 0
D31323A31323A35 D30373A30393A32
393A3030 383A3130
T2 STATUS 2 494E56414C4944 56414C4944 0
T2 TEMPORARY 2 4E 59 0
T2 GENERATED 2 4E 59 0
T2 SECONDARY 1 4E 4E 0
T2 NAMESPACE 18 C102 C141 0
T2 EDITION_NAME 0 0
T3 OBJECT_NAME
T3 SUBOBJECT_NAME
T3 OBJECT_ID 37803 C103 C309581F 0
T3 DATA_OBJECT_ID
T3 OBJECT_TYPE
T3 CREATED
T3 LAST_DDL_TIME
T3 TIMESTAMP
T3 STATUS
T3 TEMPORARY
T3 GENERATED
T3 SECONDARY
T3 NAMESPACE
T3 EDITION_NAME
T4 OBJECT_NAME 36922 2F3130303033323 73756E2F7574696 0
3645F44656C6567 C2F7265736F7572
617465496E766F6 6365732F4C6F636
36174696F6E4861 16C654E616D6573
T4 SUBOBJECT_NAME 84 5030 575248245F57414 0
95453545F333238
323839373733325
F30
T4 OBJECT_ID 37804 C103 C3095820 0
T4 DATA_OBJECT_ID 2449 C103 C3095820 0
T4 OBJECT_TYPE 41 434C5553544552 57494E444F57 0
T4 CREATED 518 787108180C2624 787505070A1D17 0
T4 LAST_DDL_TIME 606 787108180C2624 787505070A1D17 0
T4 TIMESTAMP 573 313939372D30342 323031372D30352 0
D31323A31323A35 D30373A30393A32
393A3030 383A3232
T4 STATUS 2 494E56414C4944 56414C4944 1
T4 TEMPORARY 2 4E 59 1
T4 GENERATED 2 4E 59 1
T4 SECONDARY 1 4E 4E 1
T4 NAMESPACE 18 C102 C141 0
T4 EDITION_NAME 0 0
56 rows selected.
据此，在指定for
all columns 和不指定任何参数的时候oracle会给所有字段做统计信息,在指定for indexed
columns时,oracle只给有索引的字段进行字段信息统,如果我们别有必要给所有字段统计信息时,这个属性就很有用了.
--这里是对于索引的统计信息：SQL> 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 from user_indexes where table_name in ('T1','T2','T3','T4');
TABLE INDEX_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS AVG_DATA_BLOCKS CLUSTERING_FACTOR NUM_ROWS
----- ------------------------------ ---------- ----------- ------------- --------------- --------------- ----------------- ----------
T4 PK_T4_IDX 1 79 37804 1 1 549 37804
T3 PK_T3_IDX
T2 PK_T2_IDX
T1 PK_T1_IDX
--从这里我们可以看出,只有表t4有索引统计信息.
--再综合前面的我们就会发现,如果在运行analyze
table时我们不指定参数,oracle将收集对于特定表的所有统计信息(表,索引,表字段的统计信息)
====================补充================================
补充,truncate命令不修改以上统计信息：
truncate
table t1;
truncate table t2;
truncate table t3;
truncate table
t4;
SQL> select table_name,num_rows,blocks,empty_blocks from user_tables where table_name in ('T1','T2','T3','T4');
TABLE NUM_ROWS BLOCKS EMPTY_BLOCKS
----- ---------- ---------- ------------
T1 37801 514 125
T2
T3
T4 37804 514 125
SQL> select table_name,column_name,num_distinct,low_value,high_value,density from user_tab_columns where table_name in ('T1','T2','T3','T4');
TABLE COLUMN_NAME NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY
----- ------------------------------ ------------ --------------- --------------- ------------
T1 OBJECT_NAME
T1 SUBOBJECT_NAME
T1 OBJECT_ID
T1 DATA_OBJECT_ID
T1 OBJECT_TYPE
T1 CREATED
T1 LAST_DDL_TIME
T1 TIMESTAMP
T1 STATUS
T1 TEMPORARY
T1 GENERATED
T1 SECONDARY
T1 NAMESPACE
T1 EDITION_NAME
T2 OBJECT_NAME 36920 2F3130303033323 73756E2F7574696 0
3645F44656C6567 C2F7265736F7572
617465496E766F6 6365732F4C6F636
36174696F6E4861 16C654E616D6573
T2 SUBOBJECT_NAME 84 5030 575248245F57414 0
95453545F333238
323839373733325
F30
T2 OBJECT_ID 37802 C103 C309581E 0
T2 DATA_OBJECT_ID 2447 C103 C309581E 0
T2 OBJECT_TYPE 41 434C5553544552 57494E444F57 0
T2 CREATED 516 787108180C2624 787505070A1D0B 0
T2 LAST_DDL_TIME 604 787108180C2624 787505070A1D0B 0
T2 TIMESTAMP 571 313939372D30342 323031372D30352 0
D31323A31323A35 D30373A30393A32
393A3030 383A3130
T2 STATUS 2 494E56414C4944 56414C4944 0
T2 TEMPORARY 2 4E 59 0
T2 GENERATED 2 4E 59 0
T2 SECONDARY 1 4E 4E 0
T2 NAMESPACE 18 C102 C141 0
T2 EDITION_NAME 0 0
T3 OBJECT_NAME
T3 SUBOBJECT_NAME
T3 OBJECT_ID 37803 C103 C309581F 0
T3 DATA_OBJECT_ID
T3 OBJECT_TYPE
T3 CREATED
T3 LAST_DDL_TIME
T3 TIMESTAMP
T3 STATUS
T3 TEMPORARY
T3 GENERATED
T3 SECONDARY
T3 NAMESPACE
T3 EDITION_NAME
T4 OBJECT_NAME 36922 2F3130303033323 73756E2F7574696 0
3645F44656C6567 C2F7265736F7572
617465496E766F6 6365732F4C6F636
36174696F6E4861 16C654E616D6573
T4 SUBOBJECT_NAME 84 5030 575248245F57414 0
95453545F333238
323839373733325
F30
T4 OBJECT_ID 37804 C103 C3095820 0
T4 DATA_OBJECT_ID 2449 C103 C3095820 0
T4 OBJECT_TYPE 41 434C5553544552 57494E444F57 0
T4 CREATED 518 787108180C2624 787505070A1D17 0
T4 LAST_DDL_TIME 606 787108180C2624 787505070A1D17 0
T4 TIMESTAMP 573 313939372D30342 323031372D30352 0
D31323A31323A35 D30373A30393A32
393A3030 383A3232
T4 STATUS 2 494E56414C4944 56414C4944 1
T4 TEMPORARY 2 4E 59 1
T4 GENERATED 2 4E 59 1
T4 SECONDARY 1 4E 4E 1
T4 NAMESPACE 18 C102 C141 0
T4 EDITION_NAME 0 0
56 rows selected.
SQL> 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 from user_indexes where table_name in ('T1','T2','T3','T4');
TABLE INDEX_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS AVG_DATA_BLOCKS CLUSTERING_FACTOR NUM_ROWS
----- ------------------------------ ---------- ----------- ------------- --------------- --------------- ----------------- ----------
T4 PK_T4_IDX 1 79 37804 1 1 549 37804
T3 PK_T3_IDX
T2 PK_T2_IDX
T1 PK_T1_IDX
结论：统计信息未改变；
--我们再对以上各表做一次分析
analyze table t1 compute statistics for
table;
analyze table t2 compute statistics for all columns;
analyze table
t3 compute statistics for all indexed columns;
analyze table t4 compute
statistics;
再次查询：
SQL> select table_name,num_rows,blocks,empty_blocks from user_tables where table_name in ('T1','T2','T3','T4');
TABLE NUM_ROWS BLOCKS EMPTY_BLOCKS
----- ---------- ---------- ------------
T1 0 0 7
T2
T3
T4 37804 514 125
SQL> select table_name,column_name,num_distinct,low_value,high_value,density from user_tab_columns where table_name in ('T1','T2','T3','T4');
TABLE COLUMN_NAME NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY
----- ------------------------------ ------------ --------------- --------------- ------------
T1 OBJECT_NAME
T1 SUBOBJECT_NAME
T1 OBJECT_ID
T1 DATA_OBJECT_ID
T1 OBJECT_TYPE
T1 CREATED
T1 LAST_DDL_TIME
T1 TIMESTAMP
T1 STATUS
T1 TEMPORARY
T1 GENERATED
T1 SECONDARY
T1 NAMESPACE
T1 EDITION_NAME
T2 OBJECT_NAME 0 0
T2 SUBOBJECT_NAME 0 0
T2 OBJECT_ID 0 0
T2 DATA_OBJECT_ID 0 0
T2 OBJECT_TYPE 0 0
T2 CREATED 0 0
T2 LAST_DDL_TIME 0 0
T2 TIMESTAMP 0 0
T2 STATUS 0 0
T2 TEMPORARY 0 0
T2 GENERATED 0 0
T2 SECONDARY 0 0
T2 NAMESPACE 0 0
T2 EDITION_NAME 0 0
T3 OBJECT_NAME
T3 SUBOBJECT_NAME
T3 OBJECT_ID 0 0
T3 DATA_OBJECT_ID
T3 OBJECT_TYPE
T3 CREATED
T3 LAST_DDL_TIME
T3 TIMESTAMP
T3 STATUS
T3 TEMPORARY
T3 GENERATED
T3 SECONDARY
T3 NAMESPACE
T3 EDITION_NAME
T4 OBJECT_NAME 36922 2F3130303033323 73756E2F7574696 0
3645F44656C6567 C2F7265736F7572
617465496E766F6 6365732F4C6F636
36174696F6E4861 16C654E616D6573
T4 SUBOBJECT_NAME 84 5030 575248245F57414 0
95453545F333238
323839373733325
F30
T4 OBJECT_ID 37804 C103 C3095820 0
T4 DATA_OBJECT_ID 2449 C103 C3095820 0
T4 OBJECT_TYPE 41 434C5553544552 57494E444F57 0
T4 CREATED 518 787108180C2624 787505070A1D17 0
T4 LAST_DDL_TIME 606 787108180C2624 787505070A1D17 0
T4 TIMESTAMP 573 313939372D30342 323031372D30352 0
D31323A31323A35 D30373A30393A32
393A3030 383A3232
T4 STATUS 2 494E56414C4944 56414C4944 1
T4 TEMPORARY 2 4E 59 1
T4 GENERATED 2 4E 59 1
T4 SECONDARY 1 4E 4E 1
T4 NAMESPACE 18 C102 C141 0
T4 EDITION_NAME 0 0
56 rows selected.
SQL> 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 from user_indexes where table_name in ('T1','T2','T3','T4');
TABLE INDEX_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS AVG_DATA_BLOCKS CLUSTERING_FACTOR NUM_ROWS
----- ------------------------------ ---------- ----------- ------------- --------------- --------------- -----------------
T4 PK_T4_IDX 1 79 37804 1 1 549 37804
T3 PK_T3_IDX
T2 PK_T2_IDX
T1 PK_T1_IDX
--由此得出结论,truncate命令不会修改数据的统计信息,--也就是如果我们想让CBO利用合理利用数据的统计信息的时候,需要我们及时的使用analyze命令或者dbms_stats重新统计数据的统计信息
931
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
