substr函数索引创建测试

技术群里小伙伴，沟通说一条经常查询的SQL缓慢，单表SQL一个列作为条件，列是int数值类型，索引类型默认创建。

一.SQL文本
substr函数索引创建测试
select *from(select substr(nm,0,17) nm1 from bbm2019) where nm1   in ('55552389655808973')
需求，将上述SQL执行速度加快，目的是走索引。
创建测试表

SQL>create table tt as select * from dba_objects;

SQL> desc tt
 OBJECT_ID                           NUMBER

二.优化思路
2.1 通过修改SQL文本方式
调整前

SQL> select * from (select substr(object_id,0,4) cc from tt) t where t.cc in ('2559');
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   870 |  4350 |   347   (1)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| TT   |   870 |  4350 |   347   (1)| 00:00:05 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(SUBSTR(TO_CHAR("OBJECT_ID"),0,4)='2559')
调整后，使用单行SQL查询，不使用子查询
SQL> select substr(object_id,0,4) from tt where substr(object_id,0,4) in ('2559');  
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   870 |  4350 |   347   (1)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| TT   |   870 |  4350 |   347   (1)| 00:00:05 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(SUBSTR(TO_CHAR("OBJECT_ID"),0,4)='2559')
无效，SQL还是一次全表扫描，只是测试使用。


2.2 调整索引为字符格式，SQL访问使用%模糊匹配
select substr(nm,0,17) nm1 lrrq from bbm2019 where nm1in like '55552389655808973%';
SQL> create index tt_obje_ind on tt(object_id);
SQL> set autotrace on
SQL>select object_id from tt where object_id like '25599%';
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |   182 |   347   (1)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| TT   |    14 |   182 |   347   (1)| 00:00:05 |
--------------------------------------------------------------------------
可以发现，当表字段为数值类型，使用like 字符格式访问，是无法获取结果的。

SQL> drop index tt_obje_ind;
SQL> create index tt_obje_ind on tt(to_char(object_id));
SQL> select object_id from tt where object_id like '25599%';
-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |    35 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TT          |     1 |    35 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TT_OBJE_IND |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
SQL> select object_id from tt where object_id like '2559%';
-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |    11 |   385 |     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TT          |    11 |   385 |     5   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TT_OBJE_IND |    11 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

      2 - access(TO_CHAR("OBJECT_ID") LIKE '2559%')
          filter(TO_CHAR("OBJECT_ID") LIKE '2559%')

使用to_char可以将索引存储格式调整为字符类型，where条件使用%进行查询可以走索引快速访问。

SQL> select object_id from tt where object_id='25599';  
SQL> select object_id from tt where object_id in '25599';  

SQL> select substr(object_id,0,4) from tt where object_id in('2559');  
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |   490 |   347   (1)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| TT   |    14 |   490 |   347   (1)| 00:00:05 |
--------------------------------------------------------------------------
发现使用in =并未走索引！
   1 - filter("OBJECT_ID"=25599) 可以发现并未显示to_char
此处，第一次发现oracle隐患转换的优先级，可能会影想是否走索引，由于oracle to_number优先级大于to_char因此即使我们写法'xx'字符数值等值查询，
oracle自动转换为数值类型，由于索引为字符类型，因此无法走索引。

SQL> select object_id from tt where to_char(object_id)='25599';  

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 870 | 23490 | 72 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TT | 870 | 23490 | 72 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TT_OBJE_IND | 348 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

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

2 - access(TO_CHAR("OBJECT_ID")='25599')

不创建函数索引，直接使用to_char类，查询条件

2.3 创建Substr函数索引
SQL> drop index tt_obje_ind; 
SQL> create index tt_obje_ind on tt(substr(object_id,0,4));
 SQL> select substr(object_id,0,4) from tt where substr(object_id,0,4) in ('2559');
 -------------------------------------------------------------------------------- 
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 
-------------------------------------------------------------------------------- 
| 0 | SELECT STATEMENT | | 11 | 110 | 1 (0)| 00:00:01 | 
|* 1 | INDEX RANGE SCAN| TT_OBJE_IND | 11 | 110 | 1 (0)| 00:00:01 |
 -------------------------------------------------------------------------------- 
Predicate Information (identified by operation id):
 ---------------------------------------------------
 1 - access(SUBSTR(TO_CHAR("OBJECT_ID"),0,4)='2559') 
SQL> create index tt_obje_ind on tt(substr(to_char(object_id),0,4)); 
SQL> select substr(object_id,0,4) from tt where substr(object_id,0,4) in ('2559');
 --------------------------------------------------------------------------------
 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
 -------------------------------------------------------------------------------- 
| 0 | SELECT STATEMENT | | 11 | 110 | 1 (0)| 00:00:01 |
 |* 1 | INDEX RANGE SCAN| TT_OBJE_IND | 11 | 110 | 1 (0)| 00:00:01 |
 -------------------------------------------------------------------------------- 
Predicate Information (identified by operation id):
 --------------------------------------------------- 
1 - access(SUBSTR(TO_CHAR("OBJECT_ID"),0,4)='2559')
创建substr函数索引，oracle自动会to_char转换，可以显示创建索引语法加上 （可不加,规范语法加上最好~)

on tt(substr(to_char(object_id),0,4));