Understanding Oracle Index Order

Question

I'm a bit confused by this, I hope someone can help. I'm reading trough Markus Winand's excellent Use The Index Luke book and there's this thing about concatenated indexes.

There is an (EMPLOYEE_ID, SUBSIDIARY_ID) index created so when he queries

SELECT first_name, last_name
  FROM employees
 WHERE subsidiary_id = 20

This execution plan comes up :

----------------------------------------------------
| Id | Operation         | Name      | Rows | Cost |
----------------------------------------------------
|  0 | SELECT STATEMENT  |           |  106 |  478 |
|* 1 |  TABLE ACCESS FULL| EMPLOYEES |  106 |  478 |
----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("SUBSIDIARY_ID"=20)

But here's the thing : on my own employees table (empno, ename, init, job, mgr, bdate, msal, comm, deptno) I created a concatenated index on (ENAME, JOB)

The query select ename from employees where job = 'TRAINER'; gives me the following execution plan :

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 4271702361

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

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

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

|   0 | SELECT STATEMENT |             |     3 |    45 |     1   (0)| 00:00:01 |

|*  1 |  INDEX SKIP SCAN | ENAME_INDEX |     3 |    45 |     1   (0)| 00:00:01 |

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


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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------


   1 - access("JOB"='TRAINER')
       filter("JOB"='TRAINER')

So now I'm a bit confused.

1) How come, despite the order, my index was still used?

2) Does an Index Skip Scan work for any concatenated index where I don't use the first column in a where clause?

3) Does an Index Skip Scan have a major impact on performance?

4) How come there's both an access and a filter predicate?

And while I'm here, I have another question

5) Do I need to take any precautions in indexing dates?

Answer 1

Good question.

Obviously, if you query had contained both ENAME and JOB, then Oracle would have used the index, either with a INDEX RANGE SCAN or INDEX UNIQUE SCAN. However, the leading edge of the index, ENAME, was not provided in the query predicates. So, Oracle's Cost Based Optimizer (CBO) has a choice. It may choose to do a FULL TABLE SCAN (ignoring the index), or an INDEX SKIP SCAN.

I assume you know what a FULL TABLE SCAN is, so I won't go into that.

So, what's an INDEX SKIP SCAN? Well, the CBO has the option, depending on the shape and size of the index, to do a skip scan. This usually happens when there is a relatively small number of distinct values for the leading column in the index. What happens is, Oracle takes the index and effectively breaks it down into several indexes. Suppose the leading column has 4 distinct values, (1-4). So, Oracle looks at the subset of the index where leading column equals 1, and does a range scan of that subset of the index, then does the same for the subsets of the index where leading column equals 2, then 3, then 4. In some cases, depending on how many distinct values the leading column has, and how selective the range scan on the second column is, this access path may be less costly than a FULL TABLE SCAN.

This is another reason why, if all other things are equal, you may want to put less selective columns in the leading edge of the index. (The other main reason being compression.)

Answer 2

Answers for most of your questions: https://oracle-base.com/articles/9i/index-skip-scanning

1) This is exactly what INDEX SKIP SCAN is for.

2) Yes, it can be used, but it depends on your statistics

3) It might and it might not -> depends on your statistics

4) Access path is about selecting data blocks to load and filter is about how row from data block are filtered out.

5) Index on DATE works very similarly to the index on other data types. DATE is 7 bytes long.

Answer 3

Oracle does have the ability, via an index skip scan, to use a composite index when you don't specify the leading column of the index in your predicate. This is generally much less efficient than a regular index scan, however. Conceptually, you can think of it doing an index scan for every distinct value of the leading column of the index. Normally, Oracle would only consider this sort of plan if the leading column had a few distinct values and the trailing column(s) were particularly selective. I wouldn't expect either to be true here-- presumably ename is nearly unique and job is rather less selective. I would expect that a full scan of the table would be more efficient so I would guess that something about your statistics is "wonky". If your table is particularly small, that could certainly cause query plans to be unusual simply because every plan appears to be exceptionally cheap.

In the real world, there are exceptionally few cases that someone sees an "index skip scan" in a query plan and thinks "Great! That's the plan I wanted." It generally means that something has gone wrong but that it may not have gone as far wrong as it might have.