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