oracle的like走索引吗,在索引的字段上like查询 like ':1%'走索引 like '%:1%' 不走索引...

DROP TABLE t;

CREATE TABLE t AS SELECT * FROM dba_objects;

CREATE INDEX idx_t ON t(object_name);

Execution Plan

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

Plan hash value: 112249094

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

| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |       | 19642 |  3970K|  2274   (1)| 00:00:28 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T     | 19642 |  3970K|  2274   (1)| 00:00:28 |

|*  2 |   INDEX FULL SCAN           | IDX_T |  2987 |       |   376   (1)| 00:00:05 |

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

Predicate Information (identified by operation id):

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

2 - filter("OBJECT_NAME" IS NOT NULL AND "OBJECT_NAME" LIKE '%T%')

text索引，全文索引，

不要章了，只能说可以走full index,但是效果肯定不好，除非只找索引字段，走index fast full...

dingjun123@ORADB> SELECT  object_name FROM t WHERE object_name LIKE '%T%';

26477 rows selected.

Elapsed: 00:00:00.25

Execution Plan

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

Plan hash value: 2497555198

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

| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT     |       | 19642 |  1265K|   109   (1)| 00:00:02 |

|*  1 |  INDEX FAST FULL SCAN| IDX_T | 19642 |  1265K|   109   (1)| 00:00:02 |

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

比如如果知道，通配的结果不多，也可以改写为：

dingjun123@ORADB> SELECT  *  FROM t WHERE object_name LIKE '%TABC%';

21 rows selected.

Elapsed: 00:00:00.07

Execution Plan

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

Plan hash value: 1601196873

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

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |      |    12 |  2484 |   300   (1)| 00:00:04 |

|*  1 |  TABLE ACCESS FULL| T    |    12 |  2484 |   300   (1)| 00:00:04 |

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

Predicate Information (identified by operation id):

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

1 - filter("OBJECT_NAME" IS NOT NULL AND "OBJECT_NAME" LIKE '%TABC%')

Note

-----

- dynamic sampling used for this statement (level=2)

Statistics

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

0  recursive calls

0  db block gets

1078  consistent gets

0  physical reads

0  redo size

2943  bytes sent via SQL*Net to client

531  bytes received via SQL*Net from client

3  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

21  rows processed

dingjun123@ORADB> SELECT * FROM t WHERE object_name IN (

2  SELECT  object_name FROM t WHERE object_name LIKE '%TABC%'

3  );

21 rows selected.

Elapsed: 00:00:00.06

Execution Plan

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

Plan hash value: 61236260

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

| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT             |       |    11 |  3003 |   123   (2)| 00:00:02 |

|   1 |  NESTED LOOPS                |       |       |       |            |          |

|   2 |   NESTED LOOPS               |       |    11 |  3003 |   123   (2)| 00:00:02 |

|   3 |    SORT UNIQUE               |       |    12 |   792 |   109   (1)| 00:00:02 |

|*  4 |     INDEX FAST FULL SCAN     | IDX_T |    12 |   792 |   109   (1)| 00:00:02 |

|*  5 |    INDEX RANGE SCAN          | IDX_T |     1 |       |     2   (0)| 00:00:01 |

|   6 |   TABLE ACCESS BY INDEX ROWID| T     |     1 |   207 |     3   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

4 - filter("OBJECT_NAME" LIKE '%TABC%' AND "OBJECT_NAME" IS NOT NULL)

5 - access("OBJECT_NAME"="OBJECT_NAME")

filter("OBJECT_NAME" LIKE '%TABC%' AND "OBJECT_NAME" IS NOT NULL)

Note

-----

- dynamic sampling used for this statement (level=2)

Statistics

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

0  recursive calls

0  db block gets

432  consistent gets

0  physical reads

0  redo size

2965  bytes sent via SQL*Net to client

531  bytes received via SQL*Net from client

3  SQL*Net roundtrips to/from client

1  sorts (memory)

0  sorts (disk)

21  rows processed