字符类型存数字查询中可能出现的问题:所有数据都是数字则查询参数可以不加引号可以查询,但查询效率不高因为不走索引。如果该字段中的数据不都是数字则参数必须加索引,否则查不出结果。总结下来就是,查询字符型字段时候参数最好都加索引。
oracle字符型数值不加引号,字符类型存数字及查询数字时使用单引号走不走索引的问题 | 信春哥,系统稳,闭眼上线不回滚!..._weixin_39608398的博客-CSDN博客
新上线的系统很多数字类型的字段都是使用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类型存放数字,不论查询时加不加单元号都会走索引的。
总结:如果使用字符类型存放数字,只有在查询时使用单引号将数字引起来才会使用索引;如果是使用数字类型存放数字,查询时不管是否使用单引号,都会走索引。