Re: Whatcha' wanta have?????

Serge Rielau wrote: > This feature applies to unique indexes. > Let's presume you have an employee table. The PK is empno. > You want to optimize lookups of employee names by empno. > Without this feature the fastest to do this (assuming a non trivial > table size) is to fetch the rowid from the index and then do an fetch > by rowid from the table to get the name. > If you INCUDE empname in the unique index then you save the the table > access. Another way of looking at this is as a non-unique index with a > unique subset. OK - and like Neil, I'm cynical about people stuffing all sorts of fields in there and then complaining "oh, my query's are so slowwww". Also, an index like this needs modifying if you change the empname - sounds like many people will lose in the straights as you gain in the roundabouts.... > Index ANDing. Let's presume an index on the employee table by name and > one on department. You want to look up Mr. Smith in marketing. > The DBMS first collects all rowid's for Smith and then all for the > employees in the marketing department. It can then intersect the sets > and go after the real rows. ummmmmm - ok - if the selectivity of both is quite high, then it can help. I'd wager that it help less often than you might think ?? > > Cheers > Serge

"Andrew Hamm" <ahamm@mail.com> wrote in message news:c0p1kf$18fcsm$1@ID-79573.news.uni-berlin.de... > > Index ANDing. Let's presume an index on the employee table by name and > > one on department. You want to look up Mr. Smith in marketing. > > The DBMS first collects all rowid's for Smith and then all for the > > employees in the marketing department. It can then intersect the sets > > and go after the real rows. > > ummmmmm - ok - if the selectivity of both is quite high, then it can help. > I'd wager that it help less often than you might think ?? Not so sure. Way back when I was working for Sperry, the DMS-1100 product had via-set records that could be stored either as links within the row or in an index-pointer array. The addresses within the IPA would then point to the record within the set. A common way of improving the selectivity of several set intersections was to fetch the IPA for the various sets and then select the intersection by using two way, three way, etc. match/merge of the addresses and then fetch the actual records via the the reduced address array. It made a significant improvement in the select/fetch performance. The index ANDing is nothing more than a fancy term for what was being done back with IPAs in the codasyl DBMS days. M.P. > > > > > > Cheers > > Serge > >