oracle索引匹配,左匹配查询（ like '%1234'）不能用到索引的优化方法

SQL> create table test(id varchar2(60) not null primary key,mc varchar2(60));

表已创建。

SQL> begin

2  for i in 1..1000 loop

3  insert into test values(i,i);

4  end loop;

5  end;

6  /

PL/SQL 过程已成功完成。

SQL> commit;

提交完成。

SQL> select index_name from user_indexes where table_name='TEST';

INDEX_NAME

------------------------------------------------------------

SYS_C00198273

SQL> select /*+index(test SYS_C00198273)*/ * from test where id like '%333';

ID

--------------------------------------------------------------------------------

MC

--------------------------------------------------------------------------------

333

333

执行计划

----------------------------------------------------------

0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=64)

1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (TABLE) (Cost=1 Ca

rd=1 Bytes=64)

2    1     INDEX (RANGE SCAN) OF 'SYS_C00198273' (INDEX (UNIQUE)) (

Cost=2 Card=9)

统计信息

----------------------------------------------------------

4  recursive calls

0  db block gets

120  consistent gets

3  physical reads

224  redo size

391  bytes sent via SQL*Net to client

504  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

SQL> select * from test where id like '%333';

ID

--------------------------------------------------------------------------------

MC

--------------------------------------------------------------------------------

333

333

执行计划

----------------------------------------------------------

0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=9 Card=1 Bytes=64)

1    0   TABLE ACCESS (FULL) OF 'TEST' (TABLE) (Cost=9 Card=1 Bytes

=64)

统计信息

----------------------------------------------------------

0  recursive calls

0  db block gets

23  consistent gets

0  physical reads

0  redo size

391  bytes sent via SQL*Net to client

504  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

SQL> select * from test where id like '333%';

ID

--------------------------------------------------------------------------------

MC

--------------------------------------------------------------------------------

333

333

执行计划

----------------------------------------------------------

0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=64)

1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (TABLE) (Cost=1 Ca

rd=1 Bytes=64)

2    1     INDEX (RANGE SCAN) OF 'SYS_C00198273' (INDEX (UNIQUE)) (

Cost=2 Card=1)

统计信息

----------------------------------------------------------

9  recursive calls

0  db block gets

30  consistent gets

3  physical reads

0  redo size

391  bytes sent via SQL*Net to client

504  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed