Sql-server equivalent for ORACLE's hash cluster

Posted on 2003-03-23
Medium Priority
Last Modified: 2006-11-17
I'm making this convertion from oracle to sql-server.
Some of my tables are loaded into a hash cluster for performance reasons. The idea
is to minimize I/O not having an index to maintain.

The question is if sql server 2000 has anything like this?

Question by:hwendt
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
LVL 13

Expert Comment

ID: 8193654
Can you post more about a ORA hash cluster.

In MSSQL2K you can use distributed partitioned views to increase performance and to split an index.

Author Comment

ID: 8194027
I'm not an expert in hash clusters, the link below explains:

Storing a table in a hash cluster is an optional way to improve the performance of data retrieval. A hash cluster provides an alternative to a nonclustered table with an index or an index cluster. With an indexed table or index cluster, Oracle locates the rows in a table using key values that Oracle stores in a separate index. To use hashing, you create a hash cluster and load tables into it. Oracle physically stores the rows of a table in a hash cluster and retrieves them according to the results of a hash function.

LVL 13

Accepted Solution

ispaleny earned 240 total points
ID: 8194136

This seems to be built into MSSQLSERVER2K also.
See BOL Topics "hash indices","CHECKSUM"

From BOL

Returns the checksum value computed over a row of a table, or over a list of expressions. CHECKSUM is intended for use in building hash indices.

-- Create a checksum index.
USE Northwind
ADD cs_Pname AS checksum(ProductName)
CREATE INDEX Pname_index ON Products (cs_Pname)

The checksum index can be used as a hash index, particularly to improve indexing speed when the column to be indexed is a long character column. The checksum index can be used for equality searches.

/*Use the index in a SELECT query. Add a second search
condition to catch stray cases where checksums match,
but the values are not identical.*/
FROM Products
WHERE checksum(N'Vegie-spread') = cs_Pname
AND ProductName = N'Vegie-spread'

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.


Author Comment

ID: 8194589
As I understand the checksum index, a new column must be added for the table and an index must be applied to this column.

In contrast the hash cluster can be used without affecting the original table.

With the checksum I still have to maintain an index, which i can do without using the hash cluster, ie I can speed up insertions.
LVL 13

Expert Comment

ID: 8194815
Yes, but ORA does the same thing, only transparently.

In MSSQL, you must use 2 columns (original and calculated).
When you hash 8 bytes to 4 bytes, the information of 4 bytes is missing. Even if you hash 4 bytes, you MUST use also a original column (CHECKSUM is an absolutely asymetric function).

Author Comment

ID: 8195126
My current setup i have hash cluster with  
HASH IS agent_id HASHKEYS 1500 ;
The table is put in the cluster has an ID which does not exceed 1500.

The Managing Hash Clusters documentation states:

To find or store a row in an indexed table or cluster, a minimum of two (there are usually more) I/Os must be performed:

- One or more I/Os to find or store the key value in the index

- Another I/O to read or write the row in the table or cluster

In contrast, Oracle uses a hash function to locate a row in a hash cluster; no I/O is required. As a result, a minimum of one I/O operation is necessary to read or write a row in a hash cluster.

I dont see that the checksum has the same performance profile.

LVL 13

Expert Comment

ID: 8195589
You know ORA better, but I thought you are looking for a MSSQL alternative.

Author Comment

ID: 8201058
I just hopped for a better alternative, but you might be right that checksum is the one comming closest. You get the points anyway, as the only one answering.

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question