Put here the new ddls then the sql you use and the plan used.
How many records it reads to get the new key?
Em 16/04/2013 12:01, "skander_sp" <skander_sp@...> escreveu:

> **
>
>
> well, im not doing tryes in SP but in direct query, and i get different
> result, but not an optimistic one...
>
> Respect to the "compile or not" , my tests say to me it's enought to
> disconnect and reconnect again with the front as IBExpert (or exit and run
> the program). It's true, if don't do this, some time even not see the
> changes in some SP or TRIGGERS
>
> --- In firebird-support@yahoogroups.com, fabianoaspro@... wrote:
> >
> > Maybe I'm wrong but in my tests you need to recompile or do a full
> > backup/restore cycle.
> > Be sure you create the index Decending and that you put desc on both
> > statements when using the sql - like the another friend posted.
> > Em 16/04/2013 11:32, "skander_sp" <skander_sp@...> escreveu:
> >
> > > **
> > >
> > >
> > > Since when is necesary to recompile?
> > > Plans are not stored in the SP, the are calculated first time you use,
> > > after a connection
> > >
> > > May be necessary to disconnect and reconnect, but i doubt i need to
> > > recompile all my procedures to use new index.
> > >
> > > At least I read this ages ago, about the core of firebird.
> > > Or it's not?
> > >
> > > --- In firebird-support@yahoogroups.com, fabianoaspro@ wrote:
> > > >
> > > > 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]
> > > >
> > >
> > >
> > >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>
>
>

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