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