Hashed file Indexed
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 83
- Joined: Tue Dec 19, 2006 8:38 am
Hashed file Indexed
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
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
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.
The only performance difference you will see in DataStage with hashed files is a degradation in write speed.
-
- Participant
- Posts: 83
- Joined: Tue Dec 19, 2006 8:38 am
Hi arndw,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 ...
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 83
- Joined: Tue Dec 19, 2006 8:38 am
But Ray in any database when you specify a key automatically there will be indexing on that column...Or am i wrong?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 ...
I got only half of ur point ray...
![Crying or Very sad :cry:](./images/smilies/icon_cry.gif)
Again premium poster, George you must need to become a premium member soon..
Thanks
George
-
- Participant
- Posts: 83
- Joined: Tue Dec 19, 2006 8:38 am
But Ray in any database when you specify a key automatically there will be indexing on that column...Or am i wrong?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 ...
I got only half of ur point ray...
![Crying or Very sad :cry:](./images/smilies/icon_cry.gif)
Again premium poster, George you must need to become a premium member soon..
Thanks
George
-
- Participant
- Posts: 83
- Joined: Tue Dec 19, 2006 8:38 am
But Ray in any database when you specify a key automatically there will be indexing on that column...Or am i wrong?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 ...
I got only half of ur point ray...
![Crying or Very sad :cry:](./images/smilies/icon_cry.gif)
Again premium poster, George you must need to become a premium member soon..
Thanks
George
-
- Participant
- Posts: 83
- Joined: Tue Dec 19, 2006 8:38 am
But Ray in any database when you specify a key automatically there will be indexing on that column...Or am i wrong?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 ...
I got only half of ur point ray...
![Crying or Very sad :cry:](./images/smilies/icon_cry.gif)
Again premium poster, George you must need to become a premium member soon..
Thanks
George
-
- Participant
- Posts: 83
- Joined: Tue Dec 19, 2006 8:38 am
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
Last edited by ray.wurlod on Fri Jul 06, 2007 9:29 am, edited 1 time in total.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 83
- Joined: Tue Dec 19, 2006 8:38 am