0

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->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

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).

11
  • What exactly is you question? The plan does use an index lookup as far as I can tell (I prefer the text output from explain plain)
    – user1822
    Commented Nov 27, 2013 at 13:07
  • The plan is using IX1, why not IX2?
    – Twinkles
    Commented Nov 27, 2013 at 13:09
  • Because most probably using the data_time is more efficient. How many rows would be returned by the condition device_id = 1041 AND sensor_id = 202
    – user1822
    Commented Nov 27, 2013 at 13:11
  • histograms?! docs.oracle.com/cd/E16655_01/server.121/e15858/…
    – Munchi
    Commented Nov 27, 2013 at 13:24
  • 1
    Is the index you want it to use global or local?
    – Philᵀᴹ
    Commented Nov 27, 2013 at 14:07

1 Answer 1

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.