Is not a problem the concurrence, because this specifica case, only a dozen of user working toghether and not probably they insert in the same milisecond, but thks for the notice.

And about the optimized index, I finally undestand, but i hope to get a instant result for last year (actually only used)

--- In firebird-support@yahoogroups.com, Ann Harrison <aharrison@...> wrote:
>
> 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]
>