---
title: "对于analyze table使用"
author: "winzenghua"
date: "2009-05-08T13:57:00Z"
tags: []
slug: "analyze-table"
draft: false
lastmod: "2009-05-08T13:57:00Z"
id: 987885
url: "https://java4gis.iteye.com/blog/987885"
---

首先创建四个临时表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重新统计数据的统计信息