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.
Post Reply
User avatar
ALI RAZA
★★★★★★★★★★★★★
★★★★★★★★★★★★★
Posts: 174
Joined: Fri Dec 22, 2017 8:10 am
Location: Lahore
Has thanked: 6 times
Been thanked: 7 times
Contact:
Status: Offline

Discuss some factors on the basis of which optimizer can decide to use either Sort Merge Join or Hash based Join.

Post by ALI RAZA » Thu Oct 04, 2018 9:44 am

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.
Answer:
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.



Post Reply

Last 10 Members Who Visited This Topic

User avatar ALI RAZA (4)

  • Similar Topics
    Replies
    Views
    Last post

Who is online

Users browsing this forum: No registered users and 1 guest