The task of optimizer is to decide which join technique should be used for a particular search query. In a decision support system, Sort Merge Join and Hash Based join are some important join techniques which can be used by the optimizer. Discuss some factors on the basis of which optimizer can decide to use either Sort Merge Join or Hash based Join.
The main function of the optimizer is to choose the best join technique. The optimizer chooses the join technique on the basis of lowest estimated cost. The factors that are consider for the choice of either sort Merge join or hash based join are as follows.
Sort Merge join
The sort merge join is chosen when large amount of data needed to be join and the joining condition is not equi join
When there are indexes database can avoid the sorting of first data set after this the merge phase is started which produce faster output rows.
It can be more useful in term of being cost effective when hash tables do not fit into memory.
Hash Based Join
Hash based joins perform better then sort merge joins because sorting is an expensive task.
Hash based join is chosen when large amount of data needed to be join and joining condition is an equi join.
It is more cost effective when hash tables fits into memory.
Discuss some factors on the basis of which optimizer can decide to use either Sort Merge Join or Hash based Join.
Vu Help Subsection Discuss Gdb's of in this section Help each other.
1 post • Page 1 of 1
Last 10 Members Who Visited This Topic
ALI RAZA (4)
- Similar Topics
- Last post
Users browsing this forum: No registered users and 1 guest