I have a table that logs the transmission of data to another system.
create table transfer_log (id number(10),
fk_data number(10),
first_sent_time date,
last_sent_time date,
transfer_status number(3),
error_message varchar2(250));
create unique index pk_transfer_log on transfer_log (id);
alter table transfer_log add constraint pk_transfer_log primary key (id);
create index idx_transfer_log_status on transfer_log (transfer_status, last_sent_time desc);
The transfer_status column stores the HTTP status from sending the data.
transfer_status is never null (initially 0), last_sent_time is initially null.
So I have a health check, to see when the last successful transmission was:
select
max(d.last_sent_time)
from
transfer_log d
where
d.transfer_status = 200
;
and I hoped the index would make this query fast. But it's close to 1sec now and the execution plan doesn't look good:
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 2624 | |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
| 2 | INDEX RANGE SCAN | IDX_TRANSFER_LOG_STATUS | 1272670 | 7636020 | 2624 | |
----------------------------------------------------------------------------------------
I hoped the index (especially with the descending last_sent_time) would make this query fast as only the first entry needs to be read.
What did I do wrong?
Best Answer
Drop that index and create without desc:
The database can decide to read an ascending index in descending order. The last entry can be read as quickly as the first entry.
But even better, it can recognize a case when you search for a MIN/MAX value and use the operation for that:
Unfortunately this does not work with a
desc
index.