Re: an index question

dcruncher4@aim.com wrote: > In article <fle19n011r2@drn.newsguy.com>, dcruncher4@aim.com says... >> create index aaaa on table(col1 asc, col2 desc, col3 asc)>> >> The performance gain on the view was incredible. >> What can be the reason behind it. Can anyone explain >> the internals behind it. >> >> The only problem I see is that with this index I can not >> create a PKY or a unique constraint, and for that I am >> forced to create another index. > > I take it back. PKY can be created on an index with > desc columns but not as part of create table syntax. > Index has to be created first explicitly and then > PKY should be created. IDS will use the same > index. > Does that mean that there is some sort of a bug > here because one way of creating PKY allows > desc index to be used, while the other one does > not. > When you "create index" by defining a PK, Informix will automatically create an index with the defaults. The defaults are to use the ASC, to create the index using a special name etc. There is no BUG, only "defaults" in action. As for the performance increase, if you imagine a physical representation of the index it's easier to understand it...: 1 1 1 1 1 2 1 1 3 ... 1 9 1 (this is the "default") 1 9 1 1 8 1 ... 1 1 1 (this is the "desc" ) It's more probable that the "max(col2)" is "closer" from the record you're looking at in the second case... The real gain will probably vary a lot depending on your data distributions. Regards. -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...