数值转换导致不走索引

一. 问题描述：

表的数据量1300万+  搜索列为50-80w

每次都全表扫描，性能很差

SQL> set autotrace on
SQL> select count(*) from clspuser.crf_p2p_interest_split_rslt t where t.hb_status=1;

  COUNT(*)
----------
    782889


Execution Plan
----------------------------------------------------------
Plan hash value: 553503729

--------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                             |     1 |     2 | 45953   (3)| 00:09:12 |
|   1 |  SORT AGGREGATE    |                             |     1 |     2 |            |          |
|*  2 |   TABLE ACCESS FULL| CRF_P2P_INTEREST_SPLIT_RSLT |  1751K|  3421K| 45953   (3)| 00:09:12 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(TO_NUMBER("T"."HB_STATUS")=1)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
     207148  consistent gets
     190464  physical reads
      84076  redo size
        517  bytes sent via SQL*Net to client
        469  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> 

由以上执行计划可见过滤列进行了函数转换

  2 - filter(TO_NUMBER("T"."HB_STATUS")=1)

这说明本列数字类型为字符型，而查询默认是数字类型

SQL> set linesize 100
SQL> desc clspuser.crf_p2p_interest_split_rslt
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 HB_STATUS                                                      CHAR(1)
SQL> 

二 . 解决方法：

建立索引：

SQL>  create index SPLIT_RSLT_HB_STATUS_IDX ON  clspuser.crf_p2p_interest_split_rslt (HB_STATUS) ONLINE;

Index created.

SQL> 

更改查询语句：

SQL> select count(*) from clspuser.crf_p2p_interest_split_rslt t where t.hb_status='1';

  COUNT(*)
----------
     46674


Execution Plan
----------------------------------------------------------
Plan hash value: 3563200806

--------------------------------------------------------------------------------------------------
| Id  | Operation             | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                          |     1 |     2 |   584  (19)| 00:00:08 |
|   1 |  SORT AGGREGATE       |                          |     1 |     2 |            |          |
|*  2 |   INDEX FAST FULL SCAN| SPLIT_RSLT_HB_STATUS_IDX |   484K|   945K|   584  (19)| 00:00:08 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("T"."HB_STATUS"='1')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1768  consistent gets
        131  physical reads
          0  redo size
        517  bytes sent via SQL*Net to client
        469  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>