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. Gum: The function in a functional index isn't what checks for uniqueness. It just takes in some column values and returns a value to become the key value for the row. If you want a UNIQUE index to allow non-unique column values if one of the columns has the value 'C' the only way is to set the index up as a unique index and for the function to return a unique key for rows with row type A or B but to return a possibly duplicated key for rows containing type 'C'. Example (pseudocode): keyfunc(col1, col2, type, rowid) { if (type == C) then return col1 || col2 || type || ' ' else return return col1 || col2 || type || rowid endif } So for types 'A' and 'B' the rowid is appended to the actually indexed key so that the key is guaranteed to be unique but for type 'C' rows only spaces are appended so there can be a dup key generated if the row already exists. The underlying unique index will reject the duplicate key. I don't know how a functional index will deal with a partial key, so you may not be able to use it for general searching. For full functionality you may have to use the VII to create the index that filters the way you want it to. This is what I alluded to in my first reply. Personally I don't see how that's much less overhead than the triggers, but YMMV so test the heck out of it. Art S. Kagel Oninit