Index being ignored by Oracle I have a table for sensor data, simplified it looks like this:

DEVICE_ID NUMBER
SENSOR_ID NUMBER
DATA_TIME NUMBER
DATA_VALUE1 NUMBER


The table constains billions of rows and is partitioned by week number, DATA_TIME is in Unix time (milliseconds since 1970). I have two indexes on this table, IX1 on (DATA_TIME DESC) and IX2 on (DEVICE_ID,SENSOR_ID,DATA_TIME). IX2 was created yesterday.

My query wants to see the data for one sensor in the current month and goes like this:

SELECT /*+ PARALLEL (12) */
   data_time,
   data_value1
FROM table
WHERE device_id = 1041
  AND sensor_id = 202
  AND data_time BETWEEN 1383304859 *1000 AND 1385464859 *1000
ORDER BY data_time ASC 


I was surprised to find that the optimizer ignores IX2 and comes up with the following plan:

---------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                              |    65 |  1365 |     5  (20)| 00:00:01 |       |       |        |      |            |
|   1 |  PX COORDINATOR                         |                              |       |       |            |          |       |       |        |      |            |
|   2 |   PX SEND QC (ORDER)                    | :TQ10001                     |    65 |  1365 |     5  (20)| 00:00:01 |       |       |  Q1,01 | P-&gt;S | QC (ORDER) |
|   3 |    SORT ORDER BY                        |                              |    65 |  1365 |     5  (20)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
|   4 |     PX RECEIVE                          |                              |    65 |  1365 |     4   (0)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
|   5 |      PX SEND RANGE                      | :TQ10000                     |    65 |  1365 |     4   (0)| 00:00:01 |       |       |  Q1,00 | P-&gt;P | RANGE      |
|   6 |       PX PARTITION RANGE ITERATOR       |                              |    65 |  1365 |     4   (0)| 00:00:01 |   252 |   256 |  Q1,00 | PCWC |            |
|*  7 |        TABLE ACCESS BY LOCAL INDEX ROWID| table                        |    65 |  1365 |     4   (0)| 00:00:01 |   252 |   256 |  Q1,00 | PCWP |            |
|*  8 |         INDEX RANGE SCAN                | ix1                          |     1 |       |     4   (0)| 00:00:01 |   252 |   256 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------

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

   7 - filter("DEVICE_ID"=1041 AND "SENSOR_ID"=202)
   8 - access(SYS_OP_DESCEND("DATA_TIME")&gt;=HEXTORAW('38FDD8C8BEA9A4FF')  AND SYS_OP_DESCEND("DATA_TIME")&lt;=HEXTORAW('38FDD8DDFAA9A4FF') )
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("DATA_TIME"))&gt;=1383304859000 AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("DATA_TIME"))&lt;=1385464859000)

Note
-----
   - automatic DOP: skipped because of IO calibrate statistics are missing


The statistics on the table and the indexes are recent. I found some SQL Baselines that were created for this statement and removed them.
And on top of that, the SQL tuning advisor recommended creating an index on (DEVICE_ID,SENSOR_ID,DATA_TIME,DATA_VALUE1).
I resolved the problem by dropping and recreating IX1. (The reasoning behind that was to invalidate any cached plan still using it.) The query now returns 420 rows after 11 seconds. If anybody can give me a good theory on what happened I will accept it as a correct answer.