Re: Functional Indexes?

Ian Michael Gumby wrote: > > >> Date: Tue, 8 Apr 2008 12:29:19 -0400 >> From: art@oninit.com >> To: im_gumby@hotmail.com >> CC: informix-list@iiug.org >> Subject: Re: Functional Indexes? >> >> Ian Michael Gumby wrote: >> > Ok sports fans, here's a really fun problem. >> > >> > Suppose you have a table with the following columns: >> > >> > Table Foo: >> > Column a: id (Integer) >> > Column b: type (single character) >> > Column c: sequence # >> > Column d: description (varchar up to 128 characters wide) >> > >> > Now there are 3 types of records: "A", "B","C" >> > >> > Here's where the fun begins. >> > >> > I need to ensure that if I have a record of type "C", the pair (id, >> > type) must be unique. >> > For the other record types, I need to allow for multiple entries per id. >> > >> > So how do you do this? >> > >> > The answer is create a functional index that ensures uniqueness for >> > records of type "C" per id. >> >> I don't think that a unique index would work, hmm, except perhaps by >> appending additional chars for types A & B that would force the key to >> be unique. >> >> You could do it with INSERT and UPDATE triggers calling a UDR. That >> would be minimally invasive for types A & B as there's no check to >> perform. Only for type C records would it have to look for a dup. >> >> Art S. Kagel >> Oninit > > You have to think beyond a simple index. > In Oracle, you have the ability to create a functional index a simple > one is to create an index on UPPER(last_name) which will create an > index of the last name in all upper case. > > The idea is that you create a functional index that is dependent on type > = C and the ID combined being unique. If the type doesn't = 'C', its > not going to be added to the functional index. > > You don't want to do insert triggers or a trigger at all because of the > overhead. > > Again the idea is to have a portable method that could be used in > different databases. I'm slightly lost Mikey. Run by me again why you can't use a functional index with ids? -- Ciao, Marco ______________________________________________________________________________ Marco Greco /UK /IBM Standard disclaimers apply! Structured Query Scripting Language http://www.4glworks.com/sqsl.htm 4glworks http://www.4glworks.com Informix on Linux http://www.4glworks.com/ifmxlinux.htm

On 8 Apr, 22:24, Marco Greco <ma...@4glworks.com> wrote: > Ian Michael Gumby wrote: > > >> Date: Tue, 8 Apr 2008 12:29:19 -0400 > >> From: a...@oninit.com > >> To: im_gu...@hotmail.com > >> CC: informix-l...@iiug.org > >> Subject: Re: Functional Indexes? > > >> Ian Michael Gumby wrote: > >> > Ok sports fans, here's a really fun problem. > > >> > Suppose you have a table with the following columns: > > >> > Table Foo: > >> > Column a: id (Integer) > >> > Column b: type (single character) > >> > Column c: sequence # > >> > Column d: description (varchar up to 128 characters wide) > > >> > Now there are 3 types of records: "A", "B","C" > > >> > Here's where the fun begins. > > >> > I need to ensure that if I have a record of type "C", the pair (id, > >> > type) must be unique. > >> > For the other record types, I need to allow for multiple entries per id. > > >> > So how do you do this? > > >> > The answer is create a functional index that ensures uniqueness for > >> > records of type "C" per id. > > >> I don't think that a unique index would work, hmm, except perhaps by > >> appending additional chars for types A & B that would force the key to > >> be unique. > > >> You could do it with INSERT and UPDATE triggers calling a UDR. That > >> would be minimally invasive for types A & B as there's no check to > >> perform. Only for type C records would it have to look for a dup. > > >> Art S. Kagel > >> Oninit > > > You have to think beyond  a simple index. > > In Oracle, you have the ability to create a functional index a simple > > one is to create an index  on  UPPER(last_name) which will create an > > index of the last name in all upper case. > > > The idea is that you create a functional index that is dependent on type > > = C and the ID combined being unique. If the type doesn't  =  'C', its > > not going to be added to the functional index. > > > You don't want to do  insert triggers or a trigger at all because of the > > overhead. > > > Again the idea is to have a portable method that could be used in > > different databases. > > I'm slightly lost Mikey. Run by me again why you can't use a functional index > with ids? > -- > Ciao, > Marco > ___________________________________________________________________________­___ > Marco Greco /UK /IBM                               Standard disclaimers apply! > > Structured Query Scripting Language          http://www.4glworks.com/sqsl.htm > 4glworks                                              http://www.4glworks.com > Informix on Linux                        http://www.4glworks.com/ifmxlinux.htm- Hide quoted text - > > - Show quoted text - He wants to want a functional index on just some rows of the table.

Marco Greco wrote: > Ian Michael Gumby wrote: >> >>> Date: Tue, 8 Apr 2008 12:29:19 -0400 >>> From: art@oninit.com >>> To: im_gumby@hotmail.com >>> CC: informix-list@iiug.org >>> Subject: Re: Functional Indexes? >>> >>> Ian Michael Gumby wrote: >>>> Ok sports fans, here's a really fun problem. >>>> >>>> Suppose you have a table with the following columns: >>>> >>>> Table Foo: >>>> Column a: id (Integer) >>>> Column b: type (single character) >>>> Column c: sequence # >>>> Column d: description (varchar up to 128 characters wide) >>>> >>>> Now there are 3 types of records: "A", "B","C" >>>> >>>> Here's where the fun begins. >>>> >>>> I need to ensure that if I have a record of type "C", the pair (id, >>>> type) must be unique. >>>> For the other record types, I need to allow for multiple entries per id. >>>> >>>> So how do you do this? >>>> >>>> The answer is create a functional index that ensures uniqueness for >>>> records of type "C" per id. >>> I don't think that a unique index would work, hmm, except perhaps by >>> appending additional chars for types A & B that would force the key to >>> be unique. >>> >>> You could do it with INSERT and UPDATE triggers calling a UDR. That >>> would be minimally invasive for types A & B as there's no check to >>> perform. Only for type C records would it have to look for a dup. >>> >>> Art S. Kagel >>> Oninit >> You have to think beyond a simple index. >> In Oracle, you have the ability to create a functional index a simple >> one is to create an index on UPPER(last_name) which will create an >> index of the last name in all upper case. >> >> The idea is that you create a functional index that is dependent on type >> = C and the ID combined being unique. If the type doesn't = 'C', its >> not going to be added to the functional index. >> >> You don't want to do insert triggers or a trigger at all because of the >> overhead. >> >> Again the idea is to have a portable method that could be used in >> different databases. > > I'm slightly lost Mikey. Run by me again why you can't use a functional index > with ids? He was too busy with his latest posts so that he couldn't check the docs :) or... He still uses IDS 7... or... his function is variant... or... he his checking how many IBMers are certified on IDS... or... ???? Just for fun... don't take me serious! Regards. -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...