今天在写SELECT语句的时候发现始终差不出来等于某个日期的数据,后来才发现是ORACLE内部发生的转化。
3、像碰到这样的问题,我首先想到的是查看执行计划
"T_DATE"=TO_DATE(' 2014-11-28 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
废话不多说,直接看示例。
1、构建数据
show parameter nls_date_format
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_date_format string YYYY-MM-DD
DROP TABLE t PURGE;
CREATE TABLE t
AS
SELECT (SYSDATE + LEVEL) AS t_date
FROM dual
CONNECT BY LEVEL < 10;
CREATE INDEX idx_t ON t(t_date);
SELECT * FROM t;
T_DATE
----------
2014-11-28
2014-11-29
2014-11-30
2014-12-01
2014-12-02
2014-12-03
2014-12-04
2014-12-05
2014-12-06
SELECT table_name, column_name, data_type FROM USER_TAB_COLUMNS WHERE table_name = 'T';
TABLE_NAME COLUMN_NAME DATA_TYPE
------------------------------ ------------------------------ --------------------
T T_DATE DATE
由上面可以看出创建了一个表T,其中有一个类型为DATE的T_DATE字段
2、困扰怎么没有数据出来
SELECT * FROM t WHERE t_date='2014-11-28';
no rows selected
难道是没有将2014-11-28转换成DATE类型的原因?
SELECT * FROM t WHERE t_date=TO_DATE('2014-11-28', 'yyyy-mm-dd');
no rows selected
还是没有,真奇怪!
3、像碰到这样的问题,我首先想到的是查看执行计划
以上两种查询方法建议使用第二种,它使用了显示类型转化,更具有可读性,等让人明白。
SET AUTOTRACE ON EXPLAIN
SELECT * FROM t WHERE t_date=TO_DATE('2014-11-28', 'yyyy-mm-dd');
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 2296882198
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_T | 1 | 9 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T_DATE"=TO_DATE(' 2014-11-28 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
Note
-----
- dynamic sampling used for this statement (level=2)
通过上面的执行计划,可以看到ORACLE自动发生了DATE -> TIMESTAMP的转化:
"T_DATE"=TO_DATE(' 2014-11-28 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
4、所以我们要查询出相关时间只能是另寻他法了。
SELECT * FROM t
WHERE t_date >= TO_DATE('2014-11-28', 'yyyy-mm-dd') AND
t_date < TO_DATE('2014-11-29', 'yyyy-mm-dd');
T_DATE
----------
2014-11-28
Execution Plan
----------------------------------------------------------
Plan hash value: 2296882198
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_T | 1 | 9 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T_DATE">=TO_DATE(' 2014-11-28 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "T_DATE"<TO_DATE(' 2014-11-29 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
Note
-----
- dynamic sampling used for this statement (level=2)
由上看到数据终于查出来了