分区统计信息

----------------------创建测试表
create table t1 (
 part_col not null,
 id  not null,
 small_vc,
 padding 
)
partition by range(part_col) (
 partition p0200 values less than ( 200),
 partition p0400 values less than ( 400),
 partition p0600 values less than ( 600),
 partition p0800 values less than ( 800),
 partition p1000 values less than (1000)
)
nologging
as
with generator as (
 select --+ materialize
  rownum  id
 from all_objects
 where rownum <= 5000
)
select
 trunc(sqrt(rownum-1)),
 rownum-1,
 lpad(rownum-1,10),
 rpad('x',50)
from
 generator v1,
 generator v2
where
 rownum <= 1000000
/
--------------------------分析表
begin
 dbms_stats.gather_table_stats(
  user,
  't1',
  cascade => true,
  estimate_percent => null,
  method_opt => 'for all columns size 1'
 );
end;
/

------------------------查询分区统计信息
select table_name, partition_name, num_rows
from  user_tab_partitions
where table_name = 'T1'
order by partition_position
;
TABLE_NAME                     PARTITION_NAME                                                 NUM_ROWS
------------------------------ ------------------------------------------------------------ ----------
T1                             P0200                                                             40000
T1                             P0400                                                            120000
T1                             P0600                                                            200000
T1                             P0800                                                            280000
T1                             P1000                                                            360000

select partition_name, column_name, num_distinct, density
from user_part_col_statistics
where table_name = 'T1' and column_name='PART_COL'
order by partition_name
;

PARTITION_NAME                                               COLUMN_NAME                    NUM_DISTINCT    DENSITY
------------------------------------------------------------ ------------------------------ ------------ ----------
P0200                                                        PART_COL                                200       .005
P0400                                                        PART_COL                                200       .005
P0600                                                        PART_COL                                200       .005
P0800                                                        PART_COL                                200       .005
P1000                                                        PART_COL                                200       .005
-------------------------查询表级统计信息
select  table_name, num_rows
from  user_tables
where table_name = 'T1'
;
TABLE_NAME                       NUM_ROWS
------------------------------ ----------
T1                                1000000

select column_name, num_distinct, density
from user_tab_col_statistics
where table_name = 'T1'
;

COLUMN_NAME                    NUM_DISTINCT    DENSITY
------------------------------ ------------ ----------
PART_COL                               1000       .001
ID                                  1000000    .000001
SMALL_VC                            1000000    .000001
PADDING                                   1          1

---------------------------没有跨分区，且分区统计信息都存在
explain plan for
select /*+ index_rs(t1) */count(*)
from  t1
where part_col between 250 and 350
;

------------------------------------------------------------------------------------------------
| Id  | Operation               | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |     1 |     4 |   286   (1)| 00:00:04 |       |       |
|   1 |  SORT AGGREGATE         |      |     1 |     4 |            |          |       |       |
|   2 |   PARTITION RANGE SINGLE|      | 61502 |   240K|   286   (1)| 00:00:04 |     2 |     2 |
|*  3 |    TABLE ACCESS FULL    | T1   | 61502 |   240K|   286   (1)| 00:00:04 |     2 |     2 |
------------------------------------------------------------------------------------------------

选择率=(350-250)/(399-200)+1/100-------------分区级别统计信息
基数  =120000*选择率=61502

 

---------------------------跨分区
explain plan for
select count(*)
from  t1
where part_col between 150 and 250
;

--------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |     1 |     4 |   382   (1)| 00:00:05 |       |       |
|   1 |  SORT AGGREGATE           |      |     1 |     4 |            |          |       |       |
|   2 |   PARTITION RANGE ITERATOR|      |   102K|   398K|   382   (1)| 00:00:05 |     1 |     2 |
|*  3 |    TABLE ACCESS FULL      | T1   |   102K|   398K|   382   (1)| 00:00:05 |     1 |     2 |
--------------------------------------------------------------------------------------------------

选择率=(250-150)/1000-1)+2/1000-------------表级别的统计信息
基数  =1000000*选择率=102100

--------------------------不跨分区，但是分区的统计信息缺失
begin
  dbms_stats.delete_table_stats(user, tabname => 't1', partname => 'p0400');
end;
/
explain plan for
select /*+ index_rs(t1) */count(*)
from  t1
where part_col between 250 and 350
;
------------------------------------------------------------------------------------------------
| Id  | Operation               | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |     1 |     4 |   217   (1)| 00:00:03 |       |       |
|   1 |  SORT AGGREGATE         |      |     1 |     4 |            |          |       |       |
|   2 |   PARTITION RANGE SINGLE|      |   102K|   398K|   217   (1)| 00:00:03 |     2 |     2 |
|*  3 |    INDEX RANGE SCAN     | T_I  |   102K|   398K|   217   (1)| 00:00:03 |     2 |     2 |
------------------------------------------------------------------------------------------------
看到了用到了表的统计信息

--------------------------------------看看索引的情况
begin
 dbms_stats.gather_table_stats(
  user,
  't1',
  cascade => true,
  estimate_percent => null,
  method_opt => 'for all columns size 1'
 );
end;
/
create index t_i on t1(PART_COL) local;

------------------不跨分区
explain plan for
select /*+ index_rs(t1) */count(*)
from  t1
where part_col between 250 and 350
;

------------------------------------------------------------------------------------------------
| Id  | Operation               | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |     1 |     4 |   131   (1)| 00:00:02 |       |       |
|   1 |  SORT AGGREGATE         |      |     1 |     4 |            |          |       |       |
|   2 |   PARTITION RANGE SINGLE|      | 61502 |   240K|   131   (1)| 00:00:02 |     2 |     2 |
|*  3 |    INDEX RANGE SCAN     | T_I  | 61502 |   240K|   131   (1)| 00:00:02 |     2 |     2 |
------------------------------------------------------------------------------------------------
select 251*选择率 from dual;-----------------分区级别的统计信息

251*(61502/120000)
------------------
        128.641683
选择率=(350-250)/(399-200)+1/100-------------分区级别统计信息
基数  =120000*选择率=61502
cost  =251*选择率=131

------------------不跨分区，分区统计信息缺失
begin
  dbms_stats.delete_table_stats(user, tabname => 't1', partname => 'p0400');
end;
/

------------------------------------------------------------------------------------------------
| Id  | Operation               | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |     1 |     4 |   217   (1)| 00:00:03 |       |       |
|   1 |  SORT AGGREGATE         |      |     1 |     4 |            |          |       |       |
|   2 |   PARTITION RANGE SINGLE|      |   102K|   398K|   217   (1)| 00:00:03 |     2 |     2 |
|*  3 |    INDEX RANGE SCAN     | T_I  |   102K|   398K|   217   (1)| 00:00:03 |     2 |     2 |
------------------------------------------------------------------------------------------------
选择率=(250-150)/1000-1)+2/1000-------------表级别的统计信息
基数  =1000000*选择率=102100
SELECT 2092*0.1021 from dual;      

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

转载于:http://blog.itpub.net/22034023/viewspace-703788/