最近有一个需求需要了解关于分区列字段的选择的问题，下面进行了简单的测试：
我们可以看到，对于date，varchar2和number类型，cbo对于card的计算是有差异的，相比之下，date最小。
下面分别来看下3个sql 的10053 trace：
####### 10053 trace
------ tab2
这里似乎跟cbo 上面描述的出入，这是正常的，当取值超过范围时，
oracle会使用一个估算的selectivity。
----- tab3
######### 测试分区
我们知道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算法是在不断的改进。
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>
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>
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 ({39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}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 ({39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}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 ({39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}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>
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>
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>
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>
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 ({39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}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 ({39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}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 ({39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}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',
'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算法是在不断的改进。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
