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