LOL, nice try,... and really pretty curious, they "seems" works,
but don't do in this way.

May be you must remember in strings "9" is higher than "800000"
then for a (tricky) aproach to working way...

CREATE DESCENDING INDEX ix_ordenes_produccion_orden ON ordenes_produccion COMPUTED BY (cast(ejer*100000000+n_orden as char(12)));


and accessing with


select first 1 ejer, n_orden
from ordenes_produccion
where cast(ejer*100000000+n_orden as char(12)) like '2013%'
order by 1 desc


of course we are guessing not n_orden as high as 99.999.999 (but im pretty sure ;)

--- In firebird-support@yahoogroups.com, fabianoaspro@... wrote:
>
> 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]
>