Create a descending index with yearorden and norden in this sequence. Thats
it.
Em 16/04/2013 04:57, "skander_sp" <skander_sp@...> escreveu:

> **
>
>
> 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
>
>
>

[Non-text portions of this message have been removed]