Monday, November 10, 2014

Dynamic Hash Join

Dynamic hash join provides the ability to do an equality join directly between a small table and a large table on non‑primary index columns without placing the large table into a spool file. important criteria for dynamic hash join to be used, the left table must be small enough to fit in a single hash join partition.
It process as follows:
1) Duplicate the smaller table.
2) Place the smaller table in a hash array
3) Read a row from the right table
4) Compute the row hash code
5) Match each right row with all rows in the hash array having the same row hash.
6) Join the matching rows.
Dynamic hash join is faster than redistributing the small table, putting it into the hash array, reading the large table, building the row hash code, writing the data out to spool, and then reading the table in again to do the hash join.
The dynamic hash join can be decided by optimizer in case of left, right, and full outer joins as well as inner joins, and can also take advantage of equality conditions for dynamic partition elimination. 

1 comment:

  1. Data Warehousing Online Training, ONLINE TRAINING – IT SUPPORT – CORPORATE TRAINING http://www.21cssindia.com/courses/data-warehousing-online-training-29.html The 21st Century Software Solutions of India offers one of the Largest conglomerations of Software Training, IT Support, Corporate Training institute in India - +919000444287 - +917386622889 - Visakhapatnam,Hyderabad Data Warehousing Online Training, Data Warehousing Training, Data Warehousing, Data Warehousing Online Training| Data Warehousing Training| Data Warehousing| If you’re serious about a career in IT, 21st Century would like to provide you a guidance don’t hesitate to organize a free demo session. For any further information regarding the courses once go through our website Visit:http://www.21cssindia.com | Call Us +917386622889 - +919000444287 - contact@21cssindia.com

    ReplyDelete