当前位置: 首页 > Oracle, oracle 10g, oracle 11g > 正文

字符类型存数字及查询数字时使用单引号走不走索引的问题

新上线的系统很多数字类型的字段都是使用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类型存放数字,不论查询时加不加单元号都会走索引的。

总结:如果使用字符类型存放数字,只有在查询时使用单引号将数字引起来才会使用索引;如果是使用数字类型存放数字,查询时不管是否使用单引号,都会走索引。

本文固定链接: http://www.dbdream.com.cn/2015/08/%e5%ad%97%e7%ac%a6%e7%b1%bb%e5%9e%8b%e5%ad%98%e6%95%b0%e5%ad%97%e5%8f%8a%e6%9f%a5%e8%af%a2%e6%95%b0%e5%ad%97%e6%97%b6%e4%bd%bf%e7%94%a8%e5%8d%95%e5%bc%95%e5%8f%b7%e8%b5%b0%e4%b8%8d%e8%b5%b0%e7%b4%a2/ | 信春哥,系统稳,闭眼上线不回滚!

该日志由 dbdream 于2015年08月18日发表在 Oracle, oracle 10g, oracle 11g 分类下, 你可以发表评论,并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: 字符类型存数字及查询数字时使用单引号走不走索引的问题 | 信春哥,系统稳,闭眼上线不回滚!
关键字: , , ,

字符类型存数字及查询数字时使用单引号走不走索引的问题:等您坐沙发呢!

发表评论

快捷键:Ctrl+Enter