Tenho uma tabela de dados do sensor, simplificada fica assim:
DEVICE_ID NUMBER
SENSOR_ID NUMBER
DATA_TIME NUMBER
DATA_VALUE1 NUMBER
A tabela contém bilhões de linhas e é particionada por número de semana, DATA_TIME está em tempo Unix (milissegundos desde 1970). Eu tenho dois índices nesta tabela, IX1 em (DATA_TIME DESC) e IX2 em (DEVICE_ID,SENSOR_ID,DATA_TIME). IX2 foi criado ontem.
Minha consulta deseja ver os dados de um sensor no mês atual e é assim:
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
Fiquei surpreso ao descobrir que o otimizador ignora o IX2 e apresenta o seguinte plano:
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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
As estatísticas na tabela e os índices são recentes. Encontrei algumas SQL Baselines que foram criadas para esta instrução e as removi. Além disso, o consultor de ajuste SQL recomendou a criação de um índice em (DEVICE_ID,SENSOR_ID,DATA_TIME,DATA_VALUE1).
Resolvi o problema descartando e recriando o IX1. (O raciocínio por trás disso era invalidar qualquer plano em cache que ainda o usasse.) A consulta agora retorna 420 linhas após 11 segundos. Se alguém puder me dar uma boa teoria sobre o que aconteceu, aceitarei como uma resposta correta.