Re: A DBMS implementation Question

From: --CELKO-- <>
Date: 28 Feb 2003 13:29:30 -0800
Message-ID: <>

This is a problem with most tree-based indexes. The usual solutions are to be sure that your picked the right order -- that is, "CREATE INDEX AB(a, b) ON Foobar;" versus "CREATE INDEX BA(b, a) ON Foobar;" can make a difference.

The "CREATE INDEX AB(a, b) ON foobar;" statement also gives the same effect as "CREATE INDEX A(a) ON foobar;".

On the other hand, the Teradata hashing scheme gives us the equivalent of hashing for any combination of columns. That is "CREATE HASH AB(a, b) ON foobar;" implies "CREATE HASH AB(b, a) ON foobar;" "CREATE HASH B(b) ON foobar;" and "CREATE HASH A(a) ON foobar;" in their scheme. NO access can take more than two probes, GROUP BY operations are much easier (the same values all fall in the same hash bucket), etc.

On the third hand, the Nucleus engine and Sybase IQ use bit vectors so that all the columns are automatically fully indexed.

I do not know of a good book with a high level overview of the various methods, but indexes are certainly not the only way to go. Received on Fri Feb 28 2003 - 22:29:30 CET

Original text of this message