As a senior software and database developer, this is my experience:

If anything can go wrong, it will! also known as "Murphy's law"

Happy coding
Christian

--- In firebird-support@yahoogroups.com, "skander_sp" <skander_sp@...> wrote:
>
> 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]
> >
>