Re: Functional Indexes?

Ian Michael Gumby wrote: Gumby: As one of those who, privately, said that the table design breaks the relational model, I beg to differ. It does. Been writing normalized relational model databases since a few weeks after Codd's article broke (1980?). Damn I'm old! Coded them at first by hand, and later using a long parade of relational databases starting with Grumman's RIM truly the very first database engine to support the relational model in any form. But enough rambling. It's not the index that causes the breakage, but the entire notion of multiple row types in a single table using a 'type' field to differentiate. The fact that one of the row types has a different set of integrity constraints on it than the rest of the row types in the table is simply one proof that the model is broken. The fact that for the other row types the primary key is not unique is proof. If you want to do this in a purely relational way then there should be a master record table with several different child tables one for each content type each related back to the master record which is unique. Each row of each of the child tables should have a unique key as well some identifier or sequence number. How else can you tell one type 'A' record from another or order them correctly when you select them? With that correctly done, the type field in the master record will point to the correct child table and each child table can have its own constraints and all is simple and easy. You can also do this using IDS's OO features so that fetching the master record brings in the correct child record type. That's not relational either, but it just fits a different model and the model is supported by the engine. The way you have things now you are getting no schema support for your model from the engine. Even Orable's ISAMish index kludge and the functional index workaround that I sent to you directly are not really model support, just work arounds for the models the engine is supposed to be enforcing. Art S. Kagel Oninit > > > > From: markbtownsend@sbcglobal.net > > Subject: Re: Functional Indexes? > > Date: Wed, 9 Apr 2008 05:52:42 +0000 > > To: informix-list@iiug.org > > > > Ian Michael Gumby wrote: > > > > > The solution in Oracle is a neat little inline function. I was > shocked. > > > So what is the solution in DB2 and IDS? Postgres? > > > > Hmm - I'm guessing by returning nulls for the non C values so it's a > > simple case statement or nvl call ? > > > > If so, the real question is - Does IDS allow nulls in a unique key ? > > > > (a little featurette in Oracle that suprisingly, some people don't like) > > > Yes, that's exactly it. A simple case statement which would return > either the id, or NULL. > You can't do that in Informix, and there wasn't a comment from the DB2 > peanut gallery. > > While someone did point out that it "broke the relational model", it > doesn't. > This index would be a backing store index to guarantee uniqueness. > Some could have said that the table organization wasn't a good model. > While you could argue it, you could also claim that it was because > you're creating a table that differentiates the type of varchar texts > by type. > > The interesting thing is the VII. It is theorectically possible to > create your own index type that mimics the behavior that you find in > Oracle. (I don't know VII, so I'll leave that open to a true VII guru) > > So thanks to those who actually tried to answer the question. Of > course for the individual who gave the "RFTM" here's the section on > functional indexes, you didn't actually answer the question and well > frankly, the "manual" didn't help. > > The interesting exercise would be to write a VII to do exactly this. > It actually has some unique uses and as long as you're not using it > for a PK or FK, you're not going to break anything. > > But hey! What do I know? When you support C/C++ Objective C, Python, > Java, etc ... and a couple of relational databases, its difficult to > keep things straight. > > Oh and to be fair, the Oracle reference manuals were about as useful > as tits on a boar. The solution came from an Oracle DBA guru who's > been working with the Oracle produvt for years. And of course, he > knows a bit more about Oracle than most of the employees working > there. (He's one of the ones who found out why you got terrible > performance in R-Tree Indexes in 9i) ;-) > > -G > > PS. The point is that if you're going to mimic Oracle so you can make > migration of Oracle to IDS easier, this is something you'd want to do. > Also the fact that you *can* define your own indexes, means that your > model could support multiple indexing types while Oracle and others > can't. (Now that's a big feature hint.) >

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_descchar(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.... And yes i did not like the fact that obstacle allowed for more then one sqlnull in a unique index... well if one knows then you can live with it and take countermeasures. Superboer. way fast=http://www.clipjes.nl/clip/nederlands/n/normaal_- _oerend_hard.html On 9 apr, 16:21, "Art S. Kagel (Oninit)" <a...@oninit.com> wrote: > Ian Michael Gumby wrote: > > Gumby: > > As one of those who, privately, said that the table design breaks the > relational model, I beg to differ. It does. Been writing normalized > relational model databases since a few weeks after Codd's article broke > (1980?). Damn I'm old! Coded them at first by hand, and later using a > long parade of relational databases starting with Grumman's RIM truly > the very first database engine to support the relational model in any > form. But enough rambling. > > It's not the index that causes the breakage, but the entire notion of > multiple row types in a single table using a 'type' field to > differentiate. The fact that one of the row types has a different set > of integrity constraints on it than the rest of the row types in the > table is simply one proof that the model is broken. The fact that for > the other row types the primary key is not unique is proof. If you want > to do this in a purely relational way then there should be a master > record table with several different child tables one for each content > type each related back to the master record which is unique. Each row > of each of the child tables should have a unique key as well some > identifier or sequence number. How else can you tell one type 'A' > record from another or order them correctly when you select them? With > that correctly done, the type field in the master record will point to > the correct child table and each child table can have its own > constraints and all is simple and easy. > > You can also do this using IDS's OO features so that fetching the master > record brings in the correct child record type. That's not relational > either, but it just fits a different model and the model is supported by > the engine. > > The way you have things now you are getting no schema support for your > model from the engine. Even Orable's ISAMish index kludge and the > functional index workaround that I sent to you directly are not really > model support, just work arounds for the models the engine is supposed > to be enforcing. > > Art S. Kagel > Oninit > > > > > > From: markbtowns...@sbcglobal.net > > > Subject: Re: Functional Indexes? > > > Date: Wed, 9 Apr 2008 05:52:42 +0000 > > > To: informix-l...@iiug.org > > > > Ian Michael Gumby wrote: > > > > > The solution in Oracle is a neat little inline function. I was > > shocked. > > > > So what is the solution in DB2 and IDS? Postgres? > > > > Hmm - I'm guessing by returning nulls for the non C values so it's a > > > simple case statement or nvl call ? > > > > If so, the real question is - Does IDS allow nulls in a unique key ? > > > > (a little featurette in Oracle that suprisingly, some people don't like) > > > Yes, that's exactly it. A simple case statement which would return > > either the id, or NULL. > > You can't do that in Informix, and there wasn't a comment from the DB2 > > peanut gallery. > > > While someone did point out that it "broke the relational model", it > > doesn't. > > This index would be a backing store index to guarantee uniqueness. > > Some could have said that the table organization wasn't a good model. > > While you could argue it, you could also claim that it was because > > you're creating a table that differentiates the type of varchar texts > > by type. > > > The interesting thing is the VII. It is theorectically possible to > > create your own index type that mimics the behavior that you find in > > Oracle. (I don't know VII, so I'll leave that open to a true VII guru) > > > So thanks to those who actually tried to answer the question. Of > > course for the individual who gave the "RFTM" here's the section on > > functional indexes, you didn't actually answer the question and well > > frankly, the "manual" didn't help. > > > The interesting exercise would be to write a VII to do exactly this. > > It actually has some unique uses and as long as you're not using it > > for a PK or FK, you're not going to break anything. > > > But hey! What do I know? When you support C/C++ Objective C, Python, > > Java, etc ... and a couple of relational databases, its difficult to > > keep things straight. > > > Oh and to be fair, the Oracle reference manuals were about as useful > > as tits on a boar. The solution came from an Oracle DBA guru who's > > been working with the Oracle produvt for years. And of course, he > > knows a bit more about Oracle than most of the employees working > > there. (He's one of the ones who found out why you got terrible > > performance in R-Tree Indexes in 9i) ;-) > > > -G > > > PS. The point is that if you're going to mimic Oracle so you can make > > migration of Oracle to IDS easier, this is something you'd want to do. > > Also the fact that you *can* define your own indexes, means that your > > model could support multiple indexing types while Oracle and others > > can't. (Now that's a big feature hint.)