0

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?

1

Drop that index and create without desc:

create index idx_transfer_log_status on transfer_log (transfer_status, last_sent_time);

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:

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  6q8sbnqkajz74, child number 1
-------------------------------------
select   max(d.last_sent_time) from   transfer_log d where
d.transfer_status = 200

Plan hash value: 3076158531

--------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                         |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE              |                         |     1 |    11 |            |          |
|   2 |   FIRST ROW                  |                         |     1 |    11 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN (MIN/MAX)| IDX_TRANSFER_LOG_STATUS |     1 |    11 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  3 - access("D"."TRANSFER_STATUS"=200)

Unfortunately this does not work with a desc index.

2
  • that was unexpected! And I thought I was extra smart by making it desc. The query is fast now (30ms), but the cost still shows as 2207. – EasterBunnyBugSmasher Mar 16 at 16:08
  • So what's the point in having a DESC index at all if the database can decide to read an ascending index in descending order? Plus, the DESC index isn't able to deal with MIN()/MAX() (or at least not efficiently)? Puzzled... – Vérace Mar 16 at 20:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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