## The INSTR Function will Never Use an Index? I will give You a Hint

1 06 2010

June 1, 2010

As regular readers probably know, I frequently read books on various computer related topics.  Last winter I pre-ordered the book “Oracle SQL Recipes”, and have been reading the book on and off since that time.  Some parts of the book are absolutely fantastic, and others leave me scratching my head – did I read that right?  Page 169 of the book describes the INSTR and LIKE functions.  The book makes the following statement:

LIKE can be more readable, and may even use an index if there are no pattern-matching characters at the beginning of the search string. INSTR will never use an existing index unless you have created a function-based index containing the exact INSTR clause that you used in the WHERE clause.

The above paragraph conveys a lot of good information, and certainly the authors have tested that the above paragraph is correct.  Repeat after me: The INSTR function will never use a non-function based index!  The INSTR function will never use a non-function based index.  The INSTR function will never use a non-function based index?

I sense that some people are not sure that the above is true.  Let’s put together a quick test table for a test:

```CREATE TABLE T5 (
CHAR_COL VARCHAR2(10),
C2 VARCHAR2(100),
PRIMARY KEY (CHAR_COL));

INSERT INTO
T5
SELECT
TO_CHAR(ROWNUM),
FROM
DUAL
CONNECT BY
LEVEL <=1000000;

COMMIT;

Now that we have a test table with a primary key index, we need a test script:

```SET AUTOTRACE TRACEONLY EXPLAIN

SELECT
*
FROM
T5
WHERE
CHAR_COL LIKE '99999%';

SELECT
*
FROM
T5
WHERE
INSTR(CHAR_COL,'99999') = 1;

SELECT
*
FROM
T5
WHERE
SUBSTR(CHAR_COL,1,5) = '99999';

SELECT /*+ INDEX(T5) */
*
FROM
T5
WHERE
INSTR(CHAR_COL,'99999') = 1;

SELECT /*+ INDEX(T5) */
*
FROM
T5
WHERE
SUBSTR(CHAR_COL,1,5) = '99999';

SELECT /*+ INDEX(T5) CARDINALITY(T5 11) */
*
FROM
T5
WHERE
INSTR(CHAR_COL,'99999') = 1;

SELECT /*+ INDEX(T5) CARDINALITY(T5 11) */
*
FROM
T5
WHERE
SUBSTR(CHAR_COL,1,5) = '99999';```

If the above script is executed on Oracle Database 11.2.0.1, which of the above seven SQL statements will use the primary key index?

Before you cry fowl, think about the question and the expected execution plans.

Wild turkeys, probably not what you would expect to find on a blog that is about Oracle Databases.  Yet seemingly appropriate.  I found this group of turkeys wandering in my field a year or two ago.  It is amazing how close I was able to get to the birds – with a 20x zoom camera.

Now that you have had a chance to think about it, and you have probably seen this blog article, which, if any, of the execution plans will show that the primary key index was used?  Here is the output of the script:

```SQL> SELECT
2    *
3  FROM
4    T5
5  WHERE
6    CHAR_COL LIKE '99999%';

Execution Plan
----------------------------------------------------------
Plan hash value: 260357324

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     1 |   108 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T5           |     1 |   108 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | SYS_C0023201 |     1 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CHAR_COL" LIKE '99999%')
filter("CHAR_COL" LIKE '99999%')

---

SQL> SELECT
2    *
3  FROM
4    T5
5  WHERE
6    INSTR(CHAR_COL,'99999') = 1;

Execution Plan
----------------------------------------------------------
Plan hash value: 2002323537

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 10000 |  1054K|  4407   (1)| 00:00:53 |
|*  1 |  TABLE ACCESS FULL| T5   | 10000 |  1054K|  4407   (1)| 00:00:53 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(INSTR("CHAR_COL",'99999')=1)

---

SQL> SELECT
2    *
3  FROM
4    T5
5  WHERE
6    SUBSTR(CHAR_COL,1,5) = '99999';

Execution Plan
----------------------------------------------------------
Plan hash value: 2002323537

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 10000 |  1054K|  4422   (1)| 00:00:54 |
|*  1 |  TABLE ACCESS FULL| T5   | 10000 |  1054K|  4422   (1)| 00:00:54 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(SUBSTR("CHAR_COL",1,5)='99999')

---

SQL> SELECT /*+ INDEX(T5) */
2    *
3  FROM
4    T5
5  WHERE
6    INSTR(CHAR_COL,'99999') = 1;

Execution Plan
----------------------------------------------------------
Plan hash value: 1277447555

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              | 10000 |  1054K|  5935   (1)| 00:01:12 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T5           | 10000 |  1054K|  5935   (1)| 00:01:12 |
|*  2 |   INDEX FULL SCAN           | SYS_C0023201 | 10000 |       |  3922   (1)| 00:00:48 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(INSTR("CHAR_COL",'99999')=1)

---

SQL> SELECT /*+ INDEX(T5) */
2    *
3  FROM
4    T5
5  WHERE
6    SUBSTR(CHAR_COL,1,5) = '99999';

Execution Plan
----------------------------------------------------------
Plan hash value: 1277447555

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              | 10000 |  1054K|  5950   (1)| 00:01:12 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T5           | 10000 |  1054K|  5950   (1)| 00:01:12 |
|*  2 |   INDEX FULL SCAN           | SYS_C0023201 | 10000 |       |  3937   (1)| 00:00:48 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(SUBSTR("CHAR_COL",1,5)='99999')

---

SQL> SELECT /*+ INDEX(T5) CARDINALITY(T5 11) */
2    *
3  FROM
4    T5
5  WHERE
6    INSTR(CHAR_COL,'99999') = 1;

Execution Plan
----------------------------------------------------------
Plan hash value: 1277447555

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |    11 |  1188 |  5935   (1)| 00:01:12 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T5           |    11 |  1188 |  5935   (1)| 00:01:12 |
|*  2 |   INDEX FULL SCAN           | SYS_C0023201 | 10000 |       |  3922   (1)| 00:00:48 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(INSTR("CHAR_COL",'99999')=1)

---

SQL> SELECT /*+ INDEX(T5) CARDINALITY(T5 11) */
2    *
3  FROM
4    T5
5  WHERE
6    SUBSTR(CHAR_COL,1,5) = '99999';

Execution Plan
----------------------------------------------------------
Plan hash value: 1277447555

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |    11 |  1188 |  5950   (1)| 00:01:12 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T5           |    11 |  1188 |  5950   (1)| 00:01:12 |
|*  2 |   INDEX FULL SCAN           | SYS_C0023201 | 10000 |       |  3937   (1)| 00:00:48 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(SUBSTR("CHAR_COL",1,5)='99999')```