索引被 Oracle 忽略

我有一个传感器数据表，简化后如下所示：

DEVICE_ID NUMBER
SENSOR_ID NUMBER
DATA_TIME NUMBER
DATA_VALUE1 NUMBER

该表包含数十亿行并按周数分区，DATA_TIME 为 Unix 时间（自 1970 年以来的毫秒数）。我在这个表上有两个索引，IX1 (DATA_TIME DESC) 和 IX2 (DEVICE_ID,SENSOR_ID,DATA_TIME)。IX2 是昨天创建的。

我的查询想要查看当月一个传感器的数据，如下所示：

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 

我惊讶地发现优化器忽略了 IX2 并提出了以下计划：

---------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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->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->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")>=HEXTORAW('38FDD8C8BEA9A4FF')  AND SYS_OP_DESCEND("DATA_TIME")<=HEXTORAW('38FDD8DDFAA9A4FF') )
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("DATA_TIME"))>=1383304859000 AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("DATA_TIME"))<=1385464859000)

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

表和索引的统计信息是最新的。我找到了一些为此语句创建的 SQL 基线并将其删除。最重要的是，SQL 调优顾问建议在 (DEVICE_ID,SENSOR_ID,DATA_TIME,DATA_VALUE1) 上创建索引。