验证where条件中类型隐式转换问题

当列的类型为date时，判断值为字符串类型时，判断值会隐式转换为date类型，即列的类型。当列的类型为varchar2类型时，判断值为数值型数据类型时，oracle会隐式的把varchar2列的数据类型转换成判断值的数值型数据类型。如下：

scott表emp的结构如下：

SQL> desc emp;
Name     Type         Nullable Default Comments
-------- ------------ -------- ------- --------
EMPNO    NUMBER(4)                             
ENAME    VARCHAR2(10) Y                        
JOB      VARCHAR2(9)  Y                        
MGR      NUMBER(4)    Y                        
HIREDATE DATE         Y                        
SAL      NUMBER(7,2)  Y                        
COMM     NUMBER(7,2)  Y                        
DEPTNO   NUMBER(2)    Y   

 

其中hiredate列为date类型：

验证如下：

SQL> explain plan for select empno,ename,deptno from emp
  2  where hiredate>'1987/4/19' and hiredate<'1988/4/19';
 
Explained
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 381046100
--------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Rows  | Bytes | Cost (
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |     1 |    38 |     2
|*  1 |  FILTER                      |                  |       |       |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP              |     1 |    38 |     2
|*  3 |    INDEX RANGE SCAN          | IDX_HIREDATE_EMP |     1 |       |     1
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(TO_DATE('1987/4/19')<TO_DATE('1988/4/19'))
   3 - access("HIREDATE">'1987/4/19' AND "HIREDATE"<'1988/4/19')
 
16 rows selected         

 

从输入结果中可以知道，是判断值隐式转换为列的类型，然后再进行比较。不是把列的类型转换为判断值的类型，如字符串类型

 

SQL> desc emp_01;
Name     Type         Nullable Default Comments
-------- ------------ -------- ------- --------
EMPNO    NUMBER(4)    Y                        
ENAME    VARCHAR2(10) Y                        
JOB      VARCHAR2(9)  Y                        
MGR      NUMBER(4)    Y                        
HIREDATE DATE         Y                        
SAL      NUMBER(7,2)  Y                        
COMM     NUMBER(7,2)  Y                        
DEPTNO   NUMBER(2)    Y         

 

SQL> explain plan for select * from emp_01 where ename=100;
 
Explained
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2264908832
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |     3 |   261 |     4   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP_01 |     3 |   261 |     4   (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(TO_NUMBER("ENAME")=100)
 
13 rows selected
 

从上面红色标志中可以看出，列的类型转换为数值型类型。为什么转换函数不加到100上面呢，因为这是一个常量，常量是不能改变的。