Try this:

CREATE TABLE ORDENES (

ID_ORDEN INTEGER NOT NULL,

FECHA DATE NOT NULL,

EJER INTEGER,

N_ORDEN INTEGER

);

CREATE DESCENDING INDEX ORDENES_IDX1 ON ORDENES COMPUTED BY
(ejer||';'||n_orden);

select first 1 (ejer||';'||n_orden), n_orden

from ordenes

where (ejer||';'||n_orden) like '2013;%'

order by 1 desc

Just one indexes read!
Em 16/04/2013 13:40, <fabianoaspro@...> escreveu:

> This index uk_... Is the descending one?
> Give-me the ddl of this index.
> Em 16/04/2013 13:06, "skander_sp" <skander_sp@...> escreveu:
>
>> **
>>
>>
>> the plan is PLAN (O INDEX (UK_ORDENES_PRODUCCION_N_ORDEN))
>> and the readed rows it depend of the year...
>> really i love to minimize the read for the last one year (the most used)
>> but the only year read only a row is other with the higher N_Orden, all the
>> rest read between some dozens and several hundreds.
>>
>> --- In firebird-support@yahoogroups.com, fabianoaspro@... wrote:
>> >
>> > 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]
>> >
>>
>>
>>
>

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