Easy (and usual case)

Simple table

CREATE TABLE ORDENES (
ID_ORDEN INTEGER NOT NULL /* Primary key - GenId */
YEAR_ORDEN INTEGER NOT NULL /* Year of Orden */,
N_ORDEN INTEGER NOT NULL /* Order in the Year */,
... /* no matter */
);

ALTER TABLE ORDENES ADD CONSTRAINT UK_ORDENES UNIQUE (YEAR_ORDEN, N_ORDEN)USING DESCENDING INDEX UK_ORDENES_YEAR_N_ORDEN;


Now I need to access in the insert trigger, for next order to be assigned to N_ORDEN in the YEAR_ORDEN using

select coalesce(max(o.n_orden),0)+1
from ordenes o
where o.year_orden=new.year_orden
into new.n_orden

IT WORK!!!! nice, BUT NOT OPTIMIZED, in the Performance Analisys it read (indexed) all the N_ORDEN in the table, not going to the first (given the descending order of the index)

How can optimize the index/query?
Or simply is not possible doing more?


Tks in advance