RE: Functional Indexes?

> From: spam@onlinedomus.net> Subject: Re: Functional Indexes?> Date: Thu, 10 Apr 2008 01:40:27 +0100> To: informix-list@iiug.org> > Ian Michael Gumby wrote:> > Yes, this would have been the solution that I would have written even in > > Oracle.> > What amazed me was that the solution my Oracle Guru came up with was an > > inline function.> > > > To me, that was non-obvious and takes advantage of how Oracle handles nulls.> > (See my earlier post).> > > > The non-obvious IDS solution would be to write an Index that mimics how > > Oracle does things.> > Why? Because it would make life easier when you *port* *from* Oracle to > > IDS. ;-)> > > > > Using that wonderful index in Oracle...> Try doing a query that could use the index and compares the index value to NULL > and post the query plan... I'm curious...> You don't. Its what some call a *BACKING INDEX* who's only purpose is to gurantee an uniqueness constraint. Thats the beauty of it. The primary key index is the ID column (product_id). The sequence isn't a serial because its a series to keep the comments in order. So prod_id =1 and has multiple comments of type A, could have multiple rows. You keep thinking in using this index as a way to get rows from the table. Its not. All its used for is to make sure that if you have a TYPE='C' that you can only have one row. _________________________________________________________________ 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

Ian Michael Gumby wrote: > > Using that wonderful index in Oracle... > > Try doing a query that could use the index and compares the index > value to NULL > > and post the query plan... I'm curious... > > > > You don't. > Its what some call a *BACKING INDEX* who's only purpose is to gurantee > an uniqueness constraint. > > Thats the beauty of it. The primary key index is the ID column > (product_id). The sequence isn't a serial because its a series to keep > the comments in order. So prod_id =1 and has multiple comments of type > A, could have multiple rows. > > You keep thinking in using this index as a way to get rows from the > table. Its not. All its used for is to make sure that if you have a > TYPE='C' that you can only have one row. I didn't express myself correctly. Someone was saying that it was good that Oracle could store several NULLs in a unique index. I suggest you test it with a query like "WHERE indexed_column IS NULL" and check the query plan. -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...