分区表的统计信息实例
---------------------------------------  
ORATEA ORACLE的统计信息在执行SQL的过程中扮演着非常重要的作用，而且ORACLE在表的各个层次都会有不同的统计信息，通过这些统计信息来描述表的，列的各种各样的统计信息。下面通过一个复合分区表来说明一些常见的和常见的统计信息。

SQL>
create table test
partition by range(object_id)
subpartition by hash(object_type) subpartitions 4
(partition p1 values less than(10000),
partition p2 values less than(20000),
partition p3 values less than(30000),
partition p4 values less than(maxvalue))
as
select * from dba_objects;

表已创建。
sql>
BEGIN
dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                tabname          => 'TEST',
                                estimate_percent => 100,
                                block_sample     => FALSE,
                                method_opt       => 'FOR ALL COLUMNS SIZE 10',
                                granularity      => 'ALL',
                                cascade          => TRUE);
END;

1,表级的统计信息

SQL> select table_name,num_rows,blocks,empty_blocks,avg_space from user_tables where table_name = 'TEST';

TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS AVG_SPACE
------------------------------ ---------- ---------- ------------ ----------
TEST                                50705        788            0          0

2,表上列的统计信息

SQL> select table_name,column_name,num_distinct,density from user_tab_columns where table_name = 'TEST';

TABLE_NAME                     COLUMN_NAME                    NUM_DISTINCT    DENSITY
------------------------------ ------------------------------ ------------ ----------
TEST                           OWNER                                    25 .365014295
TEST                           OBJECT_NAME                           30275 .000039205
TEST                           SUBOBJECT_NAME                          191 .015657993
TEST                           OBJECT_ID                             50705 .000019722
TEST                           DATA_OBJECT_ID                         4334 .000248075
TEST                           OBJECT_TYPE                              42 .271207855
TEST                           CREATED                                2305 .001608457
TEST                           LAST_DDL_TIME                          2369 .001566737
TEST                           TIMESTAMP                              2412 .001610251
TEST                           STATUS                                    2 .000009861
TEST                           TEMPORARY                                 2 .000009861
TEST                           GENERATED                                 2 .000009861
TEST                           SECONDARY                                 2 .000009861

13 rows selected.

3,表上列的直方图信息

SQL>
select table_name,column_name,endpoint_number,endpoint_value
from user_tab_histograms
where table_name = 'TEST'
and column_name = 'OBJECT_ID';

TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE
---------- ---------- --------------- --------------
TEST       OBJECT_ID                0              2
TEST       OBJECT_ID                1           5160
TEST       OBJECT_ID                2          10587
TEST       OBJECT_ID                3          15658
TEST       OBJECT_ID                4          20729
TEST       OBJECT_ID                5          25800
TEST       OBJECT_ID                6          30870
TEST       OBJECT_ID                7          35940
TEST       OBJECT_ID                8          41089
TEST       OBJECT_ID                9          46821
TEST       OBJECT_ID               10          53497

4,分区的统计信息

SQL>
select partition_name,num_rows,blocks,empty_blocks,avg_space
from user_tab_partitions
where table_name = 'TEST';

PARTITION_NAME    NUM_ROWS     BLOCKS EMPTY_BLOCKS AVG_SPACE
--------------- ---------- ---------- ------------ ----------
P1                    9581        140            0          0
P2                    9973        164            0          0
P3                   10000        158            0          0
P4                   21151        326            0          0

5,分区上列的统计信息

SQL> select column_name,num_distinct,density,num_nulls
from user_part_col_statistics
where table_name = 'TEST'
and partition_name = 'P1';

COLUMN_NAME     NUM_DISTINCT    DENSITY NUM_NULLS
--------------- ------------ ---------- ----------
OWNER                      7 .000052187          0
OBJECT_NAME             7412 .000156925          0
SUBOBJECT_NAME            26 .47017301       9496
OBJECT_ID               9581 .000104373          0
DATA_OBJECT_ID          1765 .000664385       7780
OBJECT_TYPE               34 .18494854          0
CREATED                  913 .001977449          0
LAST_DDL_TIME            994 .001882695          0
TIMESTAMP                982 .001928775          0
STATUS                     2 .000052187          0
TEMPORARY                  2 .000052187          0
GENERATED                  2 .000052187          0
SECONDARY                  1 .000052187          0

6,分区上列的直方图信息

SQL> select column_name,bucket_number,endpoint_value
from user_part_histograms
where table_name = 'TEST'
and partition_name = 'P1'
and column_name = 'OBJECT_ID';

COLUMN_NAME     BUCKET_NUMBER ENDPOINT_VALUE
--------------- ------------- --------------
OBJECT_ID                   0              2
OBJECT_ID                   1           1005
OBJECT_ID                   2           1963
OBJECT_ID                   3           2921
OBJECT_ID                   4           3888
OBJECT_ID                   5           4859
OBJECT_ID                   6           5941
OBJECT_ID                   7           6899
OBJECT_ID                   8           7885
OBJECT_ID                   9           8864
OBJECT_ID                  10           9999

7,子分区的统计信息

SQL> select subpartition_name,num_rows,blocks,empty_blocks
from user_tab_subpartitions
where table_name = 'TEST'
and partition_name = 'P1';

SUBPARTITION_NAME                NUM_ROWS     BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
SYS_SUBP21                           3597         50            0
SYS_SUBP22                           3566         52            0
SYS_SUBP23                            637         11            0
SYS_SUBP24                           1781         27            0

8,子分区上的列的统计信息

SQL> select column_name,num_distinct,density
from user_subpart_col_statistics
where table_name = 'TEST'
and subpartition_name = 'SYS_SUBP21';
COLUMN_NAME     NUM_DISTINCT    DENSITY
--------------- ------------ ----------
OWNER                      6 .000139005
OBJECT_NAME             3595 .000278319
SUBOBJECT_NAME             4 .014285714
OBJECT_ID               3597 .000278009
DATA_OBJECT_ID           155 .006451613
OBJECT_TYPE                8 .000139005
CREATED                  751 .002392334
LAST_DDL_TIME            784 .002302524
TIMESTAMP                768 .00235539
STATUS                     1 .000139005
TEMPORARY                  2 .000139005
GENERATED                  2 .000139005
SECONDARY                  1 .000139005

9,子分区上的列的直方图信息

SQL> select column_name,bucket_number,endpoint_value
from user_subpart_histograms
where table_name = 'TEST'
and subpartition_name = 'SYS_SUBP21'
and column_name = 'OBJECT_ID';
COLUMN_NAME     BUCKET_NUMBER ENDPOINT_VALUE
--------------- ------------- --------------
OBJECT_ID                   0            208
OBJECT_ID                   1           1525
OBJECT_ID                   2           2244
OBJECT_ID                   3           2892
OBJECT_ID                   4           3252
OBJECT_ID                   5           4047
OBJECT_ID                   6           5238
OBJECT_ID                   7           6531
OBJECT_ID                   8           7661
OBJECT_ID                   9           8474
OBJECT_ID                  10           9998

我们对这个复合分区分析之后产生了上面这九种不同层次的统计信息。CBO想要得要一个高效的执行计划需要如此多的统计信息.

 

http://www.cnblogs.com/rootq/archive/2010/02/04/1663622.html