在白鳝的《像ORACLE一样思考》中提到，like '%A' 并不能使用反转索引，验证如下。

--版本
select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

--测试数据准备
create table test
as
select object_id,object_name,rpad('x',60,'x') x from dba_objects;

insert into test
select * from test;
commit;

begin
dbms_stats.gather_table_stats(user,'TEST');
end;
/

1,like '%A' 是否使用反转索引？
create index idx_test_object_name on test(object_name) reverse;

set autotrace on

1.1 like '%A'，没有使用反转索引
select object_id,object_name from test t where object_name like '%BA_OBJECTS';
OBJECT_ID OBJECT_NAME
---------- ---------------
      2371 DBA_OBJECTS
      2372 DBA_OBJECTS
      2371 DBA_OBJECTS
      2372 DBA_OBJECTS

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      | 7998 |   210K|   171   (4)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TEST | 7998 |   210K|   171   (4)| 00:00:02 |
--------------------------------------------------------------------------

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

1 - filter("OBJECT_NAME" LIKE '%BA_OBJECTS')

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

1.2 使用提示的情况下，反转索引也只是被全索引扫描（INDEX FULL SCAN）

select /*+ index(t idx_test_object_name) */ object_id,object_name from test t where object_name like '%BA_OBJECTS';
OBJECT_ID OBJECT_NAME
---------- ---------------
      2371 DBA_OBJECTS
      2372 DBA_OBJECTS
      2371 DBA_OBJECTS
      2372 DBA_OBJECTS

Execution Plan
----------------------------------------------------------
Plan hash value: 2094098115

----------------------------------------------------------------------------------------------------
| Id | Operation                   | Name                 | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                      | 7998 |   210K| 5868   (1)| 00:00:49 |
|   1 | TABLE ACCESS BY INDEX ROWID| TEST                 | 7998 |   210K| 5868   (1)| 00:00:49 |
|* 2 |   INDEX FULL SCAN           | IDX_TEST_OBJECT_NAME | 7998 |       |   735   (1)| 00:00:07 |
----------------------------------------------------------------------------------------------------

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

2 - filter("OBJECT_NAME" LIKE '%BA_OBJECTS')

Statistics
----------------------------------------------------------
          1 recursive calls
          0 db block gets
        733 consistent gets
        728 physical reads
          0 redo size
        831 bytes sent via SQL*Net to client
        385 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          4 rows processed

2,like '%A' 是否使用反转函数(reverse())索引？
create index idx_test_object_name_r on test(reverse(object_name));

2.1 like '%A'，在查询列上不使用反转函数时不使用函数索引
select object_id,object_name from test t where object_name like '%BA_OBJECTS';
OBJECT_ID OBJECT_NAME
---------- ---------------
      2371 DBA_OBJECTS
      2372 DBA_OBJECTS
      2371 DBA_OBJECTS
      2372 DBA_OBJECTS

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      | 7998 |   210K|   171   (4)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TEST | 7998 |   210K|   171   (4)| 00:00:02 |
--------------------------------------------------------------------------

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

1 - filter("OBJECT_NAME" LIKE '%BA_OBJECTS')

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

2.2 like '%A'，必须在查询条件中使用reverse函数,才能使用函数索引(INDEX RANGE SCAN)
select object_id,object_name from test t where reverse(object_name) like reverse('%BA_OBJECTS');
OBJECT_ID OBJECT_NAME
---------- ---------------
      2371 DBA_OBJECTS
      2372 DBA_OBJECTS
      2371 DBA_OBJECTS
      2372 DBA_OBJECTS

Execution Plan
----------------------------------------------------------
Plan hash value: 748328293

------------------------------------------------------------------------------------------------------
| Id | Operation                   | Name                   | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                        | 7998 |   210K|   165   (1)| 00:00:02 |
|   1 | TABLE ACCESS BY INDEX ROWID| TEST                   | 7998 |   210K|   165   (1)| 00:00:02 |
|* 2 |   INDEX RANGE SCAN          | IDX_TEST_OBJECT_NAME_R | 1440 |       |     9   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

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

2 - access(REVERSE("OBJECT_NAME") LIKE 'STCEJBO_AB%')
filter(REVERSE("OBJECT_NAME") LIKE 'STCEJBO_AB%')

Statistics
----------------------------------------------------------
         24 recursive calls
          0 db block gets
         10 consistent gets
          3 physical reads
          0 redo size
        544 bytes sent via SQL*Net to client
        385 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          4 rows processed

ITPub博客

like运算符是否能使用反转索引

最新文章