关于分区 card
今天pub上搜索分区 ,无意中发现一个帖子
http://www.itpub.net/thread-1006757-1-1.html  08年的

问
有2种查询方式
select * from t partition(p1);
select * from t where t.parttition_cd=1
 以上查询都在一个分区中,cost一样但card不一样 是为什么

原因是card的计算问题
特此实验并记录
create table t1 太长不贴全了 (表是根据 基于成本优化书中的一个脚本,表比较大方便分析分区)
   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)
)

SQL> execute dbms_stats.gather_table_stats('SYS','T1');~收集下信息

PL/SQL procedure successfully completed.
 
查看一些T1的基本信息
SQL> select table_name,num_rows from user_tables where table_name='T1';

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
T1                                1003670

SQL> select column_name ,num_distinct,density ,num_nulls from user_tab_col_sta
stics where table_name='T1';

COLUMN_NAME                    NUM_DISTINCT    DENSITY  NUM_NULLS
------------------------------ ------------ ---------- ----------
PART_COL                                911 .001097695          0
ID                                  1003670 9.9634E-07          0
SMALL_VC                            1003670 9.9634E-07          0
PADDING                                   1          1          0
SQL> selEct partition_name,num_rows from user_tab_partitions where table_name='T
1';

PARTITION_NAME                   NUM_ROWS
------------------------------ ----------
P0200                               40000*******************
P0400                              119071
P0600                              200286
P0800                              279693
P1000                              360246

SQL> select PARTITION_NAME,COLUMN_NAME,NUM_DISTINCT,DENSITY,NUM_NULLS from  USER
_PART_COL_STATISTICS where table_name='T1';

PARTIT COLUMN_N NUM_DISTINCT    DENSITY  NUM_NULLS
------ -------- ------------ ---------- ----------
P0200  PART_COL          200       .005          0***********************
P0200  ID              40000    .000025          0
P0200  SMALL_VC        40000    .000025          0
P0200  PADDING             1          1          0
P0400  PART_COL          201 .004975124          0
P0400  ID             119071 8.3984E-06          0
P0400  SMALL_VC       119071 8.3984E-06          0
P0400  PADDING             1          1          0
P0600  PART_COL          201 .004975124          0
P0600  ID             200286 4.9929E-06          0
P0600  SMALL_VC       200286 4.9929E-06          0

PARTIT COLUMN_N NUM_DISTINCT    DENSITY  NUM_NULLS
------ -------- ------------ ---------- ----------
P0600  PADDING             1          1          0
P0800  PART_COL          200       .005          0
P0800  ID             279693 3.5753E-06          0
P0800  SMALL_VC       279693 3.5753E-06          0
P0800  PADDING             1          1          0
P1000  PART_COL          200       .005          0
P1000  ID             360246 2.7759E-06          0
P1000  SMALL_VC       360246 2.7759E-06          0
P1000  PADDING             1          1          0

20 rows selected.

SQL> explain plan for select * from t1 partition(P0200);

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 2602558080

-------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost  | Pstart| Pstop |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      | 40000 |  2734K|    42 |       |       |
|   1 |  PARTITION RANGE SINGLE|      | 40000 |  2734K|    42 |     1 |     1 |
|   2 |   TABLE ACCESS FULL    | T1   | 40000 |  2734K|    42 |     1 |     1 |
-------------------------------------------------------------------------------

Note

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

-----
   - cpu costing is off (consider enabling it)

13 rows selected.
没有过滤谓词  Pstart,Pstop 都为1,在一个分区中 rows=p0200分区的num_rows=40000

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL> explain plan for select * from t1 where part_col=2;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 2602558080

-------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost  | Pstart| Pstop |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |   200 | 14000 |    42 |       |       |
|   1 |  PARTITION RANGE SINGLE|      |   200 | 14000 |    42 |     1 |     1 |
|*  2 |   TABLE ACCESS FULL    | T1   |   200 | 14000 |    42 |     1 |     1 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

---------------------------------------------------

   2 - filter("PART_COL"=2)

过滤谓词 "PART_COL"=2  rows=该分区的num_rows*1/该分区的num_distinct(是该分区的)=40000*1/200
=200

实际情况 复杂的多得多,跨越分区的card计算 会在以后有空的时候 实验记录下