October 11, 2007 at 12:34 am
Hi All,
1st Qst : I am really Confused why I will use Hash Join,
I mean to say in what circumstances I can use hash join,
and what is the real use of this?
2nd Qst: What is hash partitioning, and how it works
in SQL server.
(hints- this hash partitioning concept i knew from Table partitioning concept)
Please help me,
Cheers!
Sandy.
--
October 11, 2007 at 1:41 am
Hi Sandy,
Sandy (10/11/2007)
Hi All,1st Qst : I am really Confused why I will use Hash Join,
I mean to say in what circumstances I can use hash join,
and what is the real use of this?
A hash join is just an algorithm to perform a join. SQL Server will take your join, left join, etc depending on the indexes, data distribution, etc. decides what algorithm to use to get the data. Generally a hash join is used if there are no good enough indexes on the join columns. The smaller of the two tables are used to build up a hashtable, and then the other table is scanned and the relevant matching rows are looked up in the hashtable. You can get more information about hash joins on http://msdn2.microsoft.com/en-us/library/ms189313.aspx
2nd Qst: What is hash partitioning, and how it works in SQL server.
(hints- this hash partitioning concept i knew from Table partitioning concept)
Hash partitioning is used with horizontal table partitioning, where a table is split up into several smaller tables with the same columns. The union of these tables will give back the original table, but at the same time you can store these smaller tables on different filegroups, have different indexes for them, etc. It is really usefull for very large tables, especially ones that store historical information. E.g. invoices. You can have invoices for before 2005 on a few readonly filegroups, the ones for the current year on a fast disk. The way to decide which sub-table to use to store a particular row is determined by a function, which is basically a hash function. 2005 currently supports a rather primitive "hash function" in which it uses ranges. One of the betas for 2005 allowed more complex functions, so this is something we may see in the future. You can get more info on how it works, which system tables/views to use to get information about existing partitions if you search books online for CREATE PARTITION FUNCTION and CREATE PARTITION SCHEME. (the partition function is your hash function)
Regards,
Andras
October 11, 2007 at 2:13 am
Hey Andras Belokosztolszki,
I am really happy to see this quick reply,
I appreciate the way you answering to my topic.
Cheers!:)
Sandy.
--
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply