Re: Functional Indexes?

Superboer wrote: > Hello Mikey, > > Art was right; however i disagree on the rowid part. (fragmented > tables!!! it's time that that is allowed/implemenented GRMBLL) > > > anyways: > > create table mikey( a_id int, b_type char(1), c_seq serial, d_desc> char(100), primary key (c_seq) ); > -->> i assume you have a primary key on your table...... which is > c_seq... > > create function mikeyfun ( a int, b char(1), c int) > returning char(100) > with > (not variant)> ; > > if ( b = "C") THEN > return a||b; > else > return a||b||c; > end if; > end function; > > create unique index mikeyix on mikey(mikeyfun(a_id, b_type,c_seq ));> > insert into mikey values (1,"D",0,"myfirstone"); > insert into mikey values (1,"D",0,"mysecond one");> > insert into mikey values (1,"C",0,"myfirstone"); > insert into mikey values (1,"C",0,"should fail");> # ^ > # 239: Could not insert new row - duplicate value in a UNIQUE INDEX > column. > # > # 100: ISAM error: duplicate value for a record with unique key. > # > > Assume the above is what you are after.... > Argh... I promise not to post without reading the whole thread... I wrote the same thing... :/ But it's funny to see how we got to the same (obvious) solution... >

Yes, this would have been the solution that I would have written even in Oracle. What amazed me was that the solution my Oracle Guru came up with was an inline function. To me, that was non-obvious and takes advantage of how Oracle handles nulls. (See my earlier post). The non-obvious IDS solution would be to write an Index that mimics how Oracle does things. Why? Because it would make life easier when you *port* *from* Oracle to IDS. ;-) > From: spam@onlinedomus.net> Subject: Re: Functional Indexes?> Date: Thu, 10 Apr 2008 01:16:23 +0100> To: informix-list@iiug.org> > Superboer wrote:> > Hello Mikey,> > > > Art was right; however i disagree on the rowid part. (fragmented> > tables!!! it's time that that is allowed/implemenented GRMBLL)> > > > > > anyways:> > > > create table mikey( a_id int, b_type char(1), c_seq serial, d_desc> > char(100), primary key (c_seq) );> > -->> i assume you have a primary key on your table...... which is> > c_seq...> > > > create function mikeyfun ( a int, b char(1), c int)> > returning char(100)> > with> > (not variant)> > ;> > > > if ( b = "C") THEN> > return a||b;> > else> > return a||b||c;> > end if;> > end function;> > > > create unique index mikeyix on mikey(mikeyfun(a_id, b_type,c_seq ));> > > > insert into mikey values (1,"D",0,"myfirstone");> > insert into mikey values (1,"D",0,"mysecond one");> > > > insert into mikey values (1,"C",0,"myfirstone");> > insert into mikey values (1,"C",0,"should fail");> > # ^> > # 239: Could not insert new row - duplicate value in a UNIQUE INDEX> > column.> > #> > # 100: ISAM error: duplicate value for a record with unique key.> > #> > > > Assume the above is what you are after....> > > > Argh... I promise not to post without reading the whole thread... I wrote the > same thing... :/> But it's funny to see how we got to the same (obvious) solution...> > > _______________________________________________> Informix-list mailing list> Informix-list@iiug.org> http://www.iiug.org/mailman/listinfo/informix-list _________________________________________________________________ Use video conversation to talk face-to-face with Windows Live Messenger. http://www.windowslive.com/messenger/connect_your_way.html?ocid=TXT_TAGLM_WL_Refresh_messenger_video_042008

Fernando Nunes wrote: > Superboer wrote: > >> Hello Mikey, >> >> Art was right; however i disagree on the rowid part. (fragmented >> tables!!! it's time that that is allowed/implemenented GRMBLL) >> >> >> anyways: >> >> create table mikey( a_id int, b_type char(1), c_seq serial, d_desc>> char(100), primary key (c_seq) ); >> -->> i assume you have a primary key on your table...... which is >> c_seq... >> >> create function mikeyfun ( a int, b char(1), c int) >> returning char(100) >> with >> (not variant)>> ; >> >> if ( b = "C") THEN >> return a||b; >> else >> return a||b||c; >> end if; >> end function; >> >> create unique index mikeyix on mikey(mikeyfun(a_id, b_type,c_seq ));>> >> insert into mikey values (1,"D",0,"myfirstone"); >> insert into mikey values (1,"D",0,"mysecond one");>> >> insert into mikey values (1,"C",0,"myfirstone"); >> insert into mikey values (1,"C",0,"should fail");>> # ^ >> # 239: Could not insert new row - duplicate value in a UNIQUE INDEX >> column. >> # >> # 100: ISAM error: duplicate value for a record with unique key. >> # >> >> Assume the above is what you are after.... >> >> > > Argh... I promise not to post without reading the whole thread... I wrote the > same thing... :/ > But it's funny to see how we got to the same (obvious) solution... > And I sent virtually the same post directly to Gum Man at around the same time the Superboer posted it here. Great minds, eh? Art S. Kagel Oninit

Ian Michael Gumby wrote: > Yes, this would have been the solution that I would have written even in > Oracle. > What amazed me was that the solution my Oracle Guru came up with was an > inline function. > > To me, that was non-obvious and takes advantage of how Oracle handles nulls. > (See my earlier post). > > The non-obvious IDS solution would be to write an Index that mimics how > Oracle does things. > Why? Because it would make life easier when you *port* *from* Oracle to > IDS. ;-) > Using that wonderful index in Oracle... Try doing a query that could use the index and compares the index value to NULL and post the query plan... I'm curious... -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...

Art S. Kagel (Oninit) wrote: > And I sent virtually the same post directly to Gum Man at around the > same time the Superboer posted it here. Great minds, eh? No... I think we're lacking imagination :P -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...

Fernando Nunes wrote: > Art S. Kagel (Oninit) wrote: > >> And I sent virtually the same post directly to Gum Man at around the >> same time the Superboer posted it here. Great minds, eh? > > No... I think we're lacking imagination :P Not a kindred spirit of Anne of Green Gables... ;-) -- Serge Rielau DB2 Solutions Development IBM Toronto Lab

one more thing for that grumpy old mikey -->> basicly the same as Art explained in his email and hmmm showing his age... grin... how are you gonna maintain your table ?? you can not identify a row uniquely since you state you have multiple nulls in your unique index.... if you need to kick out one row..... or more??? or update one row.... This is not nice for people who have to maintain the stuff you build. but hey what do i know, i can not read the mind of a grumpy old Ian Michael Gumby Superboer. way fast=http://www.clipjes.nl/clip/nederlands/n/normaal_- _oerend_hard.html