If you use this code inside a SP you must recompile it for using this new
index.
Also is a good task to recompute the selectivity of the others indexes.
Sorry my bad english.
Em 16/04/2013 10:40, "skander_sp" <skander_sp@...> escreveu:

> **
>
>
> Tks!
> This is a good solution... (and my first try)
> But don't work, still read the whole table for the yearorden given.
>
> I found a couple of "unoptimized" black-point in my application.
> And what it looks to be nice and wick became an pain in the ass.
>
> --- In firebird-support@yahoogroups.com, fabianoaspro@... wrote:
> >
> > 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]
> >
>
>
>

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