Oracle数据库设计字段类型选择错误的隐患

Oracle数据库设计字段类型选择错误的隐患

数据类型不准确的一个隐患，下面来构造一张表存日期字段，一个存varchar2，一个存date，做一个测试。之前也写过两篇blog:

1.字段类型设计与实际业务不符引发的问题1

2.字段类型设计与实际业务不符引发的问题2

SQL> drop table test purge;

SQL> create table test as select
to_char(to_date('2014-01-01','yyyy-MM-dd')+rownum,'yyyymmdd') s_date,
to_date('2014-01-01','yyyy-MM-dd')+rownum d_date
from all_objects;

SQL> create index ind_t_sdate on test(s_date) nologging;
SQL> create index ind_t_ddate on test(d_date) nologging;
SQL> exec dbms_stats.gather_table_stats(user,'test',cascade => true);

SQL> set timing on
SQL> set autotrace traceonly
SQL> select * from test where s_date between '20140201' and '20140222';
已选择22行。
已用时间: 00: 00: 00.00
执行计划
----------------------------------------------------------
Plan hash value: 953148778
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 51 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 3 | 51 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_T_SDATE | 3 | | 2 (0)| 00:00:01 |

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

--可以看到CBO评估出来的行数是3，明明返回的是22

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("S_DATE">='20140201' AND "S_DATE"<='20140222')
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
944 bytes sent via SQL*Net to client
349 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
22 rows processed

SQL> select * from test
where d_date between to_date('20140201', 'yyyymmdd') and
to_date('20140222', 'yyyymmdd');
已选择22行。
已用时间: 00: 00: 00.00
执行计划
----------------------------------------------------------
Plan hash value: 112387541
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23 | 391 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 23 | 391 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_T_DDATE | 23 | | 2 (0)| 00:00:01 |

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

--可以看到CBO评估出来基本是准确的。

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("D_DATE">=TO_DATE(' 2014-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
AND "D_DATE"<=TO_DATE(' 2014-02-22 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
944 bytes sent via SQL*Net to client
349 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)

22 rows processed

总结：虽然这两条SQL消耗的资源是一样的，但SQL1的评估结果不对，在多表关联的时候，这个绝对是个隐患，非常容易导致执行计划走错。除了以上的几个原因之外，还存在的问题是用varchar2存date会造成N多的存储格式，曾经看到过一个情况，日期格式五花八门（有年月日，年月日 小时，年月日 小时，分钟），有中英文的:，有全角、半角，有null,甚至undefine(大概是从js传过来的)。且造成索引建了用不上，不得已改数据类型，光写转换的脚本就花了一天多的时间。