Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
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) –  a_horse_with_no_name Nov 27 '13 at 13:07
    
The plan is using IX1, why not IX2? –  Twinkles Nov 27 '13 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 –  a_horse_with_no_name Nov 27 '13 at 13:11
    
histograms?! docs.oracle.com/cd/E16655_01/server.121/e15858/… –  Munchi Nov 27 '13 at 13:24
1  
Is the index you want it to use global or local? –  FreshPhilOfSO Nov 27 '13 at 14:07
show 6 more comments

1 Answer

up vote 1 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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