看TOM的PPT中有一份演示，其实在LEWIS的COST BASED中也有类似的例子，就是说如果我一个字段是时间类型的，那我可以有很多种变通的存储方式。比如2010年12月1日，可以存储成DATE类型； 也可以存储成字符串20100101；也可以存储成NUMBER类型的20100101。这些存储方式也都可以用来做大小比较，查询等等，有些开发人员确 实有把DATE转化成NUMBER存储的习惯。下面就借用TOM的例子来看看相同的数据，采用不同的类型存储的时候，对优化器以及执行计划（查询效率）的 影响。[@more@]

首先创建一个表，并插入些测试数据（下面代码摘自TOM PPT）：
ops$tkyte%ORA11GR2> create table t ( str_date, date_date, number_date, data )
2 as
3 select to_char( dt+rownum,'yyyymmdd' ) str_date,
4 dt+rownum date_date,
5 to_number( to_char( dt+rownum,'yyyymmdd' ) ) number_date,
6 rpad('*',45,'*') data
7 from (select to_date('01-jan-1995','dd-mon-yyyy') dt
8 from all_objects)
9 order by dbms_random.random
10 /
Table created.

ops$tkyte%ORA11GR2> create index t_str_date_idx on t(str_date);
ops$tkyte%ORA11GR2> create index t_date_date_idx on t(date_date);
ops$tkyte%ORA11GR2> create index t_number_date_idx on t(number_date);
ops$tkyte%ORA11GR2> begin
2 dbms_stats.gather_table_stats
3 ( user, 'T',
4 method_opt=> 'for all indexed columns size 254',
5 cascade=> true );
6 end;
7 /

PL/SQL procedure successfully completed.
这段代码是创建一个表，其中有三个字段，分别是字符串表示的日期、数字表示的日期和日期型表示的日期，然后插入些测试数据，并在每个字段上单独建立索引，并对索引列收集直方图信息。

表中共有记录数
SQL> select count(*) from t;

COUNT(*)
----------
71600


执行一个简单的查询：
SQL> SELECT * FROM T WHERE NUMBER_DATE BETWEEN 20001231 AND 20010101;

STR_DATE DATE_DATE NUMBER_DATE DATA
-------- --------- ----------- ---------------------------------------------
20001231 31-DEC-00 20001231 *********************************************
20010101 01-JAN-01 20010101 *********************************************


Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 258 | 12126 | 202 (1)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| T | 258 | 12126 | 202 (1)| 00:00:03 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("NUMBER_DATE"<=20010101 AND "NUMBER_DATE">=20001231)
表一共7万多条记录，查询只返回两条数据，应该走索引的效率更高，可是这里却走了一个全表扫描，而且可以看到ROWS那里标识出返回结果有253行，这说明CBO估算的时候出现了严重的不准确，下面来看看这个不准确的数字是怎么计算出来的。

ORACLE收集直方图的时候，有一个BUCKET的概念，就是前面收集统计信息的时候指定的for all indexed columns size 254，254表示使用254个BUCKET去收集直方图，BUCKET的取值范围为0到254，不指定值的时候默认值为75。直方图的信息可以从DBA_TAB_HISTOGRAMS中查看：
SQL> SELECT *
2 FROM DBA_TAB_HISTOGRAMS
3 WHERE TABLE_NAME = 'T'
4 AND COLUMN_NAME = 'NUMBER_DATE' AND ROWNUM<=2;

OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALUE
------- ------------- ------------- --------------- -------------- ---------------------
SYS T NUMBER_DATE 0 19950121
SYS T NUMBER_DATE 1 19951003
使用254个BUCKET的时候，这里会有255行数据来表示254个区间，每个区间也就是一个BUCKET，每个BUCKET的区间起始值可以根据ENDPOINT_VALUE来划定。划分BUCKET的原则就是等分法，划完后落到每个BUCKET的记录总数是相等的。所以，如果某个区间的值比较少，那么这个BUCKET所跨越的取值区间机会比较大；反过来如果某一个BUCKET中的值非常多，那么这个BUCKET的取值区间就会窄，从而保证了最终落到每个BUCKET中的记录总数是相同的。


我们可以使用下面的SQL来查看BUCKET的分布情况：
SQL> SELECT ROWNUM TENTH,
2 PREV LOW_VAL,
3 CURR HIGH_VAL,
4 CURR - PREV WIDTH,
5 ROUND(282 / (CURR - PREV), 2) HEIGTH
6 FROM (SELECT ENDPOINT_VALUE CURR,
7 LAG(ENDPOINT_VALUE, 1) OVER(ORDER BY ENDPOINT_NUMBER) PREV
8 FROM DBA_TAB_HISTOGRAMS
9 WHERE TABLE_NAME = 'T'
10 AND COLUMN_NAME = 'NUMBER_DATE')
11 WHERE PREV IS NOT NULL
12 ORDER BY CURR;
13 /

TENTH LOW_VAL HIGH_VAL WIDTH HEIGTH
---------- ---------- ---------- ---------- ----------
1 19950121 19951003 882 0.32
2 19951003 19960621 9618 0.03
3 19960621 19970311 9690 0.03
4 19970311 19971104 793 0.36
5 19971104 19980712 9608 0.03
6 19980712 19990521 9809 0.03
7 19990521 20000316 9795 0.03
8 20000316 20001011 695 0.41
9 20001011 20010706 9695 0.03
10 20010706 20020331 9625 0.03
11 20021226 20030813 9587 0.03
12 20030813 20040718 9905 0.03
13 20040718 20050902 10184 0.03
节约篇幅，只取了BUCKET值从小到大的前几个BUCKET，其中的TENTH表示第几个BUCKET，LOW_VAL和HIGH_VAL表示这个BUCKET的最小和最大值，WIDTH表示这个BUCKET的取值跨度，HEIGTH表示BUCKET的高度，也就是平均下来的话，在这个BUCKET中随便选一个值，那么这个值占这个BUCKET中所有的取值的比值，也可以认为是可选择度。比如第一个BUCKET的HEIGTH为0.03，表示如果在区间19950103到19960621内取100个值的话，平均下来会有3个值是在表T中的。（上面计算公式中的282是表的记录总数71600/bucket数量的结果，也就是说每个BUCKET中包含了282条记录。这个BUCKET中一共有9618个取值的可能，但是一共只有282条记录，所以区间内随便取一个值的话，这个值可能在表中的概率是0.03）


下面来看看表的返回的ROWS是如何计算的，公司如下：
ROWS=SELECTIVITY*NUMBER OF ROWS IN A BUCKET=((REQUIRED RANGE)/(HIGH_VAL-LOW_VAL)+2*DENSITY)*NUMBER OF ROWS IN A BUCKET
其中，NUMBER OF ROWS IN A BUCKET就是上面的总记录数除以收集直方图时的BUCKET的数量(其中BUCKET的数量可以从DBA_TAB_COLUMNS.NUM_BUCKETS得到)。
REQUIRED RANGE表示的是查询条件的范围，如果范围跨越了多个BUCKET，则要分别计算每个范围对应的RANGE，并且去跟相应的BUCKET的HIGH_VAL和LOW_VAL做计算，然后再把计算结果全部相加。
HIGH_VAL和LOW_VAL表示查询范围所对应的BUCKET的区间值
DENSITY取自DBA_TAB_COLUMNS.DENSITY，如果没有直方图的时候，这个值就等于1/COUNT(DISTINCT COL)；有直方图的时候，这里的值会有相应的变化。（如果不是范围查询而是等值查询，那么这里的ROWS将会是COUNT*DENSITY，也就是表的记录总数*DENSITY）

根据上面的计算公式，我们来计算下返回的ROWS:
SQL> SELECT *
2 FROM (SELECT ROWNUM TENTH,
3 PREV LOW_VAL,
4 CURR HIGH_VAL,
5 CURR - PREV WIDTH,
6 ROUND(282 / (CURR - PREV), 2) HEIGTH
7 FROM (SELECT ENDPOINT_VALUE CURR,
8 LAG(ENDPOINT_VALUE, 1) OVER(ORDER BY ENDPOINT_NUMBER) PREV
9 FROM DBA_TAB_HISTOGRAMS
10 WHERE TABLE_NAME = 'T'
11 AND COLUMN_NAME = 'NUMBER_DATE')
12 WHERE PREV IS NOT NULL
13 ORDER BY CURR)
14 WHERE LOW_VAL <= 20001231
15 AND HIGH_VAL >= 20010101;
16 /

TENTH LOW_VAL HIGH_VAL WIDTH HEIGTH
---------- ---------- ---------- ---------- ----------
9 20001011 20010706 9695 0.03
上面的查询只落在一个BUCKET中，那么：
ROWS=((20010101-20001231)/(20010706-20001011)+2*0.00000142120148373435)*282=258.010941553424
结果近似于258，既然有258条结果返回，那么相应的走索引然后再回表取数据的COST计算就增加了，导致ORACLE会选择一个全表扫描来得到结果。

而这个执行计划就是不正确的，那么在字符型存储的日期类型中也有类似的问题，唯一不同的是字符型没有这么直观，它的BUCKET区间是通过字符转换然后存在HISTOGRAMS的,这个转换有计算公式的:
1、首先把字符串转换成16进制，如果转换后的长度小于15字节，则右边用0补齐到15个字节；如果转换后长度大于15字节，则只取前15个字节
2、将这个16进制数字转换成十进制数据，并只取前15位，然后右边补齐到36位就可以得到最终的BUCKET的ENDPOINT_VALUE了
公式如下：
SELECT RPAD(SUBSTR(TO_NUMBER(RPAD(UTL_RAW.CAST_TO_RAW('str'), 30, '0'),
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'),
1,
15),
36,
'0')
FROM DUAL;
转换后，输入的查询条件对应的值分别为：
SQL> SELECT RPAD(SUBSTR(TO_NUMBER(RPAD(UTL_RAW.CAST_TO_RAW('20001231'), 30, '0'),
2 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'),
3 1,
4 15),
5 36,
6 '0') AS START_DATE,
7 RPAD(SUBSTR(TO_NUMBER(RPAD(UTL_RAW.CAST_TO_RAW('20010101'), 30, '0'),
8 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'),
9 1,
10 15),
11 36,
12 '0') AS END_DATE
13 FROM DUAL
14 ;

START_DATE END_DATE
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
260592216454272000000000000000000000 260592216762543000000000000000000000

拿这两个数值去HISTOGRAMS中查询，结果如下：
SQL> SELECT TENTH, TO_CHAR(LOW_VAL), TO_CHAR(HIGH_VAL), TO_CHAR(WIDTH), HEIGTH
2 FROM (SELECT ROWNUM TENTH,
3 PREV LOW_VAL,
4 CURR HIGH_VAL,
5 CURR - PREV WIDTH,
6 ROUND(282 / (CURR - PREV), 2) HEIGTH
7 FROM (SELECT ENDPOINT_VALUE CURR,
8 LAG(ENDPOINT_VALUE, 1) OVER(ORDER BY ENDPOINT_NUMBER) PREV
9 FROM DBA_TAB_HISTOGRAMS
10 WHERE TABLE_NAME = 'T'
11 AND COLUMN_NAME = 'STR_DATE')
12 WHERE PREV IS NOT NULL
13 ORDER BY CURR)
14 WHERE LOW_VAL <= 260592216454272000000000000000000000
15 AND HIGH_VAL >= 260592216762543000000000000000000000;

TENTH TO_CHAR(LOW_VAL) TO_CHAR(HIGH_VAL) TO_CHAR(WIDTH) HEIGTH
---------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ----------
9 260592216454263000000000000000000000 260592216762572000000000000000000000 308309000000000000000000000 0
可以看到，这个查询范围是落在第九个BUCKET中的，而且只落在这一个BUCKET中，可以计算返回的ROWS：
SQL> SELECT ((260592216762543000000000000000000000 -
2 260592216454272000000000000000000000) /
3 (260592216762572000000000000000000000 -
4 260592216454263000000000000000000000) + 2 * .0000139134306345916) * 282
5 FROM DUAL
6 ;

((2605922167625430000000000000
------------------------------
281.973089837272
大约282行，而实际的执行计划如下：
SQL> SELECT * FROM T WHERE STR_DATE BETWEEN '20001231' AND '20010101';

STR_DATE DATE_DATE NUMBER_DATE DATA
-------- --------- ----------- ---------------------------------------------
20001231 31-DEC-00 20001231 *********************************************
20010101 01-JAN-01 20010101 *********************************************


Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 258 | 12126 | 202 (1)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| T | 258 | 12126 | 202 (1)| 00:00:03 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("STR_DATE"<='20010101' AND "STR_DATE">='20001231')
这当中是有出入的，不知道是否是因为舍去了些数据精度导致的。但是HISTOGRAM中有一列叫ENDPOINT_ACTUAL_VALUE的，里面存放了实际的没有经过转换的原始的字符串的上下限，把这个当做NUMBER去计算的话，是能得到精确的返回258行结果的。




但是如果使用日期型，就没有这个问题了。上面问题的发生主要是因为业务意义上的值是连续的，但是因为类型选择问题，ORACLE认为这个值是不连续的，这两个值中间有超级多的可选择的值，所以导致ORACLE认为这样一个范围扫描的成本是很高的。那么如果正确的使用日期类型来存储，得到的直方图如下：
SQL> SELECT ROWNUM TENTH,
2 PREV LOW_VAL,
3 CURR HIGH_VAL,
4 CURR - PREV WIDTH,
5 ROUND(282 / (CURR - PREV), 2) HEIGTH
6 FROM (SELECT ENDPOINT_VALUE CURR,
7 LAG(ENDPOINT_VALUE, 1) OVER(ORDER BY ENDPOINT_NUMBER) PREV
8 FROM DBA_TAB_HISTOGRAMS
9 WHERE TABLE_NAME = 'T'
10 AND COLUMN_NAME = 'DATE_DATE')
11 WHERE PREV IS NOT NULL
12 ORDER BY CURR;

TENTH LOW_VAL HIGH_VAL WIDTH HEIGTH
---------- ---------- ---------- ---------- ----------
1 2449739 2449994 255 1.11
2 2449994 2450256 262 1.08
3 2450256 2450519 263 1.07
4 2450519 2450757 238 1.18
5 2450757 2451007 250 1.13
6 2451007 2451320 313 0.9
7 2451320 2451620 300 0.94
8 2451620 2451829 209 1.35
9 2451829 2452097 268 1.05
10 2452097 2452365 268 1.05
11 2452365 2452736 371 0.76
12 2452736 2452956 220 1.28
其中的BUCKET是转换后的时间，使用的是JULIAN（January 1, 4712 BC开始到现在经过的天数）时间，这种时间格式和我们常见的年月日的转换方式如下：
SQL> SELECT TO_NUMBER(TO_CHAR(SYSDATE, 'J')) FROM DUAL;

TO_NUMBER(TO_CHAR(SYSDATE,'J')
------------------------------
2455216
上面是转换当前时间到JULIAN时间，反过来，如果知道一个JULIAN时间，也可以得到年月日时间：
SQL> select to_date(2455216,'J') from dual
2 /

TO_DATE(2455216,'J')
--------------------
2010-1-19

这样转换后，20001231和20010101之间的差值将不会是之前的20010101-20001231=8870，而是差值只有1，所以CBO计算返回的ROWS也会非常精确，从而使得ORACLE选择更准确的执行计划，下面来验证下：
SQL> SELECT TO_NUMBER(TO_CHAR(TO_DATE('20001231', 'yyyymmdd'), 'J')) AS START_DATE,
2 TO_NUMBER(TO_CHAR(TO_DATE('20010101', 'yyyymmdd'), 'J')) AS END_DATE
3 FROM DUAL;

START_DATE END_DATE
---------- ----------
2451910 2451911

首先得到两个日期范围的取值在HISTOGRAM中对应的值.
SQL> SELECT TENTH, TO_CHAR(LOW_VAL), TO_CHAR(HIGH_VAL), TO_CHAR(WIDTH), HEIGTH
2 FROM (SELECT ROWNUM TENTH,
3 PREV LOW_VAL,
4 CURR HIGH_VAL,
5 CURR - PREV WIDTH,
6 ROUND(282 / (CURR - PREV), 2) HEIGTH
7 FROM (SELECT ENDPOINT_VALUE CURR,
8 LAG(ENDPOINT_VALUE, 1) OVER(ORDER BY ENDPOINT_NUMBER) PREV
9 FROM DBA_TAB_HISTOGRAMS
10 WHERE TABLE_NAME = 'T'
11 AND COLUMN_NAME = 'DATE_DATE')
12 WHERE PREV IS NOT NULL
13 ORDER BY CURR)
14 WHERE LOW_VAL <= 2451910
15 AND HIGH_VAL >= 2451911;

TENTH TO_CHAR(LOW_VAL) TO_CHAR(HIGH_VAL) TO_CHAR(WIDTH) HEIGTH
---------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ----------
9 2451829 2452097 268 1.05
然后可以得到这个值落在了第九个BUCKET中，那么下面来计算下返回的ROWS:
SQL> SELECT ((2451911 - 2451910) / (2452097 - 2451829) +
2 2 * 0.0000139134306345916) * 282
3 FROM DUAL
4 ;

((2451911-2451910)/(2452097-24
------------------------------
1.06008598084806
可以看到返回一行数据，那么跟实际执行计划中的对比下：
SQL> SELECT *
2 FROM T
3 WHERE DATE_DATE BETWEEN TO_DATE('20001231', 'yyyymmdd') AND
4 TO_DATE('20010101', 'yyyymmdd');

STR_DATE DATE_DATE NUMBER_DATE DATA
-------- --------- ----------- ---------------------------------------------
20001231 31-DEC-00 20001231 *********************************************
20010101 01-JAN-01 20010101 *********************************************


Execution Plan
----------------------------------------------------------
Plan hash value: 546586007

-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 47 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 47 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_DATE_DATE_IDX | 1 | | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("DATE_DATE">=TO_DATE(' 2000-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
AND "DATE_DATE"<=TO_DATE(' 2001-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
可以看到，手工计算的跟执行计划中的是一致的。



总结：
1、数据类型的选择大有学问
2、字符、数字、日期的直方图计算方式以及直方图对执行计划的影响