Re: A DBMS implementation Question

--CELKO-- wrote:
> 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;"

True.

> "CREATE HASH
> B(b) ON foobar;" and "CREATE HASH A(a) ON foobar;" in their scheme.

False.
A hash can only be calculated if *all* data is supplied: WHERE a = 123 AND b = 'bla' --> index access possible WHERE a = 123 --> partial data --> *no* index access

That's the disadvantage of hashing.
So in that case you usually create two single column indexes instead and in the first case the optimizer can choose to use one index or both indexes or calculate a bitmap on the fly using both indexes.

The only way to use an 2-column index with "WHERE a = 123" in Teradata is a value ordered [instead of the default hash ordered] secondary index: It's still hashing, but now "CREATE INDEX (a, b) ORDER BY (a)" can be used for "WHERE a = 123"

Dieter Received on Sat Mar 01 2003 - 09:11:08 CET