RE: Functional Indexes?

> Date: Tue, 8 Apr 2008 22:24:43 +0100> From: marco@4glworks.com> > 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? I don't know. That's why I'm asking. Art pointed out about using a unique index. You can't use a regular index because you only want to guarantee uniqueness only for those records with TYPE='C'. I am asking what's the syntax of a functional index in IDS to do this. What I did say was that you don't want to use a trigger because of performance issues. The solution in Oracle is a neat little inline function. I was shocked. So what is the solution in DB2 and IDS? Postgres? _________________________________________________________________ Get in touch in an instant. Get Windows Live Messenger now. http://www.windowslive.com/messenger/overview.html?ocid=TXT_TAGLM_WL_Refresh_getintouch_042008

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)

> 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.) _________________________________________________________________ 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

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