数值转换导致不走索引一. 问题描述：二 . 解决方法：

71 阅读 0 评论 47 点赞

我是靠谱客的博主忧伤大侠，最近开发中收集的这篇文章主要介绍数值转换导致不走索引一. 问题描述：二 . 解决方法：，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

一. 问题描述：

表的数据量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>