/*  
  结论：索引遇到like '%LJB' 或者是'%LJB%'的查询，是用不到索引的（除非是全索引访问，这是索引能回答问题的一个例外）。
  不过like 'LJB%'是可以用到索引的。原理其实很简单，从索引有序性就可以推理到原因了。
  不过本次案例中还说了一个很有趣的，让'%LJB'用的索引的另类方法，值得大家推敲和学习，这里涉及到了函数索引的知识，在下一讲中会描述。
  另外即便是'%LJB%'，也不见的就一定用不到索引，可以考虑全文检索，这也将在下一讲中描述。
  
*/

思路：
1. 全文检索
2. 寻找函数构造的机会，并建函数索引


drop table t purge;
create table t as select * from dba_objects where object_id is not null;
set autotrace off
update t set object_id=rownum;
update t set object_name='AAALJB' where object_id=8;
update t set object_name='LJBAAA' where object_id=10;
commit;
create index idx_object_name on t(object_name);


SET AUTOTRACE ON
SET LINESIZE 1000


select object_name,object_id from t where object_name like 'LJB%';


OBJECT_NAME             OBJECT_ID
------------------------------ ---
LJBAAA                         10
LJB_TMP_SESSION             72521
LJB_TMP_SESSION             72910
LJB_TMP_TRANSACTION         72522
LJB_TMP_TRANSACTION         72911


已选择5行。


执行计划
-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |     5 |   395 |     6   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T               |     5 |   395 |     6   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_OBJECT_NAME |     5 |       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          9  consistent gets
          0  physical reads
          0  redo size
        602  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed
          
          
SQL> select object_name,object_id from t where object_name like '%LJB%';


OBJECT_NAME                 OBJECT_ID
---------------------------------- ---
AAALJB                              8
LJBAAA                             10
LJB_TMP_SESSION                 72521
LJB_TMP_TRANSACTION             72522
LJB_TMP_SESSION                 72910
LJB_TMP_TRANSACTION             72911


已选择6行。


执行计划
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    12 |   948 |   292   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T    |    12 |   948 |   292   (1)| 00:00:04 |
--------------------------------------------------------------------------
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1049  consistent gets
          0  physical reads
          0  redo size
        653  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed
          
          
          
select object_name,object_id from t where object_name like '%LJB';  
OBJECT_NAME           OBJECT_ID
---------------------------- --
AAALJB                        8      


执行计划
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    12 |   948 |   292   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T    |    12 |   948 |   292   (1)| 00:00:04 |
--------------------------------------------------------------------------
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1049  consistent gets
          0  physical reads
          0  redo size
        496  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
          
          
select  reverse('%LJB') from dual;
REVER
-----
BJL%          


create index idx_reverse_objname on t(reverse(object_name));
set autotrace on 
select object_name,object_id from t where reverse(object_name) like reverse('%LJB'); 


OBJECT_NAME           OBJECT_ID
---------------------------- --
AAALJB                        8


执行计划
---------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                     |  3596 |   509K|   290   (0)| 00:00:04 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T                   |  3596 |   509K|   290   (0)| 00:00:04 |
|*  2 |   INDEX RANGE SCAN          | IDX_REVERSE_OBJNAME |   647 |       |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        496  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed