Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: A DBMS implementation Question

Re: A DBMS implementation Question

From: Dieter Nöth <dnoeth_at_gmx.de>
Date: Sat, 01 Mar 2003 09:11:08 +0100
Message-ID: <b3psis$1ogpkq$1@ID-28204.news.dfncis.de>


--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 - 02:11:08 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US