oracle實驗記錄 （分割槽表,掃描基數的計算）

關於分割槽 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計算 會在以後有空的時候 實驗記錄下

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-608596/，如需轉載，請註明出處，否則將追究法律責任。