sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA >

【学习笔记】Oracle partition分区列字段的选择分析案例

时间:2016-12-03 12:11   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 Oracle研究中心学习笔记:分享一篇关于Oracle partition分区表使用的文章,详细分享Oracle分区表列字段的选择的方法。

本站文章除注明转载外,均为本站原创: 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: about partiton column with date or varchar2?


我们可以得出如下几个结论:

1. 当列取值超过范围时,oracle针对该列会使用估算的selectivity.
2. 对于分区表按照时间的分区进行的范围分区,对于分区键,不推荐使用除date或timestamp之外的其他的类型。
3. oracle 优化器在处理date,number和varchar2类型时是不同的。
4. 针对CBO一书,第6章节还需要进行大量的测试,毕竟oracle的cbo算法是在不断的改进。



最近有一个需求需要了解关于分区列字段的选择的问题,下面进行了简单的测试:

SQL> SHOW USER
USER IS "ROGER"

SQL> CREATE TABLE tab1 (id NUMBER PRIMARY KEY, TIME DATE);
TABLE created.

SQL> INSERT INTO tab1 SELECT rownum, created FROM sys.dba_objects;
51138 ROWS created.

SQL> commit;
Commit complete.

SQL>  CREATE TABLE tab2 (id NUMBER PRIMARY KEY, TIME varchar2(12));
TABLE created.

SQL> CREATE TABLE tab3 (id NUMBER PRIMARY KEY, TIME NUMBER);
TABLE created.

SQL> INSERT INTO tab2
  2    SELECT rownum, to_char(created, 'yyyy-mm-dd') FROM sys.dba_objects;

51138 ROWS created.

SQL> INSERT INTO tab3
  2    SELECT rownum,
  3           to_number(to_char(created, 'yyyymmdd')) FROM sys.dba_objects;

51138 ROWS created.

SQL> commit;
Commit complete

SQL> DESC tab1
Name                                      NULL     TYPE
----------------------------------------- -------- ----------------------------
ID                                        NOT NULL NUMBER
TIME                                               DATE

SQL> DESC tab2
Name                                      NULL     TYPE
----------------------------------------- -------- ----------------------------
ID                                        NOT NULL NUMBER
TIME                                               VARCHAR2(12)

SQL> DESC tab3
Name                                      NULL     TYPE
----------------------------------------- -------- ----------------------------
ID                                        NOT NULL NUMBER
TIME                                               NUMBER

SQL> analyze TABLE tab1 compute statistics;
TABLE analyzed.

SQL> analyze TABLE tab2 compute statistics;
TABLE analyzed.

SQL> analyze TABLE tab3 compute statistics;
TABLE analyzed.

SQL> SELECT COUNT(1) FROM tab1 WHERE id=51111;

  COUNT(1)
----------
         1

SQL> SET autot traceonly
SQL> SELECT * FROM tab1
  2   WHERE TIME BETWEEN to_date('2012-07-06','yyyy-mm-dd')
  3   AND to_date('2012-07-07','yyyy-mm-dd');

46 ROWS selected.

Execution Plan
----------------------------------------------------------
Plan hash VALUE: 2211052296

--------------------------------------------------------------------------
| Id  | Operation         | Name | ROWS  | Bytes | Cost (%CPU)| TIME     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   181 |  1991 |    68   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TAB1 |   181 |  1991 |    68   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------

   1 - FILTER("TIME">=TO_DATE(' 2012-07-06 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss') AND "TIME"<=TO_DATE(' 2012-07-07 00:00:00', 'syyyy-mm
-dd

              hh24:mi:ss'))


Statistics
----------------------------------------------------------
        312  recursive calls
          0  db block gets
        225  consistent gets
          0  physical reads
          0  redo SIZE
       1394  bytes sent via SQL*Net TO client
        433  bytes received via SQL*Net FROM client
          5  SQL*Net roundtrips TO/FROM client
          6  sorts (memory)
          0  sorts (disk)
         46  ROWS processed

SQL> SELECT * FROM tab2
  2  WHERE TIME BETWEEN '20120706' AND '20120707';

no ROWS selected

Execution Plan
----------------------------------------------------------
Plan hash VALUE: 2156729920

--------------------------------------------------------------------------
| Id  | Operation         | Name | ROWS  | Bytes | Cost (%CPU)| TIME     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1136 | 15904 |    68   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TAB2 |  1136 | 15904 |    68   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------

   1 - FILTER("TIME">='20120706' AND "TIME"<='20120707')


Statistics
----------------------------------------------------------
        312  recursive calls
          0  db block gets
        253  consistent gets
          0  physical reads
          0  redo SIZE
        325  bytes sent via SQL*Net TO client
        389  bytes received via SQL*Net FROM client
          1  SQL*Net roundtrips TO/FROM client
          6  sorts (memory)
          0  sorts (disk)
          0  ROWS processed

SQL> SELECT * FROM tab3
  2  WHERE TIME BETWEEN 20120706 AND 20120707;

48 ROWS selected.

Execution Plan
----------------------------------------------------------
Plan hash VALUE: 2826512543

--------------------------------------------------------------------------
| Id  | Operation         | Name | ROWS  | Bytes | Cost (%CPU)| TIME     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1152 | 10368 |    34   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TAB3 |  1152 | 10368 |    34   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------

   1 - FILTER("TIME">=20120706 AND "TIME"<=20120707)


Statistics
----------------------------------------------------------
        312  recursive calls
          0  db block gets
        176  consistent gets
          0  physical reads
          0  redo SIZE
       1359  bytes sent via SQL*Net TO client
        433  bytes received via SQL*Net FROM client
          5  SQL*Net roundtrips TO/FROM client
          6  sorts (memory)
          0  sorts (disk)
         48  ROWS processed

我们可以看到,对于date,varchar2和number类型,cbo对于card的计算是有差异的,相比之下,date最小。

下面分别来看下3个sql 的10053 trace:

####### 10053 trace

------------- tab1
Table Stats::
  Table: TAB1  Alias: TAB1
    #Rows: 51138  #Blks:  244  AvgRowLen:  16.00
Index Stats::
  Index: SYS_C006431  Col#: 1
    LVLS: 1  #LB: 95  #DK: 51138  LB/K: 1.00  DB/K: 1.00  CLUF: 126.00
***************************************
SINGLE TABLE ACCESS PATH
  -----------------------------------------
  BEGIN Single Table Cardinality Estimation
  -----------------------------------------
  Column (#2): TIME(DATE)
    AvgLen: 7.00 NDV: 866 Nulls: 0 Density: 0.0011547 Min: 2455303 Max: 2456121
  Table: TAB1  Alias: TAB1    
    Card: Original: 51138  Rounded: 181  Computed: 180.56  Non Adjusted: 180.56
  -----------------------------------------
  END   Single Table Cardinality Estimation
  -----------------------------------------
  Access Path: TableScan
    Cost:  68.45  Resp: 68.45  Degree: 0
      Cost_io: 68.00  Cost_cpu: 13010734
      Resp_io: 68.00  Resp_cpu: 13010734
  Best:: AccessPath: TableScan
         Cost: 68.45  Degree: 1  Resp: 68.45  Card: 180.56  Bytes: 0
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
***********************
Join order[1]:  TAB1[TAB1]#0
***********************
Best so far: Table#: 0  cost: 68.4521  card: 180.5575  bytes: 1991
(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:80000
*********************************
Number of join permutations tried: 1
*********************************
Final - All Rows Plan:  Best join order: 1
  Cost: 68.4521  Degree: 1  Card: 181.0000  Bytes: 1991
  Resc: 68.4521  Resc_io: 68.0000  Resc_cpu: 13010734
  Resp: 68.4521  Resp_io: 68.0000  Resc_cpu: 13010734
kkoipt: Query block SEL$1 (#0)
******* UNPARSED QUERY IS *******
SELECT "TAB1"."ID" "ID","TAB1"."TIME" "TIME" FROM "ROGER"."TAB1" "TAB1" WHERE "TAB1"."TIME">=TO_DATE(' 2012-07-06 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND "TAB1"."TIME"<=TO_DATE(' 2012-07-07 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
kkoqbc-subheap (delete addr=0xb72ebf88, in-use=10140, alloc=10552)
kkoqbc-end
          : call(in-use=12996, alloc=32736), compile(in-use=33796, alloc=38000)
apadrv-end: call(in-use=12996, alloc=32736), compile(in-use=34332, alloc=38000)

sql_id=gjmqcxyagbuq4.
Current SQL statement for this session:
select * from tab1
where time between to_date('2012-07-06','yyyy-mm-dd')
and to_date('2012-07-07','yyyy-mm-dd')

============
Plan Table
============
-------------------------------------+-----------------------------------+
| Id  | Operation          | Name    | Rows  | Bytes | Cost  | Time      |
-------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT   |         |       |       |    68 |           |
| 1   |  TABLE ACCESS FULL | TAB1    |   181 |  1991 |    68 |  00:00:01 |
-------------------------------------+-----------------------------------+


对于tab1:

time列的选择性计算为:card=row_nums * selectivity  =51138 * 1/(866)=59.0508083

SQL> select count(time) from tab1;

COUNT(TIME)
-----------
      51138

SQL> select count(distinct time) from tab1;

COUNT(DISTINCTTIME)
-------------------
                866

SQL> select 1/866 from dual;

     1/866
----------
.001154734

SQL> select 51138 * 1/(866) from dual;

51138*1/(866)
-------------
   59.0508083

—— tab2

QUERY BLOCK TEXT
****************
select * from tab2
where time between '20120706' and '20120707'
*********************
QUERY BLOCK SIGNATURE
*********************
qb name was generated
signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0
  fro(0): flg=0 objn=56298 hint_alias="TAB2"@"SEL$1"
*****************************
SYSTEM STATISTICS INFORMATION
*****************************
  Using NOWORKLOAD Stats
  CPUSPEED: 2398 millions instruction/sec
  IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
  IOSEEKTIM: 10 milliseconds (default is 10)
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: TAB2  Alias: TAB2
    #Rows: 51138  #Blks:  244  AvgRowLen:  19.00
Index Stats::
  Index: SYS_C006428  Col#: 1
    LVLS: 1  #LB: 95  #DK: 51138  LB/K: 1.00  DB/K: 1.00  CLUF: 147.00
***************************************
SINGLE TABLE ACCESS PATH
  -----------------------------------------
  BEGIN Single Table Cardinality Estimation
  -----------------------------------------
  Column (#2): TIME(VARCHAR2)
    AvgLen: 10.00 NDV: 45 Nulls: 0 Density: 0.022222
  Using prorated density: 0.022091 of col #2 as selectivity of out-of-range value pred
  Table: TAB2  Alias: TAB2    
    Card: Original: 51138  Rounded: 1136  Computed: 1136.40  Non Adjusted: 1136.40
  -----------------------------------------
  END   Single Table Cardinality Estimation
  -----------------------------------------
  Access Path: TableScan
    Cost:  68.45  Resp: 68.45  Degree: 0
      Cost_io: 68.00  Cost_cpu: 13044475
      Resp_io: 68.00  Resp_cpu: 13044475
  Best:: AccessPath: TableScan
         Cost: 68.45  Degree: 1  Resp: 68.45  Card: 1136.40  Bytes: 0
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
***********************
Join order[1]:  TAB2[TAB2]#0
***********************
Best so far: Table#: 0  cost: 68.4532  card: 1136.4000  bytes: 15904
(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:80000
*********************************
Number of join permutations tried: 1
*********************************
Final - All Rows Plan:  Best join order: 1
  Cost: 68.4532  Degree: 1  Card: 1136.0000  Bytes: 15904
  Resc: 68.4532  Resc_io: 68.0000  Resc_cpu: 13044475
  Resp: 68.4532  Resp_io: 68.0000  Resc_cpu: 13044475
kkoipt: Query block SEL$1 (#0)
******* UNPARSED QUERY IS *******
SELECT "TAB2"."ID" "ID","TAB2"."TIME" "TIME" FROM "ROGER"."TAB2" "TAB2" WHERE "TAB2"."TIME">='20120706' AND "TAB2"."TIME"<='20120707'
kkoqbc-subheap (delete addr=0xb72ebe20, in-use=10140, alloc=10840)
kkoqbc-end
          : call(in-use=12900, alloc=32736), compile(in-use=33380, alloc=33876)
apadrv-end: call(in-use=12900, alloc=32736), compile(in-use=33916, alloc=38000)

sql_id=f4uv6abzf040v.
Current SQL statement for this session:
select * from tab2
where time between '20120706' and '20120707'

============
Plan Table
============
-------------------------------------+-----------------------------------+
| Id  | Operation          | Name    | Rows  | Bytes | Cost  | Time      |
-------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT   |         |       |       |    68 |           |
| 1   |  TABLE ACCESS FULL | TAB2    |  1136 |   16K |    68 |  00:00:01 |
-------------------------------------+-----------------------------------+


观察这条信息:
Using prorated density: 0.022091 of col #2 as selectivity of out-of-range value pred

重点就是这个density的计算,根据cbo的描述是这样的:

density=(20120707-20120706)/(20120712-20100415)+2/num_distinct
       =1/20297+2/45=.044493713

SQL> select max(time) as max,min(time) as min from tab2;

MAX          MIN
------------ ------------
2012-07-12   2010-04-15

SQL> select count(time) from tab2; 

COUNT(TIME)
-----------
      51138

SQL> select count(distinct time) from tab2;

COUNT(DISTINCTTIME)
-------------------
                 45

这里似乎跟cbo 上面描述的出入,这是正常的,当取值超过范围时,oracle会使用一个估算的selectivity。

—– tab3

***********************
Table Stats::
  Table: TAB3  Alias: TAB3
    #Rows: 51138  #Blks:  118  AvgRowLen:  14.00
Index Stats::
  Index: SYS_C006430  Col#: 1
    LVLS: 1  #LB: 95  #DK: 51138  LB/K: 1.00  DB/K: 1.00  CLUF: 112.00
***************************************
SINGLE TABLE ACCESS PATH
  -----------------------------------------
  BEGIN Single Table Cardinality Estimation
  -----------------------------------------
  Column (#2): TIME(NUMBER)
    AvgLen: 5.00 NDV: 45 Nulls: 0 Density: 0.022222 Min: 20100415 Max: 20120712
  Table: TAB3  Alias: TAB3    
    Card: Original: 51138  Rounded: 1152  Computed: 1151.52  Non Adjusted: 1151.52
  -----------------------------------------
  END   Single Table Cardinality Estimation
  -----------------------------------------
  Access Path: TableScan
    Cost:  34.42  Resp: 34.42  Degree: 0
      Cost_io: 34.00  Cost_cpu: 12148266
      Resp_io: 34.00  Resp_cpu: 12148266
  Best:: AccessPath: TableScan
         Cost: 34.42  Degree: 1  Resp: 34.42  Card: 1151.52  Bytes: 0
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
***********************
Join order[1]:  TAB3[TAB3]#0
***********************
Best so far: Table#: 0  cost: 34.4221  card: 1151.5169  bytes: 10368
(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:80000
*********************************
Number of join permutations tried: 1
*********************************
Final - All Rows Plan:  Best join order: 1
  Cost: 34.4221  Degree: 1  Card: 1152.0000  Bytes: 10368
  Resc: 34.4221  Resc_io: 34.0000  Resc_cpu: 12148266
  Resp: 34.4221  Resp_io: 34.0000  Resc_cpu: 12148266
kkoipt: Query block SEL$1 (#0)
******* UNPARSED QUERY IS *******
SELECT "TAB3"."ID" "ID","TAB3"."TIME" "TIME" FROM "ROGER"."TAB3" "TAB3" WHERE "TAB3"."TIME">=20120706 AND "TAB3"."TIME"<=20120707
kkoqbc-subheap (delete addr=0xb72ebe20, in-use=10140, alloc=10840)
kkoqbc-end
          : call(in-use=12708, alloc=32736), compile(in-use=33380, alloc=33876)
apadrv-end: call(in-use=12708, alloc=32736), compile(in-use=33916, alloc=38000)

sql_id=byy6pnh4g9s49.
Current SQL statement for this session:
select * from tab3
where time between 20120706 and 20120707

============
Plan Table
============
-------------------------------------+-----------------------------------+
| Id  | Operation          | Name    | Rows  | Bytes | Cost  | Time      |
-------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT   |         |       |       |    34 |           |
| 1   |  TABLE ACCESS FULL | TAB3    |  1152 |   10K |    34 |  00:00:01 |
-------------------------------------+-----------------------------------+

对于tab3:

time列的选择性计算为:card=row_nums * selectivity=51138*(1/45)=51338*0.022222

SQL> select 51138*0.022222 from dual;

51138*0.022222
--------------
    1136.38864

可以看到实际上最后的card是1151.52,跟上面的1136有点诧异。

######### 测试分区

SQL> CREATE TABLE tab4 (id NUMBER PRIMARY KEY, TIME DATE);
TABLE created.

SQL> INSERT INTO tab4 SELECT rownum, created FROM sys.dba_objects;
51140 ROWS created.

SQL> commit;
Commit complete.

SQL> CREATE TABLE tab5 (id NUMBER PRIMARY KEY, TIME varchar2(9)) partition BY range (TIME)
  2  (partition p1 VALUES less than ('20111101'),
  3   partition p2 VALUES less than ('20111201'),
  4   partition p3 VALUES less than ('20120101'),
  5   partition p4 VALUES less than ('20120201'),
  6   partition p5 VALUES less than ('20120301'),
  7   partition p6 VALUES less than ('20120401'),
  8   partition p7 VALUES less than ('20120501'),
  9   partition p8 VALUES less than ('20120601'),
10   partition p9 VALUES less than ('20120701'), 
11   partition p10 VALUES less than (maxvalue))
12  /

TABLE created.

SQL> INSERT INTO tab5  SELECT * FROM tab4;

51140 ROWS created.

SQL> commit;
Commit complete.

SQL> CREATE TABLE tab6 (id, TIME) partition BY range (TIME)
  2  (partition p1 VALUES less than (to_date('2011-11-1', 'yyyy-mm-dd')),
  3   partition p2 VALUES less than (to_date('2011-12-1', 'yyyy-mm-dd')),
  4   partition p3 VALUES less than (to_date('2012-1-1', 'yyyy-mm-dd')),
  5   partition p4 VALUES less than (to_date('2012-2-1', 'yyyy-mm-dd')),
  6   partition p5 VALUES less than (to_date('2012-3-1', 'yyyy-mm-dd')),
  7   partition p6 VALUES less than (to_date('2012-4-1', 'yyyy-mm-dd')),
  8   partition p7 VALUES less than (to_date('2012-5-1', 'yyyy-mm-dd')),
  9   partition p8 VALUES less than (to_date('2012-6-1', 'yyyy-mm-dd')),
10   partition p9 VALUES less than (to_date('2012-7-1', 'yyyy-mm-dd')), 
11   partition p10 VALUES less than (maxvalue))
12   AS SELECT id, TIME FROM tab4;

TABLE created.

SQL> EXEC dbms_stats.gather_table_stats('ROGER','TAB5',degree=>2);

PL/SQL PROCEDURE successfully completed.

SQL> EXEC dbms_stats.gather_table_stats('ROGER','TAB6',degree=>2);

PL/SQL PROCEDURE successfully completed.

SQL> DESC tab5
Name                                      NULL     TYPE
----------------------------------------- -------- ----------------------------
ID                                        NOT NULL NUMBER
TIME                                               VARCHAR2(9)

SQL> DESC tab6
Name                                      NULL     TYPE
----------------------------------------- -------- ----------------------------
ID                                                 NUMBER
TIME                                               DATE

SQL> ALTER system FLUSH shared_pool;

System altered.

SQL> SET autot traceonly 
SQL> SELECT * FROM tab6
  2   WHERE TIME BETWEEN to_date('2012-07-06','yyyy-mm-dd')
  3   AND to_date('2012-07-07','yyyy-mm-dd');

46 ROWS selected.

Execution Plan
----------------------------------------------------------
Plan hash VALUE: 2102902811

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | ROWS  | Bytes | Cost (%CPU)| TIME     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |    30 |   360 |     3   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|      |    30 |   360 |     3   (0)| 00:00:01 |    10 |    10 |
|*  2 |   TABLE ACCESS FULL    | TAB6 |    30 |   360 |     3   (0)| 00:00:01 |    10 |    10 |
-----------------------------------------------------------------------------------------------

Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------

   2 - FILTER("TIME"<=TO_DATE(' 2012-07-07 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "TIME">=TO_DATE(' 2012-07-06 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))


Statistics
----------------------------------------------------------
       1885  recursive calls
          0  db block gets
        345  consistent gets
          9  physical reads
          0  redo SIZE
       1394  bytes sent via SQL*Net TO client
        433  bytes received via SQL*Net FROM client
          5  SQL*Net roundtrips TO/FROM client
         43  sorts (memory)
          0  sorts (disk)
         46  ROWS processed

SQL> SELECT * FROM tab5
  2  WHERE TIME BETWEEN '20120706' AND '20120707';

no ROWS selected

Execution Plan
----------------------------------------------------------
Plan hash VALUE: 2465561023

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | ROWS  | Bytes | Cost (%CPU)| TIME     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |     7 |    98 |     3   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|      |     7 |    98 |     3   (0)| 00:00:01 |    10 |    10 |
|*  2 |   TABLE ACCESS FULL    | TAB5 |     7 |    98 |     3   (0)| 00:00:01 |    10 |    10 |
-----------------------------------------------------------------------------------------------

Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------

   2 - FILTER("TIME"<='20120707' AND "TIME">='20120706')

Statistics
----------------------------------------------------------
        577  recursive calls
          0  db block gets
        107  consistent gets
          0  physical reads
          0  redo SIZE
        325  bytes sent via SQL*Net TO client
        389  bytes received via SQL*Net FROM client
          1  SQL*Net roundtrips TO/FROM client
          7  sorts (memory)
          0  sorts (disk)
          0  ROWS processed

SQL> SELECT *
  2    FROM tab5
  3   WHERE to_date(TIME,'dd-mon-yyyy') BETWEEN
  4         to_date('06-jul-2012','dd-mon-yyyy') AND
  5         to_date('07-jul-2012','dd-mon-yyyy');

no ROWS selected

Execution Plan
----------------------------------------------------------
Plan hash VALUE: 317698379

--------------------------------------------------------------------------------------------
| Id  | Operation           | Name | ROWS  | Bytes | Cost (%CPU)| TIME     | Pstart| Pstop |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |   129 |  1806 |    70   (2)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ALL|      |   129 |  1806 |    70   (2)| 00:00:01 |     1 |    10 |
|*  2 |   TABLE ACCESS FULL | TAB5 |   129 |  1806 |    70   (2)| 00:00:01 |     1 |    10 |
--------------------------------------------------------------------------------------------

Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------

   2 - FILTER(TO_DATE("TIME",'dd-mon-yyyy')>=TO_DATE(' 2012-07-06 00:00:00',
          Oracleoracleplus.net    'syyyy-mm-dd hh24:mi:ss') AND TO_DATE("TIME",'dd-mon-yyyy')<=TO_DATE(' 2012-07-07
              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        221  consistent gets
          0  physical reads
          0  redo SIZE
        325  bytes sent via SQL*Net TO client
        389  bytes received via SQL*Net FROM client
          1  SQL*Net roundtrips TO/FROM client
          0  sorts (memory)
          0  sorts (disk)
          0  ROWS processed

我们知道between and是等价于>= and <= 的,其计算selectivity的公式如下:

selectivity=(high_limit – low_limit)/(high_value – low_value)+1/num_distinct+1/num_distinct


上面的3个sql查询,我们发现根据这个公式去计算存在一定的差异。

虽然从test来看,似乎有些失败,但是至少我们可以得出如下几个结论:

1. 当列取值超过范围时,oracle针对该列会使用估算的selectivity.
2. 对于分区表按照时间的分区进行的范围分区,对于分区键,不推荐使用除date或timestamp之外的其他的类型。
3. oracle 优化器在处理date,number和varchar2类型时是不同的。
4. 针对CBO一书,第6章节还需要进行大量的测试,毕竟oracle的cbo算法是在不断的改进。

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle partition分区列字段的选择分析案例

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1356.html

Oracle研究中心

关键词:

Oracle partition详解

Oracle 分区列字段的选择方法