LIKE 什么时候走索引

SQL> set autotrace traceonly
SQL> select count(*) from awen.ob_bak where owner='AWEN';


执行计划
----------------------------------------------------------
Plan hash value: 3442547862

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 1860 (4)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | INDEX RANGE SCAN| OB_INDEX | 774K| 4538K| 1860 (4)| 00:00:03 |
------------------------------------------------------------------------------

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

2 - access("OWNER"='AWEN')


统计信息
----------------------------------------------------------
274 recursive calls
0 db block gets
46 consistent gets
8 physical reads
0 redo size
409 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select count(*) from awen.ob_bak where owner like '%AWEN%';


执行计划
----------------------------------------------------------
Plan hash value: 961138420

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 47284 (6)| 00:00:58 |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | INDEX FULL SCAN| OB_INDEX | 968K| 5673K| 47284 (6)| 00:00:58 |
-----------------------------------------------------------------------------

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

2 - filter("OWNER" LIKE '%AWEN%')


统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
44622 consistent gets
44636 physical reads
0 redo size
409 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)
1 rows processed

SQL> select count(*) from awen.ob_bak where owner like '%AWE%';


执行计划
----------------------------------------------------------
Plan hash value: 961138420

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 47284 (6)| 00:00:58 |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | INDEX FULL SCAN| OB_INDEX | 968K| 5673K| 47284 (6)| 00:00:58 |
-----------------------------------------------------------------------------

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

2 - filter("OWNER" LIKE '%AWE%')


统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
44622 consistent gets
44614 physical reads
0 redo size
409 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)
1 rows processed

SQL> select count(*) from awen.ob_bak where owner like 'AWE%';


执行计划
----------------------------------------------------------
Plan hash value: 3442547862

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 1860 (4)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | INDEX RANGE SCAN| OB_INDEX | 774K| 4538K| 1860 (4)| 00:00:03 |
------------------------------------------------------------------------------

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

2 - access("OWNER" LIKE 'AWE%')
filter("OWNER" LIKE 'AWE%')


统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
409 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)
1 rows processed

SQL> select count(*) from awen.ob_bak where owner like 'AWEN';


执行计划
----------------------------------------------------------
Plan hash value: 3442547862

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 1860 (4)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | INDEX RANGE SCAN| OB_INDEX | 774K| 4538K| 1860 (4)| 00:00:03 |
------------------------------------------------------------------------------

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

2 - access("OWNER" LIKE 'AWEN')


统计信息
----------------------------------------------------------
149 recursive calls
0 db block gets
30 consistent gets
5 physical reads
0 redo size
409 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed


like 'AWEN' 走索引
like 'AWE%' 走索引
like '%AWE' 不走
like '%AWE%' 不走