RE: Functional Indexes?

Trying to corner the frozen orange juice market, eh Mortimer? (Trading places for those who are not movie buffs.) Ok first issue is the design. No I didn't do it. I just have to live with it. Is the overloading of a comments table valid? Sure. Some types of comments can only have one record and are limited by the width of the var char. Other types of comments could be multiple records. An example use case. I have a product table, and then I have a product comments table. (Thats why the sequence number isn't a serial its an int.) Now the main application only deals with one record of TYPE='C' however we also bulk load data and well, my team doesn't control the data cleansing. The pk is on id because that's how you join or look up based on the id. Now of course I'm giving a scaled down example so I don't violate the NDA, and the column id could be, oh lets say a product_id, or something like that. So then your table looks like: product_id, type sequence Text_String The issue is that Oracle does allow you to have multiple records in a unique index with null. So the Oracle solution essentially says, if the row isn't of TYPE='C' then use NULL in the index, otherwise use the id. Its a very cool oracle centric kludge. (Unless DB2 allows it.) What you end up with is an unique index where you're storing only the ids of TYPE='C' and a NULL record. Very efficient. So how does IBM DB2 handle this? > From: srielau@ca.ibm.com > Subject: Re: Functional Indexes? > Date: Wed, 9 Apr 2008 11:01:14 -0400 > To: informix-list@iiug.org > > Sorry the "peanut gallery" was at wall street yesterday. And these guys > aren't famous for their open internet connections. > In DB2 you use an "expression generated column" instead of fucntion baed > index. > You can then index that column. > The "unique where not null" issue remains however. > > I too question the table design. > Especially the concept of an ID not being an ID seems to give a hint... > > Cheers > Serge > > -- > Serge Rielau > DB2 Solutions Development > IBM Toronto Lab > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list _________________________________________________________________ More immediate than e-mail? Get instant access with Windows Live Messenger. http://www.windowslive.com/messenger/overview.html?ocid=TXT_TAGLM_WL_Refresh_instantaccess_042008

"Some types of comments can only have one record and are limited by the width of the var char." So you are working around Oracle's limit of VARCHAR(4000) and now you are blaming DB2 because your workaround that isn't needed in Db2 to begin with doesn't work.... Hint: Use a trigger Cheers Serge PS: And now let's quit talking DB2 here. It is, after all, considered a swear word in these halls. -- Serge Rielau DB2 Solutions Development IBM Toronto Lab

Serge, Its posts like these which make me question your intelligence. Lets try this again. I have a table: product_id int type char sequence int text varchar(128) There is a PK based on product_id. type can be one of the following: ('A','B','C') If type = 'B' there can be multiple entries, hence the sequence number to order the rows of data. If type = 'C' there can only be one record per product_id. So, how do you create an index that would enforce the unique constraint the for a given record of type='C' that there can only be one entry per product_id? Hint: The solution in Oracle is to create a unique functional index with an inline function that contains something like " (CASE type='C' THEN product_id END;)" There is no simple solution in IDS, unless you create a VII that mimics Oracle's "feature" that allows you to enter multiple rows using a null value for product_id. This would yield a backing index for only records that have type='C'. Using a before insert trigger could work in all databases, however you incur a bit of overhead. So how can you do this in DB2? Does DB2 also allow multiple rows where the key value is NULL? (IDS only allows one NULL record to be inserted when there is a unique key in place.) The question is really "Here's how its done in Oracle. Now how is it done in IDS and in DB2." -G > From: srielau@ca.ibm.com > Subject: Re: Functional Indexes? > Date: Wed, 9 Apr 2008 15:00:27 -0400 > To: informix-list@iiug.org > > "Some types of comments can only have one record and are limited by the > width of the var char." > So you are working around Oracle's limit of VARCHAR(4000) and now you > are blaming DB2 because your workaround that isn't needed in Db2 to > begin with doesn't work.... > > Hint: Use a trigger > > Cheers > Serge > > PS: And now let's quit talking DB2 here. It is, after all, considered a > swear word in these halls. > -- > Serge Rielau > DB2 Solutions Development > IBM Toronto Lab > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list _________________________________________________________________ More immediate than e-mail? Get instant access with Windows Live Messenger. http://www.windowslive.com/messenger/overview.html?ocid=TXT_TAGLM_WL_Refresh_instantaccess_042008

Well why we are at reacaping: My first post: In DB2 you use an "expression generated column" instead of fucntion baed index. You can then index that column. The "unique where not null" issue remains however." Note that last sentence! Then: "Hint: Use a trigger" Lets combine these two sentences: DB2 does support a feature roughly comparable with function based indexes. It does NOT support multiple NULLs in unique constraints/indexes (An orthogonal issue). Therefore use a trigger. If you had paid attention when you worked for IBM you would know that in DB2 for LUW triggers are INLINE. While they are more expensive than an index they are a lot cheaper by design than on Oracle or IDS. For example in DB2 the cost of a check constraint and the equivalent before trigger is identical. In your typical single row insert scenario and a NON unique index on the columns the overhead should be very low indeed fro your problem. Cheers Serge -- Serge Rielau DB2 Solutions Development IBM Toronto Lab

Serge, While I was at IBM I was in the position of pimping the lab services consultants and per management directive, I was not allowed to get my hands dirty. So I didn't get a chance to play with DB2. And why would I? I have no plans on working on a Z/OS or an I series box. ;-) It would be nice to see an example of a DB2 inline trigger. Yes I know that DB2 like IDS doesn't allow multiple null entries in to an index. Interestingly enough, it was said that this "feature" breaks the relational model. I don't think so but I'm not to actually going to hunt down my 20 year old copy of Date's book. So your use of a DB2 trigger could work. Again show me how DB2's inline triggers are different from the triggers we see in IDS and Oracle. Sure this is an IDS forum, and I'd like to see a solution using IDS. IMHO, the interesting solution would be to write a VII that simulates an Oracle index. (And yes, there is some madness to my method or is it method to my madness? What do I know? ;-) -G > From: srielau@ca.ibm.com > Subject: Re: Functional Indexes? > Date: Wed, 9 Apr 2008 16:03:09 -0400 > To: informix-list@iiug.org > > Well why we are at reacaping: > My first post: > In DB2 you use an "expression generated column" instead of fucntion baed > index. > You can then index that column. > The "unique where not null" issue remains however." > Note that last sentence! > > Then: > "Hint: Use a trigger" > > Lets combine these two sentences: > DB2 does support a feature roughly comparable with function based > indexes. It does NOT support multiple NULLs in unique > constraints/indexes (An orthogonal issue). > Therefore use a trigger. > > If you had paid attention when you worked for IBM you would know that in > DB2 for LUW triggers are INLINE. While they are more expensive than an > index they are a lot cheaper by design than on Oracle or IDS. > > For example in DB2 the cost of a check constraint and the equivalent > before trigger is identical. > > In your typical single row insert scenario and a NON unique index on the > columns the overhead should be very low indeed fro your problem. > > Cheers > Serge > -- > Serge Rielau > DB2 Solutions Development > IBM Toronto Lab > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list _________________________________________________________________ Pack up or back up–use SkyDrive to transfer files or keep extra copies. Learn how. hthttp://www.windowslive.com/skydrive/overview.html?ocid=TXT_TAGLM_WL_Refresh_skydrive_packup_042008

Ian Michael Gumby wrote: > Serge, > > Its posts like these which make me question your intelligence. > Lets try this again. > > I have a table: > product_id int > type char > sequence int > text varchar(128) > > There is a PK based on product_id. A PK cannot by design, be NULL... Which is different from a unique Index > > type can be one of the following: ('A','B','C') > > If type = 'B' there can be multiple entries, hence the sequence number > to order the rows of data. > If type = 'C' there can only be one record per product_id. > > So, how do you create an index that would enforce the unique constraint > the for a given record of type='C' that there can only be one entry per > product_id? > > Hint: The solution in Oracle is to create a unique functional index with > an inline function that contains something like " (CASE type='C' THEN > product_id END;)" > > There is no simple solution in IDS, unless you create a VII that mimics There is, if you don't need to twist the relational model... Assuming you can deal with a unique index instead of a PK: DROP TABLE test; DROP FUNCTION idx_func; CREATE TABLE test ( c_product_id int, c_type char, c_sequence int, c_text varchar(128) ); CREATE FUNCTION idx_func(v_type CHAR, v_prod_id INT, v_seq INT) RETURNINGCHAR(11) WITH (NOT VARIANT); DEFINE v_ret CHAR(11); IF v_type = "C" THEN LET v_ret = "C"||v_prod_id; ELSE LET v_ret = "O"||v_seq; END IF RETURN v_ret; END FUNCTION; create unique index idx_test on test(idx_func(c_type, c_product_id, c_sequence)); insert into test (c_product_id, c_type, c_sequence, c_text) values (1,"A",1,"A1"); insert into test (c_product_id, c_type, c_sequence, c_text) values (2,"B",2,"B2"); insert into test (c_product_id, c_type, c_sequence, c_text) values (3,"C",3,"C3"); insert into test (c_product_id, c_type, c_sequence, c_text) values (2,"B",4,"B2"); select "And now... the error: " from systables where tabid = 1; insert into test (c_product_id, c_type, c_sequence, c_text) values (3,"C",5,"C3");# ^ # 239: Could not insert new row - duplicate value in a UNIQUE INDEX column (U# 100: ISAM error: duplicate value for a record with unique key. # # > Oracle's "feature" that allows you to enter multiple rows using a null > value for product_id. > This would yield a backing index for only records that have type='C'. > Feature or design flaw... pick whichone you prefer :) > Using a before insert trigger could work in all databases, however you > incur a bit of overhead. > > So how can you do this in DB2? I have no idea, but this is not comp.databases.ibm.... > Does DB2 also allow multiple rows where the key value is NULL? (IDS only > allows one NULL record to be inserted when there is a unique key in place.) > > The question is really "Here's how its done in Oracle. Now how is it > done in IDS and in DB2." No... the real question is: Will you be able to look at DB2 manuals? We already know you couldn't read the Informix ones :P Joking... And yes... VII is great... but you don't need it for such trivial stuff. And yes... you can argue that the example function may not be very efficient... but if you don't want to mess with concatenation stuff you could use negative product_ids for "C" type and positive sequence numbers for the "O"ther records, assuming you product IDs and sequences are all positive... Just an example... Now that you have the syntax (although you didn't RTFabulousM) you can play with it... Regards, -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...

Ian Michael Gumby wrote: > Sure this is an IDS forum, and I'd like to see a solution using IDS. > IMHO, the interesting solution would be to write a VII that simulates an > Oracle index. Argh... Why would you want to use such a refine tool to do such a dirty work?! :P

> > >> Oracle's "feature" that allows you to enter multiple rows using a null >> value for product_id. >> This would yield a backing index for only records that have type='C'. >> > > Feature or design flaw... pick whichone you prefer :) > Sigh. Unique index, not primary key. Repeat after me. Product_id, primary_key = no nulls for me.

Ian Michael Gumby wrote: > It would be nice to see an example of a DB2 inline trigger. Well, I try very hard to be nice. It's a challenge sometimes: Here is a trivial example: CREATE TABLE T(id INT); CREATE TRIGGER trg BEFORE INSERT ON T REFERENCING NEW AS N FOR EACH ROW WHEN (EXISTS(SELECT 1 FROM T WHERE id = N.id))SIGNAL SQLSTATE '78000' SET MESSAGE_TEXT = 'Not unique'; ------ db2 => INSERT INTO T VALUES (1); DB20000I The SQL command completed successfully. db2 => INSERT INTO T VALUES (NULL); DB20000I The SQL command completed successfully. db2 => INSERT INTO T VALUES (1); DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0438N Application raised error or warning with diagnostic text: "Not unique". SQLSTATE=78000 db2 => INSERT INTO T VALUES (NULL); DB20000I The SQL command completed successfully. ---- Now let's see what DB2 does. EXPLAIN PLAN FOR INSERT INTO T VALUES (1); Check out SCAN (8) over T. That's your EXISTS right there. The SCAN contains a SARGable (Type 2) predicate which knows about the constant 1! That is the optimizer has pushed the INSERT input deep into the trigger logic and obviously the trigger is part of the plan. In real life there would be an index of course, so there would be no scan. Just a single row index fetch. OK and now let's really shut the DB2 part of this discussion down. If you want to continue you know where to find me. !db2exfmt -d sample -o unique.exfmt -1 Original Statement: ------------------ INSERT INTO T VALUES (1) Optimized Statement: ------------------- $WITH CONTEXT$($TRIGGER$(SRIELAU.TRG)) INSERT INTO SRIELAU.T AS Q10 SELECT 1 FROM (SELECT $INTERNAL_CONSEC$() FROM (VALUES 1) AS Q1) AS Q2 WHERE $INTERNAL_PRED$ Access Plan: ----------- Total Cost: 15.2861 Query Degree: 1 Rows RETURN ( 1) Cost I/O | 0.04 INSERT ( 2) 15.2861 2 /----+----\\ 0.04 180 FILTER TABLE: SRIELAU ( 3) T 7.72142 1 +--------------------------++------------------------+ 1 1 7.2 TBSCAN NLJOIN TBSCAN ( 4) ( 5) ( 8) 4.29833e-005 0.00055778 7.71846 0 0 1 | /-------+------\\ | 1 1 1 180 TABFNC: SYSIBM TBSCAN TBSCAN TABLE: SRIELAU GENROW ( 6) ( 7) T 4.29833e-005 0.00020238 0 0 | | 1 1 TABFNC: SYSIBM TABFNC: SYSIBM GENROW GENROW ... 8) TBSCAN: (Table Scan) Cumulative Total Cost: 7.71846 Cumulative CPU Cost: 442375 Cumulative I/O Cost: 1 Cumulative Re-Total Cost: 0.140334 Cumulative Re-CPU Cost: 391782 Cumulative Re-I/O Cost: 0 Cumulative First Row Cost: 7.59751 Estimated Bufferpool Buffers: 1 Arguments: --------- CUR_COMM: (Currently Committed) TRUE LCKAVOID: (Lock Avoidance) TRUE MAXPAGES: (Maximum pages for prefetch) 1 PREFETCH: (Type of Prefetch) NONE ROWLOCK : (Row Lock intent) SHARE (CS/RS) SCANDIR : (Scan Direction) FORWARD SKIP_INS: (Skip Inserted Rows) TRUE TABLOCK : (Table Lock intent) INTENT SHARE TBISOLVL: (Table access Isolation Level) CURSOR STABILITY Predicates: ---------- 6) Sargable Predicate Comparison Operator: Equal (=) Subquery Input Required: No Filter Factor: 0.04 Predicate Text: -------------- (Q7.ID = 1) ... -- Serge Rielau DB2 Solutions Development IBM Toronto Lab

Mark Townsend wrote: > >> >> >>> Oracle's "feature" that allows you to enter multiple rows using a >>> null value for product_id. >>> This would yield a backing index for only records that have type='C'. >>> >> >> Feature or design flaw... pick whichone you prefer :) >> > > > Sigh. Unique index, not primary key. Repeat after me. Product_id, > primary_key = no nulls for me. I could ask you to repeat with me everything that was written here about it... That would mean I would give the context, something you choose not to do here... I'm in the group that believes a primary key should not be null. But I was born just a few years before Codd started this big madness, so I'll leave that discussion to others... -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...

> I'm in the group that believes a primary key should not be null. We are all in the same group. Primary keys may not contain nulls in Oracle either. Unique indexes can. Unless of course you add a NOT NULL constraint.

On 9 Apr, 20:36, Ian Michael Gumby <im_gu...@hotmail.com> wrote: > Serge, > > Its posts like these which make me question your intelligence. > Lets try this again. > > I have a table: > product_id  int > type           char > sequence   int > text           varchar(128) > > There is a PK based on product_id. > > type can be one of the following: ('A','B','C') > > If type = 'B' there can be multiple entries, hence the sequence number to order the rows of data. > If type = 'C' there can only be one record per product_id. > > So, how do you create an index that would enforce the unique constraint the for a given record of type='C' that there can only be one entry per product_id? You don't need an index. You just want to constrain sequence to 1 when the type is "C", so ... CHECK (type IN ("A","B") OR (type = "C" AND sequence = 1)) ... or am I missing something?

richard.harnden@googlemail.com wrote: > On 9 Apr, 20:36, Ian Michael Gumby <im_gu...@hotmail.com> wrote: > >> Serge, >> >> Its posts like these which make me question your intelligence. >> Lets try this again. >> >> I have a table: >> product_id int >> type char >> sequence int >> text varchar(128) >> >> There is a PK based on product_id. >> >> type can be one of the following: ('A','B','C') >> >> If type = 'B' there can be multiple entries, hence the sequence number to order the rows of data. >> If type = 'C' there can only be one record per product_id. >> >> So, how do you create an index that would enforce the unique constraint the for a given record of type='C' that there can only be one entry per product_id? >> > > You don't need an index. You just want to constrain sequence to 1 > when the type is "C", so ... > > CHECK (type IN ("A","B") OR (type = "C" AND sequence = 1)) > > ... or am I missing something? Oo, thinking outside the box! I like that! Good catch Richard! Art S. Kagel Oninit