title: Understanding Oracle Index Order
body: <p>I'm a bit confused by this, I hope someone can help. I'm reading trough Markus Winand's excellent <code>Use The Index Luke</code> book and there's this thing about concatenated indexes.</p>
  
  <p>There is an <code>(EMPLOYEE_ID, SUBSIDIARY_ID)</code> index created so when he queries </p>
  
  <pre><code>SELECT first_name, last_name
    FROM employees
   WHERE subsidiary_id = 20
  </code></pre>
  
  <p>This execution plan comes up : </p>
  
  <pre><code>----------------------------------------------------
  | 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)
  </code></pre>
  
  <p>But here's the thing : on my own employees table <code>(empno, ename, init, job, mgr, bdate, msal, comm, deptno)</code> I created a concatenated index on <code>(ENAME, JOB)</code></p>
  
  <p>The query <code>select ename from employees where job = 'TRAINER';</code> gives me the following execution plan : </p>
  
  <pre><code>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')
  </code></pre>
  
  <p>So now I'm a bit confused. </p>
  
  <p>1) How come, despite the order, my index was still used? </p>
  
  <p>2) Does an Index Skip Scan work for any concatenated index where I don't use the first column in a where clause?</p>
  
  <p>3) Does an Index Skip Scan have a major impact on performance?</p>
  
  <p>4) How come there's both an access and a filter predicate?</p>
  
  <p>And while I'm here, I have another question</p>
  
  <p>5) Do I need to take any precautions in indexing dates?</p>