字符类型存数字及查询数字时使用单引号走不走索引的问题
Aug182015
新上线的系统很多数字类型的字段都是使用varchar2类型存放,要转换成number类型时,和开发人员对number类型的字段在查询时加上单引号走不走索引的问题产生了分歧,大家都知道,如果使用char类型存放数字,在查询时如果不加单引号是不会走索引的,测试信息如下,数据库版本11.2.0.4.0。
创建测试表如下。
SQL> conn dbdream/dbdream Connected. SQL> SQL> create table ind_text as select object_id,object_name from dba_objects; Table created. SQL> ALTER TABLE IND_TEXT ADD OBJECT_CHAR VARCHAR2(22); Table altered. SQL> UPDATE IND_TEXT SET OBJECT_CHAR=OBJECT_ID; 90743 rows updated. SQL> COMMIT; Commit complete. SQL> ALTER TABLE IND_TEXT MOVE; Table altered.
创建索引。
SQL> create index ind_OBJECT_ID on ind_text(OBJECT_ID); Index created. SQL> create index ind_OBJECT_NAME on ind_text(OBJECT_NAME); Index created. SQL> CREATE INDEX IND_OBJECT_CHAR ON IND_TEXT(OBJECT_CHAR); Index created.
现在OBJECT_ID字段是用number类型存放数字,OBJECT_CHAR字段使用varchar2类型存放数字,通过下面的测试可以看到如果查询OBJECT_CHAR字段字段,不加单引号是不会走索引的。
SQL> SELECT OBJECT_CHAR,OBJECT_NAME FROM IND_TEXT WHERE OBJECT_CHAR='100832'; OBJECT_CHAR OBJECT_NAME -------------------- ------------------ 100832 ORD_ORD_DTL_D Execution Plan ---------------------------------------------------------- Plan hash value: 1825303736 ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 78 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| IND_TEXT | 1 | 78 | 1 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IND_OBJECT_CHAR | 1 | | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------- SQL> SELECT OBJECT_CHAR,OBJECT_NAME FROM IND_TEXT WHERE OBJECT_CHAR=100832; OBJECT_CHAR OBJECT_NAME -------------------- -------------------- 100832 ORD_ORD_DTL_D Execution Plan ---------------------------------------------------------- Plan hash value: 2154511419 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 6 | 468 | 143 (3)| 00:00:02 | |* 1 | TABLE ACCESS FULL| IND_TEXT | 6 | 468 | 143 (3)| 00:00:02 | ------------------------------------------------------------------------------
那么到底查询用number类型存放的数字,查询时不加单引号肯定是走索引的,加上单引号会不会走索引呢,看下面的测试。
SQL> select OBJECT_NAME,OBJECT_ID from ind_text where OBJECT_ID=100832; OBJECT_NAME OBJECT_ID -------------- ----------- ORD_ORD_DTL_D 100832 Execution Plan ---------------------------------------------------------- Plan hash value: 417862871 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 79 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| IND_TEXT | 1 | 79 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IND_OBJECT_ID | 1 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------------- SQL> select OBJECT_NAME,OBJECT_ID from ind_text where OBJECT_ID='100832'; OBJECT_NAME OBJECT_ID -------------- ---------- ORD_ORD_DTL_D 100832 Execution Plan ---------------------------------------------------------- Plan hash value: 417862871 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 79 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| IND_TEXT | 1 | 79 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IND_OBJECT_ID | 1 | | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------
可见如果使用number类型存放数字,不论查询时加不加单元号都会走索引的。
总结:如果使用字符类型存放数字,只有在查询时使用单引号将数字引起来才会使用索引;如果是使用数字类型存放数字,查询时不管是否使用单引号,都会走索引。
【上一篇】OGG pump进程状态正常,trail文件无法发送到目标端
【下一篇】ORA-19606 Cannot copy or restore to snapshot control file错误解决方法
【下一篇】ORA-19606 Cannot copy or restore to snapshot control file错误解决方法