对于Oracle analyze table的使用总结 . 对于Oracle analyze table的使用总结 .
analyze table 一般可以指定分析： 表，所有字段，所有索引字段，所有索引。 若不指定则全部都分析。
SQL> analyze table my_table compute statistics;
SQL> analyze table my_table compute statistics for table for all indexes for all columns;
SQL> analyze table my_table compute statistics for table for all indexes for all indexed columns;
其中：
SQL> analyze table my_table compute statistics;
等价于：
SQL> analyze table my_table compute statistics for table for all indexes for all columns;
sample:
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 t5 compute statistics for all indexes;
analyze table t4 compute statistics; (不指定）
另外，可以删除分析数据：
SQL> analyze table my_table delete statistics;
SQL> analyze table my_table delete statistics for table for all indexes for all indexed columns;
http://wfly2004.blog.163.com/blog/static/1176427201042891042233/
首先创建四个临时表t1,t2,t3,t4,和他们相对应的索引
复制内容到剪贴板
代码:
create table t1 as
select * from user_objects;
create table t2 as select * from
user_objects;
create table t3 as select * from user_objects;
create table
t4 as select * from user_objects;
create unique index pk_t1_idx on
t1(object_id);
create unique index pk_t2_idx on t2(object_id);
create
unique index pk_t3_idx on t3(object_id);
create unique index pk_t4_idx on
t4(object_id);
查看这个时候各个表对应的数据库统计信息(表,字段,索引)
复制内容到剪贴板
代码:
--查看表的统计信息
select table_name,num_rows,blocks,empty_blocks
from user_table where table_names in
('T1','T2','T3','T4');
TABLE_NAME NUM_ROWS BLOCKS
EMPTY_BLOCKS
T1
T2
T3
T4
--查看字段的统计信息
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_NAME 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
T2
OBJECT_NAME
T2
SUBOBJECT_NAME
T2
OBJECT_ID
T2
DATA_OBJECT_ID
T2
OBJECT_TYPE
T2
CREATED
T2
LAST_DDL_TIME
T2
TIMESTAMP
T2
STATUS
T2
TEMPORARY
T2
GENERATED
T2
SECONDARY
T3
OBJECT_NAME
T3
SUBOBJECT_NAME
T3
OBJECT_ID
T3
DATA_OBJECT_ID
T3
OBJECT_TYPE
T3
CREATED
T3
LAST_DDL_TIME
T3
TIMESTAMP
T3
STATUS
T3
TEMPORARY
T3
GENERATED
T3
SECONDARY
T4
OBJECT_NAME
T4
SUBOBJECT_NAME
T4
OBJECT_ID
T4
DATA_OBJECT_ID
T4
OBJECT_TYPE
T4
CREATED
T4
LAST_DDL_TIME
T4
TIMESTAMP
T4
STATUS
T4
TEMPORARY
T4
GENERATED
T4
SECONDARY
--查看索引的统计信息
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_NAME INDEX_NAME
BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS
AVG_DATA_BLOCKS CLUSTERING_FACTOR NUM_ROWS
T1
PK_T1_IDX
T2
PK_T2_IDX
T3
PK_T3_IDX
T4
PK_T4_IDX
现在我们分别对这个表做不同形式的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;
我们再回头看看这是的oracle数据库对于各种统计信息
复制内容到剪贴板
代码:
--这是对于表的统计信息
select table_name,num_rows,blocks,empty_blocks from
user_tables where table_name in ('T1','T2','T3','T4');
TABLE_NAME
NUM_ROWS BLOCKS EMPTY_BLOCKS
T1 3930 55
1
T2
T3
T4
3933 55 1
--我们可以据此得出结论,只有我们在analyze table命令中指定了for
table或者不指定任何参数的时候,oracle数据库才会给我们统计基于表的统计信息
--这是对于表中字段的统计信息
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_NAME
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
T2 OBJECT_NAME
3823 41423030 D3F1BBB736D4C2B7DDCFFABBA7C7E5B5A5
.000270447891062615
T2 SUBOBJECT_NAME 77 503031
52455354 .012987012987013
T2 OBJECT_ID 3930
C304062D C30F4619 .000254452926208651
T2
DATA_OBJECT_ID 3662 C304062D C30F4619
.000273074822501365
T2 OBJECT_TYPE 15
4441544142415345204C494E4B 56494557
.000127194098193844
T2 CREATED 3684
7867081E111F33 7868071211152F .000547559423988464
T2
LAST_DDL_TIME 3574 7867081E11251B 7868071211152F
.000565522924083892
T2 TIMESTAMP 3649
323030332D30382D33303A31363A33303A3530
323030342D30372D31383A31363A32303A3436 .000559822349362313
T2
STATUS 2 494E56414C4944 56414C4944
.000127194098193844
T2 TEMPORARY 2 4E 59
.000127194098193844
T2 GENERATED 2 4E 59
.000127194098193844
T2 SECONDARY 2 4E 59
.000127194098193844
T3 OBJECT_NAME
T3 SUBOBJECT_NAME
T3
OBJECT_ID 3931 C304062D C30F461A
.000254388196387688
T3 DATA_OBJECT_ID
T3 OBJECT_TYPE
T3
CREATED
T3
LAST_DDL_TIME
T3
TIMESTAMP
T3
STATUS
T3
TEMPORARY
T3
GENERATED
T3
SECONDARY
T4 OBJECT_NAME
3825 41423030 D3F1BBB736D4C2B7DDCFFABBA7C7E5B5A5
.000261437908496732
T4 SUBOBJECT_NAME 77 503031
52455354 .012987012987013
T4 OBJECT_ID 3932
C304062D C30F461B .000254323499491353
T4
DATA_OBJECT_ID 3664 C304062D C30F461B
.00027292576419214
T4 OBJECT_TYPE 15
4441544142415345204C494E4B 56494557 .0666666666666667
T4
CREATED 3685 7867081E111F33 78680712111530
.000271370420624152
T4 LAST_DDL_TIME 3575
7867081E11251B 78680712111530 .00027972027972028
T4
TIMESTAMP 3650 323030332D30382D33303A31363A33303A3530
323030342D30372D31383A31363A32303A3437 .000273972602739726
T4
STATUS 2 494E56414C4944 56414C4944 .5
T4
TEMPORARY 2 4E 59 .5
T4 GENERATED
2 4E 59 .5
T4 SECONDARY 2 4E
59
.5
/*
在这个结果中我们可以看到,oracle数据库给t2,t4的所有字段都做了统计信息.
对表t3的object_id(索引字段)做了统计信息.
由此得出结论,
在指定for
all columns 和不指定任何参数的时候oracle会给所有字段做统计信息,在指定for indexed
columns时,oracle只给[b]有索引的字段进行字段信息统计[/b],如果我们别有必要给所有字段统计信息时,这个属性就很有用了.
*/
--这里是对于索引的统计信息
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_NAME INDEX_NAME BLEVEL
LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS
AVG_DATA_BLOCKS CLUSTERING_FACTOR NUM_ROWS
T1
PK_T1_IDX
T2
PK_T2_IDX
T3
PK_T3_IDX
T4
PK_T4_IDX 1 9 3932 1 1 2143
3932
--从这里我们可以看出,只有表t4有索引统计信息.
--再综合前面的我们就会发现,如果在运行analyze
table是我们不指定参数,oracle将收集对于特定表的所有统计信息(表,索引,表字段的统计信息)
补充,truncate命令不修改以上统计信息
复制内容到剪贴板
代码:
truncate
table t1;
truncate table t2;
truncate table t3;
truncate table
t4;
--我们在查看表和索引的统计信息
select table_name,num_rows,blocks,empty_blocks from
user_tables where table_name in ('T1','T2','T3','T4');
TABLE_NAME
NUM_ROWS BLOCKS EMPTY_BLOCKS
T1 3930 55
1
T2
T3
T4
3933 55 1
--索引的统计信息
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_NAME INDEX_NAME BLEVEL
LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS
AVG_DATA_BLOCKS CLUSTERING_FACTOR NUM_ROWS
T1
PK_T1_IDX
T2
PK_T2_IDX
T3
PK_T3_IDX
T4
PK_T4_IDX 1 9 3932 1 1 2143
3932
--我们再对以上各表做一次分析
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;
--现在再来查看表和索引的统计信息
select
table_name,num_rows,blocks,empty_blocks,initial_extent,'8192' block_size from
user_tables where table_name in ('T1','T2','T3','T4');
TABLE_NAME
NUM_ROWS BLOCKS EMPTY_BLOCKS INITIAL_EXTENT
BLOCK_SIZE
T1 0 0 8 65536
8192
T2 65536
8192
T3 65536 8192
T4
0 0 8 65536 8192
--索引的统计信息
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_NAME INDEX_NAME BLEVEL
LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS
AVG_DATA_BLOCKS CLUSTERING_FACTOR NUM_ROWS
T1
PK_T1_IDX
T2
PK_T2_IDX
T3
PK_T3_IDX
T4
PK_T4_IDX 0 0 0 0 0 0
0
--由此得出结论,truncate命令不会修改数据的统计信息,
--也就是如果我们想让CBO利用合理利用数据的统计信息的时候,需要我们及时的使用analyze命令或者dbms_stats重新统计数据的统计信息
