How to control between Merged, Nested and Index Nested Loop in Execution Plan
up vote
0
down vote
favorite
I am interested to know basic preferences which will decide which join is cheaper. I can force join types by typing merge, loop, hash into the join statement but I would like to know if there is a way to fix indexes, datatypes in order to provide conditions for particular cheapest join.
Let's say query plan returns Nested Loop as a cheapest join with current conditions. Is there a way to do something with whatever in order to make Sort Merge Join cheapest. I know for example that if I delete indexes it will use hash join so my question actually is what to change in order to control between Merged, Nested and Index Nested loop?
sql
sql-server asked Nov 18 at 19:44
Arnes
32
add a comment |
up vote
0
down vote
favorite
I am interested to know basic preferences which will decide which join is cheaper. I can force join types by typing merge, loop, hash into the join statement but I would like to know if there is a way to fix indexes, datatypes in order to provide conditions for particular cheapest join.
Let's say query plan returns Nested Loop as a cheapest join with current conditions. Is there a way to do something with whatever in order to make Sort Merge Join cheapest. I know for example that if I delete indexes it will use hash join so my question actually is what to change in order to control between Merged, Nested and Index Nested loop?
sql
sql-server asked Nov 18 at 19:44
Arnes
32
1
Why bother? Trust the optimizer. (A consistent database design makes things better!)
– jarlh
Nov 18 at 19:46
Optimizer works with current conditions but it does not mean that it is the best.
– Arnes
Nov 18 at 19:50
If you don't like what the optimizer does, use hints.
– Gordon Linoff
Nov 18 at 19:59
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I am interested to know basic preferences which will decide which join is cheaper. I can force join types by typing merge, loop, hash into the join statement but I would like to know if there is a way to fix indexes, datatypes in order to provide conditions for particular cheapest join.
Let's say query plan returns Nested Loop as a cheapest join with current conditions. Is there a way to do something with whatever in order to make Sort Merge Join cheapest. I know for example that if I delete indexes it will use hash join so my question actually is what to change in order to control between Merged, Nested and Index Nested loop?
sql
sql-server asked Nov 18 at 19:44
Arnes
32
I am interested to know basic preferences which will decide which join is cheaper. I can force join types by typing merge, loop, hash into the join statement but I would like to know if there is a way to fix indexes, datatypes in order to provide conditions for particular cheapest join.
Let's say query plan returns Nested Loop as a cheapest join with current conditions. Is there a way to do something with whatever in order to make Sort Merge Join cheapest. I know for example that if I delete indexes it will use hash join so my question actually is what to change in order to control between Merged, Nested and Index Nested loop?
sql
sql-server sql
sql-server asked Nov 18 at 19:44
Arnes
32
asked Nov 18 at 19:44
Arnes
32
edited Nov 18 at 19:45
asked Nov 18 at 19:44
Arnes
32
asked Nov 18 at 19:44
Arnes
32
asked Nov 18 at 19:44
Arnes
32
32
1
Why bother? Trust the optimizer. (A consistent database design makes things better!)
– jarlh
Nov 18 at 19:46
Optimizer works with current conditions but it does not mean that it is the best.
– Arnes
Nov 18 at 19:50
If you don't like what the optimizer does, use hints.
– Gordon Linoff
Nov 18 at 19:59
add a comment |
1
Why bother? Trust the optimizer. (A consistent database design makes things better!)
– jarlh
Nov 18 at 19:46
Optimizer works with current conditions but it does not mean that it is the best.
– Arnes
Nov 18 at 19:50
If you don't like what the optimizer does, use hints.
– Gordon Linoff
Nov 18 at 19:59
1
1
Why bother? Trust the optimizer. (A consistent database design makes things better!)
– jarlh
Nov 18 at 19:46
Why bother? Trust the optimizer. (A consistent database design makes things better!)
– jarlh
Nov 18 at 19:46
Optimizer works with current conditions but it does not mean that it is the best.
– Arnes
Nov 18 at 19:50
Optimizer works with current conditions but it does not mean that it is the best.
– Arnes
Nov 18 at 19:50
If you don't like what the optimizer does, use hints.
– Gordon Linoff
Nov 18 at 19:59
If you don't like what the optimizer does, use hints.
– Gordon Linoff
Nov 18 at 19:59
add a comment |
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53364781%2fhow-to-control-between-merged-nested-and-index-nested-loop-in-execution-plan%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Why bother? Trust the optimizer. (A consistent database design makes things better!)
– jarlh
Nov 18 at 19:46
Optimizer works with current conditions but it does not mean that it is the best.
– Arnes
Nov 18 at 19:50
If you don't like what the optimizer does, use hints.
– Gordon Linoff
Nov 18 at 19:59