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...@yahoo.com> 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] ------------------------------------ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
