How to control between Merged, Nested and Index Nested Loop in Execution Plan












0















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?










share|improve this question




















  • 1





    Why bother? Trust the optimizer. (A consistent database design makes things better!)

    – jarlh
    Nov 18 '18 at 19:46













  • Optimizer works with current conditions but it does not mean that it is the best.

    – Arnes
    Nov 18 '18 at 19:50











  • If you don't like what the optimizer does, use hints.

    – Gordon Linoff
    Nov 18 '18 at 19:59
















0















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?










share|improve this question




















  • 1





    Why bother? Trust the optimizer. (A consistent database design makes things better!)

    – jarlh
    Nov 18 '18 at 19:46













  • Optimizer works with current conditions but it does not mean that it is the best.

    – Arnes
    Nov 18 '18 at 19:50











  • If you don't like what the optimizer does, use hints.

    – Gordon Linoff
    Nov 18 '18 at 19:59














0












0








0








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?










share|improve this question
















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






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 18 '18 at 19:45







Arnes

















asked Nov 18 '18 at 19:44









ArnesArnes

32




32








  • 1





    Why bother? Trust the optimizer. (A consistent database design makes things better!)

    – jarlh
    Nov 18 '18 at 19:46













  • Optimizer works with current conditions but it does not mean that it is the best.

    – Arnes
    Nov 18 '18 at 19:50











  • If you don't like what the optimizer does, use hints.

    – Gordon Linoff
    Nov 18 '18 at 19:59














  • 1





    Why bother? Trust the optimizer. (A consistent database design makes things better!)

    – jarlh
    Nov 18 '18 at 19:46













  • Optimizer works with current conditions but it does not mean that it is the best.

    – Arnes
    Nov 18 '18 at 19:50











  • If you don't like what the optimizer does, use hints.

    – Gordon Linoff
    Nov 18 '18 at 19:59








1




1





Why bother? Trust the optimizer. (A consistent database design makes things better!)

– jarlh
Nov 18 '18 at 19:46







Why bother? Trust the optimizer. (A consistent database design makes things better!)

– jarlh
Nov 18 '18 at 19:46















Optimizer works with current conditions but it does not mean that it is the best.

– Arnes
Nov 18 '18 at 19:50





Optimizer works with current conditions but it does not mean that it is the best.

– Arnes
Nov 18 '18 at 19:50













If you don't like what the optimizer does, use hints.

– Gordon Linoff
Nov 18 '18 at 19:59





If you don't like what the optimizer does, use hints.

– Gordon Linoff
Nov 18 '18 at 19:59












0






active

oldest

votes











Your Answer






StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});














draft saved

draft discarded


















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

























0






active

oldest

votes








0






active

oldest

votes









active

oldest

votes






active

oldest

votes
















draft saved

draft discarded




















































Thanks for contributing an answer to Stack Overflow!


  • Please be sure to answer the question. Provide details and share your research!

But avoid



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.




draft saved


draft discarded














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





















































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







Popular posts from this blog

Guess what letter conforming each word

Port of Spain

Run scheduled task as local user group (not BUILTIN)