技术头条 - 一个快速在微博传播文章的方式     搜索本站
您现在的位置：首页 --> Oracle --> Oracle字符类型存数字及查询数字时使用单引号走不走索引的问题

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

浏览:1046次  出处信息

新上线的系统很多数字类型的字段都是使用varchar2类型存放，要转换成number类型时，和开发人员对number类型的字段在查询时加上单引号走不走索引的问题产生了分歧，大家都知道，如果使用char类型存放数字，在查询时如果不加单引号是不会走索引的，测试信息如下，数据库版本11.2.0.4.0。

创建测试表如下。

01SQL> conn dbdream/dbdream
02Connected.
03SQL>
04
05SQL> create table ind_text as select object_id,object_name from dba_objects;
06
07Table created.
08
09SQL> ALTER TABLE IND_TEXT ADD OBJECT_CHAR VARCHAR2(22);
10
11Table altered.
12
13SQL> UPDATE IND_TEXT SET OBJECT_CHAR=OBJECT_ID;
14
1590743 rows updated.
16
17SQL> COMMIT;
18
19Commit complete.
20
21SQL> ALTER TABLE IND_TEXT MOVE;
22
23Table altered.

创建索引。

01SQL> create index ind_OBJECT_ID on ind_text(OBJECT_ID);
02
03Index created.
04
05SQL> create index ind_OBJECT_NAME on ind_text(OBJECT_NAME);
06
07Index created.
08
09SQL> CREATE INDEX IND_OBJECT_CHAR ON IND_TEXT(OBJECT_CHAR);
10
11Index created.

现在OBJECT_ID字段是用number类型存放数字，OBJECT_CHAR字段使用varchar2类型存放数字，通过下面的测试可以看到如果查询OBJECT_CHAR字段字段，不加单引号是不会走索引的。

01SQL> SELECT OBJECT_CHAR,OBJECT_NAME FROM IND_TEXT WHERE OBJECT_CHAR='100832';
02
03OBJECT_CHAR          OBJECT_NAME
04-------------------- ------------------
05100832               ORD_ORD_DTL_D
06
07Execution Plan
08----------------------------------------------------------
09Plan hash value: 1825303736
10
11-----------------------------------------------------------------------------------------------
12| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
13-----------------------------------------------------------------------------------------------
14|   0 | SELECT STATEMENT            |                 |     1 |    78 |     1   (0)| 00:00:01 |
15|   1 |  TABLE ACCESS BY INDEX ROWID| IND_TEXT        |     1 |    78 |     1   (0)| 00:00:01 |
16|*  2 |   INDEX RANGE SCAN          | IND_OBJECT_CHAR |     1 |       |     1   (0)| 00:00:01 |
17-----------------------------------------------------------------------------------------------
18
19SQL> SELECT OBJECT_CHAR,OBJECT_NAME FROM IND_TEXT WHERE OBJECT_CHAR=100832;
20
21OBJECT_CHAR          OBJECT_NAME
22-------------------- --------------------
23100832               ORD_ORD_DTL_D
24
25Execution Plan
26----------------------------------------------------------
27Plan hash value: 2154511419
28
29------------------------------------------------------------------------------
30| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
31------------------------------------------------------------------------------
32|   0 | SELECT STATEMENT  |          |     6 |   468 |   143   (3)| 00:00:02 |
33|*  1 |  TABLE ACCESS FULL| IND_TEXT |     6 |   468 |   143   (3)| 00:00:02 |
34------------------------------------------------------------------------------

那么到底查询用number类型存放的数字，查询时不加单引号肯定是走索引的，加上单引号会不会走索引呢，看下面的测试。

01SQL> select OBJECT_NAME,OBJECT_ID from ind_text where OBJECT_ID=100832;
02
03                                                                                                                 OBJECT_NAME          OBJECT_ID
04--------------       -----------
05ORD_ORD_DTL_D        100832
06
07Execution Plan
08----------------------------------------------------------
09Plan hash value: 417862871
10
11---------------------------------------------------------------------------------------------
12| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
13---------------------------------------------------------------------------------------------
14|   0 | SELECT STATEMENT            |               |     1 |    79 |     2   (0)| 00:00:01 |
15|   1 |  TABLE ACCESS BY INDEX ROWID| IND_TEXT      |     1 |    79 |     2   (0)| 00:00:01 |
16|*  2 |   INDEX RANGE SCAN          | IND_OBJECT_ID |     1 |       |     1   (0)| 00:00:01 |
17---------------------------------------------------------------------------------------------
18
19SQL> select OBJECT_NAME,OBJECT_ID from ind_text where OBJECT_ID='100832';
20
21                                                                                                                 OBJECT_NAME       OBJECT_ID
22--------------    ----------
23ORD_ORD_DTL_D     100832
24
25Execution Plan
26----------------------------------------------------------
27Plan hash value: 417862871
28
29---------------------------------------------------------------------------------------------
30| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
31---------------------------------------------------------------------------------------------
32|   0 | SELECT STATEMENT            |               |     1 |    79 |     2   (0)| 00:00:01 |
33|   1 |  TABLE ACCESS BY INDEX ROWID| IND_TEXT      |     1 |    79 |     2   (0)| 00:00:01 |
34|*  2 |   INDEX RANGE SCAN          | IND_OBJECT_ID |     1 |       |     1   (0)| 00:00:01 |
35---------------------------------------------------------------------------------------------

可见如果使用number类型存放数字，不论查询时加不加单元号都会走索引的。

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

建议继续学习：

  1. 由浅入深探究mysql索引结构原理、性能分析与优化    (阅读：14627)
  2. 浅谈MySQL索引背后的数据结构及算法    (阅读：9647)
  3. 由浅入深理解索引的实现(2)    (阅读：6108)
  4. HBase二级索引与Join    (阅读：5629)
  5. 如何建立合适的索引？    (阅读：5141)
  6. InnODB和MyISAM索引统计集合    (阅读：4984)
  7. Innodb 表和索引结构    (阅读：4603)
  8. mysql查询中利用索引的机制    (阅读：4423)
  9. MySQL索引背后的数据结构及算法原理    (阅读：4287)
  10. 多维度分类排行榜应用：用位图索引    (阅读：3945)
QQ技术交流群：445447336，欢迎加入！
扫一扫订阅我的微信号：IT技术博客大学习
<< 前一篇：Oracle正则表达式使用小结
后一篇：那些常见的Oracle错误 >>
文章信息
© 2009 - 2024 by blogread.cn 微博：@IT技术博客大学习

京ICP备15002552号-1