On Tue, Apr 16, 2013 at 3:57 AM, skander_sp <skander_sp@...> wrote:

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

Not nice at all. It works single user, but it will fail in production, and
you will get violations of your unique constraint. Remember that Firebird
uses MVCC. If two concurrent transactions run the insert trigger they
will generate the same value for new.n_orden.

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

Firebird can use an index to optimize MAX, but only if it's the MAX of the
whole key - not a partial key match. If you have a descending index on
year_orden, Firebird will use an index to find the MAX of year_orden by
walking down the left hand side of the index tree. You're asking it to
find a specific year first, then the highest value of n_orden and that
doesn't work. Even if it did, this code doesn't produce good unique
numbers.

Good luck,

Ann


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