Hashed file Indexed

georgesebastian · Post by **georgesebastian** » Tue Jul 03, 2007 1:00 am

Hi all,

When we create a hashed file with key columns, Indexes will be created for those columns Isntit?
If yes which one will be better/performance making columns as key or creating Index by Create.Index

Then will there be any advantage or difference if we create indexes for those columns separately by CREATE.INDEX ?

Thanks
George Sebastian :D

ArndW · Post by **ArndW** » Tue Jul 03, 2007 3:38 am

The secondary indices on hashed files are something completely different and are not used from DataStage. The indices created by CREATE.INDEX and BUILD.INDEX are on columns and are accessed when doing queries from the DataStage command line using commands such as SELECT (both the Inform and the SQL variants).

The only performance difference you will see in DataStage with hashed files is a degradation in write speed.

georgesebastian · Post by **georgesebastian** » Tue Jul 03, 2007 4:47 am

ArndW wrote:The secondary indices on hashed files are something completely different and are not used from DataStage. The indices created by CREATE.INDEX and BUILD.INDEX are on columns and are accessed when doing ...

Hi arndw,

I am sad to tell that i am not a premium member so i was not able to read your post fully.

can somebody retype Arndw's post..

thanks
George

ray.wurlod · Post by **ray.wurlod** » Tue Jul 03, 2007 7:14 am

No index is created on the key. Hashing algorithm is much faster than using an index.

Indexing key columns individually will only help if you run queries against the hashed file in which the report is constrained (or sorted) on those individual columns - in short, generally the answer is cost but no benefit.

georgesebastian · Post by **georgesebastian** » Tue Jul 03, 2007 9:24 pm

ray.wurlod wrote:No index is created on the key. Hashing algorithm is much faster than using an index.

Indexing key columns individually will only help if you run queries against the hashed file in which the repor ...

But Ray in any database when you specify a key automatically there will be indexing on that column...Or am i wrong?

I got only half of ur point ray...

Again premium poster, George you must need to become a premium member soon..
Thanks
George

georgesebastian · Post by **georgesebastian** » Tue Jul 03, 2007 9:26 pm

ray.wurlod wrote:No index is created on the key. Hashing algorithm is much faster than using an index.

Indexing key columns individually will only help if you run queries against the hashed file in which the repor ...

But Ray in any database when you specify a key automatically there will be indexing on that column...Or am i wrong?

I got only half of ur point ray...

Again premium poster, George you must need to become a premium member soon..
Thanks
George

georgesebastian · Post by **georgesebastian** » Tue Jul 03, 2007 9:27 pm

ray.wurlod wrote:No index is created on the key. Hashing algorithm is much faster than using an index.

Indexing key columns individually will only help if you run queries against the hashed file in which the repor ...

But Ray in any database when you specify a key automatically there will be indexing on that column...Or am i wrong?

I got only half of ur point ray...

Again premium poster, George you must need to become a premium member soon..
Thanks
George

georgesebastian · Post by **georgesebastian** » Tue Jul 03, 2007 9:27 pm

ray.wurlod wrote:No index is created on the key. Hashing algorithm is much faster than using an index.

Indexing key columns individually will only help if you run queries against the hashed file in which the repor ...

But Ray in any database when you specify a key automatically there will be indexing on that column...Or am i wrong?

I got only half of ur point ray...

Again premium poster, George you must need to become a premium member soon..
Thanks
George

ArndW · Post by **ArndW** » Tue Jul 03, 2007 9:28 pm

George - you meant to say "in any SQL compliant database". Pick type databases such as the UniVerse/DS engine work differently. Hashed files cannot be created without unique keys, the terms "key" and "index" are synonymous in this context for hashed file keys.

georgesebastian · Post by **georgesebastian** » Wed Jul 04, 2007 3:35 am

"George - you meant to say "in any SQL compliant database". Pick type databases such as the UniVerse/DS engine work differently. Hashed files cannot be created without unique keys, the terms "key" and ... "

Thanks Arndw;

Again half only....

Thanks
George sebastian

ray.wurlod · Post by **ray.wurlod** » Wed Jul 04, 2007 1:07 pm

In short, you're wrong.

UniVerse and DataStage ARE SQL-compliant, but aren't ruled by that.

There is nothing whatsoever in the SQL standards (either SQL CAE Access Group standards or ODBC standards) that requires that a key have an index. A primary key requires only a UNIQUE property.

If there is a faster, more efficient and more lightweight mechanism, then it should be used. Hashing algorithms fit this criterion nicely.

georgesebastian · Post by **georgesebastian** » Thu Jul 05, 2007 9:49 pm

Thanks Ray,

I got the point.I am learning ... :D

Thanks All,

George Sebastian