| <!DOCTYPE html> |
| |
| |
| |
| <html lang="en"> |
| <head> |
| <meta charset="UTF-8"> |
| <!-- Bugyard widget embed --> |
| <script type="text/javascript"> |
| !function(){ |
| if("function" != typeof window.bugyard){ |
| var a = function(){ a.c(arguments) }; |
| a.q = [], a.c = function(b){ a.q.push(b) }; |
| window.bugyard = a; |
| |
| var b = document.createElement("script"); |
| b.setAttribute("data-bugyard", "610961912c35ff001493163a"); |
| b.setAttribute("async", "async"); |
| b.setAttribute("defer", "defer"); |
| b.setAttribute("src", "/assets/js/bugyard.min.js"); |
| document.getElementsByTagName("head")[0].appendChild(b); |
| } |
| }(); |
| window.bugyard("hideButton"); |
| </script> |
| <!-- Bugyard widget embed --> |
| |
| <link rel="preload" href='/assets/js/code-tabs.js?1' as="script" crossorigin> |
| <link rel="preload" href='/assets/js/page-nav.js' as="script" crossorigin> |
| <link rel="preload" href='/assets/js/docs-menu.js?20201005' as="script" crossorigin> |
| |
| <style>:root{--gg-red:#ec1c24;--gg-orange:#ec1c24;--gg-orange-dark:#bc440b;--gg-orange-filter:invert(47%) sepia(61%) saturate(1950%) hue-rotate(345deg) brightness(100%) contrast(95%);--gg-dark-gray:#333333;--orange-line-thickness:3px;--block-code-background:rgba(241, 241, 241, 20%);--inline-code-background:rgba(241, 241, 241, 90%);--padding-top:25px;--link-color:#ec1c24;--body-background:#fcfcfc}header{min-height:var(--header-height);background:#fff;box-shadow:0 4px 10px 0 #eee,0 0 4px 0 #d5d5d5;z-index:1}header>.container{display:grid;grid-template-columns:auto auto 1fr auto auto auto;grid-template-areas:'left-toggle home nav ver api search lang';grid-template-rows:40px;flex-direction:row;align-items:center;justify-content:flex-start;padding:12px 20px;max-width:1400px;margin:0 auto}header nav>ul{padding:0;margin:0;list-style:none;display:inherit}header .dropdown{display:none;position:fixed;top:calc(var(--header-height) - 12px);width:auto;background:#fff;box-shadow:0 4px 4px 0 rgba(0,0,0,.24),0 0 4px 0 rgba(0,0,0,.12);border-radius:4px;padding-top:10px;padding-bottom:12px;z-index:2}header .dropdown li{display:flex}header .dropdown a{color:grey!important;font-size:16px;padding-top:5px;padding-bottom:4px}header .menu{border:none;background:0 0;width:40px;height:40px;margin-right:12px;grid-area:left-toggle}header .menu img{width:18px;height:12px}header .search-close,header .top-nav-toggle{background:0 0;border:none;padding:0;width:36px;height:36px;display:inline-flex;align-items:center;justify-content:center;color:var(--gg-dark-gray);font-size:26px}header .search-toggle{grid-area:search}header .top-nav-toggle{grid-area:top-toggle}header .home{grid-area:home;margin-right:auto}header .home img{height:36px}header #api-docs{grid-area:api;margin:0;display:flex}header #api-docs .dropdown{padding:.5em 0}header #api-docs a{padding:9px 14px;color:var(--gg-dark-gray)!important;text-decoration:none;white-space:nowrap}header #api-docs .dropdown-item a{font-weight:400;display:block;width:100%;min-width:150px}header #lang-selector li{list-style:none;display:flex;padding:9px 14px}header #lang-selector li a{display:flex;color:#000;align-items:center}header #lang-selector li a span{font-size:10px;margin-left:5px}header #lang-selector li a img{width:25px}header #lang-selector li .dropdown{margin-left:-70px}header #lang-selector li .dropdown .dropdown-item{padding:0 1em;margin-bottom:8px}header #lang-selector li .dropdown .dropdown-item a span{font-size:14px}header .search{margin-left:auto;margin-right:20px;grid-area:search}header .search input[type=search]{color:var(--gg-dark-gray);background:rgba(255,255,255,.8);border:1px solid #ccc;padding:10px 15px;font-family:inherit;max-width:148px;height:37px;font-size:14px;-webkit-appearance:unset;appearance:unset}header #version-selector{list-style:none;grid-area:ver;line-height:28px;border-radius:0;margin-right:10px;border:none;color:var(--gg-dark-gray);padding:5px 16px 5px 10px;white-space:nowrap;font-size:14px;width:auto;text-align:right;box-sizing:border-box;text-align-last:right;-moz-appearance:none;-webkit-appearance:none;appearance:none;direction:rtl}header #version-selector option{direction:ltr}header>nav{grid-area:nav;font-size:18px;display:flex;flex-direction:row;margin:0 20px}header #lang-selector{grid-area:lang}header .search-close{margin-right:10px}@media (max-width:600px){header .search{margin-right:5px}header .search input[type=search]{max-width:110px}}header:not(.narrow-header) .search-close,header:not(.narrow-header) .top-nav-toggle{display:none}@media (max-width:670px){header>.container{grid-template-columns:auto 1fr auto;grid-template-areas:'left-toggle home search' 'ver api lang'}header #lang-selector li{justify-content:flex-end}}pre,pre.rouge{padding:8px 15px;background:var(--block-code-background)!important;border-radius:5px;border:1px solid #e5e5e5;overflow-x:auto;min-height:36px;line-height:18px;color:#545454}code{color:#545454}pre.rouge code{background:0 0!important}:not(pre)>code{background:var(--inline-code-background);padding:.1em .5em;background-clip:padding-box;border-radius:3px;color:#545454;font-size:90%}.listingblock .content{position:relative}.highlight{color:#586e75}.highlight .c1{color:#657b83}.highlight .nt{color:#b58900}.highlight .o{color:#93a1a1}.highlight .k{color:#6c71c4}.highlight .kt{color:#cb4b16}.highlight .s,.highlight .s1{color:#859900}.highlight .nc{color:#b58900}.highlight .na{color:#268bd2}body{font-family:'Open Sans',sans-serif}h1,h2{color:#000;font-weight:400;font-family:'Open Sans'}h1{font-size:36px;line-height:40px}a{text-decoration:none;color:var(--link-color)}section{color:#545454}.admonitionblock .icon .title{display:none}body{--header-height:64px;--promotion-bar-height:35px;--footer-height:104px;--footer-gap:60px;padding:0;margin:0;display:flex;flex-direction:column;min-height:100vh;background-color:var(--body-background);font-family:'Open Sans',sans-serif}body>section{flex:1}header{position:-webkit-sticky;position:sticky;top:0;z-index:2}*{box-sizing:border-box}@media (max-width:670px){body{--header-height:97px}}.left-nav{padding:10px 20px;width:289px;overflow-y:auto;top:calc(var(--header-height) + var(--promotion-bar-height));height:calc(100vh - var(--header-height) - var(--promotion-bar-height));font-family:'Open Sans';padding-top:var(--padding-top);background-color:var(--body-background)}.left-nav li{list-style:none}.left-nav a,.left-nav button{text-decoration:none;color:#757575;font-size:16px;display:inline-flex;width:100%;margin:2px 0;padding:.25em .375em;background:0 0;border:none;font:inherit;text-align:left}.left-nav a.active{color:var(--link-color)}.left-nav .nav-group{margin-left:6px;font-size:14px}.left-nav nav{border-left:2px solid #ddd;margin-bottom:5px}.left-nav nav.collapsed{display:none}.left-nav nav>li>a,.left-nav nav>li>button{padding-left:20px;text-align:left}.left-nav nav>li>a.active{border-left:var(--orange-line-thickness) solid var(--active-color);padding-left:calc(20px - var(--orange-line-thickness))}.left-nav nav.sub_pages{border:none}.left-nav nav.sub_pages a{padding-left:32px}.left-nav .state-indicator{margin-left:auto;margin-top:5px;width:6.2px;height:10px;flex:0 0 auto;filter:invert(49%) sepia(4%) saturate(5%) hue-rotate(23deg) brightness(92%) contrast(90%)}.left-nav button.expanded .state-indicator{transform:rotate(90deg)}.right-nav{width:289px;padding:12px 26px;overflow-y:auto;height:calc(100vh - var(--header-height));top:0;position:-webkit-sticky;position:sticky;display:flex;flex-direction:column;font-family:'Open sans';padding-top:var(--padding-top);background-color:#fff}.right-nav ul{list-style:none;padding:0;margin:0}.right-nav li{padding:0}.right-nav a{--border-width:0px;font-size:14px;color:#757575;padding-left:calc(15px * var(--nesting-level) + 8px - var(--border-width));margin:.3em 0;display:inline-block}.right-nav .sectlevel1{border-left:2px solid #ddd}.right-nav .sectlevel1{--nesting-level:0}.right-nav .sectlevel2{--nesting-level:1}.right-nav .sectlevel3{--nesting-level:2}@media (max-width:1200px){.right-nav{width:230px}}.right-nav footer{font-size:12px;padding:calc(var(--footer-gap) * .3) 0 5px;text-align:left;margin:auto 0 0}section.page-docs{display:grid;grid-template-columns:auto 1fr auto;grid-template-rows:100%;grid-template-areas:'left-nav content right-nav';line-height:20px;max-width:1440px;margin:auto;width:100%}section.page-docs>article{border-left:1px solid #eee;background-color:#fff;padding:0 50px 30px;grid-area:content;overflow:hidden;font-family:sans-serif;font-size:16px;color:#545454;line-height:1.6em}section.page-docs>article h1,section.page-docs>article h2{font-family:'Open Sans'}@media (max-width:800px){section.page-docs>article{padding-left:15px;padding-right:15px}}section.page-docs .edit-link{position:relative;top:10px;right:10px;float:right;padding-top:calc(var(--header-height) + var(--padding-top));margin-top:calc((-1 * var(--header-height)))}section.page-docs h1,section.page-docs h2{margin-bottom:0}section.page-docs h2[id]{margin-top:var(--margin-top);margin-bottom:calc(var(--margin-top) * .5);z-index:-1}section.page-docs .title{font-style:italic}section.page-docs h2[id]{--margin-top:1.2em}.left-nav{bottom:0;position:-webkit-sticky;position:sticky}.left-nav{grid-area:left-nav}.right-nav{grid-area:right-nav}.left-nav__overlay{display:none;background:rgba(0,0,0,.5);z-index:1;position:fixed;top:var(--header-height);bottom:0;left:0;right:0}@media (max-width:990px){body:not(.hide-left-nav) .left-nav__overlay{display:block}nav.left-nav{background:#fafafa;grid-area:left-nav;box-shadow:0 4px 4px 0 rgba(0,0,0,.24),0 0 4px 0 rgba(0,0,0,.12);min-height:calc(100vh - var(--header-height));max-height:calc(100vh - var(--header-height));position:fixed;bottom:0;top:var(--header-height);z-index:2}section.page-docs>article{grid-column-start:left-nav;grid-column-end:content;grid-row:content}}@media (max-width:800px){nav.right-nav{display:none}}:target:before{content:"";display:block;margin-top:calc(var(--header-height) * -1);height:var(--header-height);width:1px}@media (min-width:600px) and (max-width:900px){:target:before{content:"";display:block;width:1px;margin-top:-150px;height:150px}} |
| #header #promotion-bar { background-color: #333333; padding: 8px; } |
| #header #promotion-bar p { font-size: 14px; line-height: 1.4em; font-weight: 600; padding: 0; margin: 0; color: #f0f0f0; text-align: center;} |
| #header #promotion-bar p a { color: #FCB903; } </style> |
| |
| |
| |
| |
| <meta name="ignite-version" content="3.1.0" /> |
| <title>SQL Performance Tuning | Ignite Documentation</title> |
| |
| <link rel="canonical" href="/docs/latest/sql-tuning/sql-tuning" /> |
| |
| |
| <link rel="shortcut icon" href="/favicon.ico"> |
| <meta name='viewport' content='width=device-width, height=device-height, initial-scale=1.0, minimum-scale=1.0'> |
| |
| <link rel="preload" as="style" href="/assets/css/fonts.css" /> |
| <link rel="stylesheet" href="/assets/css/fonts.css" media="print" onload="this.media='all'"> |
| <noscript> |
| <link rel="stylesheet" href="/assets/css/fonts.css"> |
| </noscript> |
| <link href="/docs/pagefind/pagefind-ui.css" rel="stylesheet"> |
| <script src="/docs/pagefind/pagefind-ui.js"></script> |
| <script> |
| window.addEventListener('DOMContentLoaded', () => { |
| new PagefindUI({ |
| element: "#search", |
| showSubResults: true, |
| showImages: false, |
| }); |
| }); |
| </script> |
| <script src="/assets/js/mermaid.min.js"></script> |
| <script> |
| document.addEventListener('DOMContentLoaded', function () { |
| const blocks = document.querySelectorAll('.mermaid'); |
| if (blocks.length) { |
| mermaid.initialize({ startOnLoad: false }); |
| mermaid.run(); |
| } |
| }); |
| </script> |
| |
| <script> |
| // AnchorJS - v4.2.0 - 2019-01-01 |
| // https://github.com/bryanbraun/anchorjs |
| // Copyright (c) 2019 Bryan Braun; Licensed MIT |
| !function(A,e){"use strict";"function"==typeof define&&define.amd?define([],e):"object"==typeof module&&module.exports?module.exports=e():(A.AnchorJS=e(),A.anchors=new A.AnchorJS)}(this,function(){"use strict";return function(A){function f(A){A.icon=A.hasOwnProperty("icon")?A.icon:"",A.visible=A.hasOwnProperty("visible")?A.visible:"hover",A.placement=A.hasOwnProperty("placement")?A.placement:"right",A.ariaLabel=A.hasOwnProperty("ariaLabel")?A.ariaLabel:"Anchor",A.class=A.hasOwnProperty("class")?A.class:"",A.base=A.hasOwnProperty("base")?A.base:"",A.truncate=A.hasOwnProperty("truncate")?Math.floor(A.truncate):64,A.titleText=A.hasOwnProperty("titleText")?A.titleText:""}function p(A){var e;if("string"==typeof A||A instanceof String)e=[].slice.call(document.querySelectorAll(A));else{if(!(Array.isArray(A)||A instanceof NodeList))throw new Error("The selector provided to AnchorJS was invalid.");e=[].slice.call(A)}return e}this.options=A||{},this.elements=[],f(this.options),this.isTouchDevice=function(){return!!("ontouchstart"in window||window.DocumentTouch&&document instanceof DocumentTouch)},this.add=function(A){var e,t,i,n,o,s,a,r,c,h,l,u,d=[];if(f(this.options),"touch"===(l=this.options.visible)&&(l=this.isTouchDevice()?"always":"hover"),A||(A="h2, h3, h4, h5, h6"),0===(e=p(A)).length)return this;for(function(){if(null===document.head.querySelector("style.anchorjs")){var A,e=document.createElement("style");e.className="anchorjs",e.appendChild(document.createTextNode("")),void 0===(A=document.head.querySelector('[rel="stylesheet"], style'))?document.head.appendChild(e):document.head.insertBefore(e,A),e.sheet.insertRule(" .anchorjs-link { opacity: 0; text-decoration: none; -webkit-font-smoothing: antialiased; -moz-osx-font-smoothing: grayscale; }",e.sheet.cssRules.length),e.sheet.insertRule(" *:hover > .anchorjs-link, .anchorjs-link:focus { opacity: 1; }",e.sheet.cssRules.length),e.sheet.insertRule(" [data-anchorjs-icon]::after { content: attr(data-anchorjs-icon); }",e.sheet.cssRules.length),e.sheet.insertRule(' @font-face { font-family: "anchorjs-icons"; src: url(data:n/a;base64,AAEAAAALAIAAAwAwT1MvMg8yG2cAAAE4AAAAYGNtYXDp3gC3AAABpAAAAExnYXNwAAAAEAAAA9wAAAAIZ2x5ZlQCcfwAAAH4AAABCGhlYWQHFvHyAAAAvAAAADZoaGVhBnACFwAAAPQAAAAkaG10eASAADEAAAGYAAAADGxvY2EACACEAAAB8AAAAAhtYXhwAAYAVwAAARgAAAAgbmFtZQGOH9cAAAMAAAAAunBvc3QAAwAAAAADvAAAACAAAQAAAAEAAHzE2p9fDzz1AAkEAAAAAADRecUWAAAAANQA6R8AAAAAAoACwAAAAAgAAgAAAAAAAAABAAADwP/AAAACgAAA/9MCrQABAAAAAAAAAAAAAAAAAAAAAwABAAAAAwBVAAIAAAAAAAIAAAAAAAAAAAAAAAAAAAAAAAMCQAGQAAUAAAKZAswAAACPApkCzAAAAesAMwEJAAAAAAAAAAAAAAAAAAAAARAAAAAAAAAAAAAAAAAAAAAAQAAg//0DwP/AAEADwABAAAAAAQAAAAAAAAAAAAAAIAAAAAAAAAIAAAACgAAxAAAAAwAAAAMAAAAcAAEAAwAAABwAAwABAAAAHAAEADAAAAAIAAgAAgAAACDpy//9//8AAAAg6cv//f///+EWNwADAAEAAAAAAAAAAAAAAAAACACEAAEAAAAAAAAAAAAAAAAxAAACAAQARAKAAsAAKwBUAAABIiYnJjQ3NzY2MzIWFxYUBwcGIicmNDc3NjQnJiYjIgYHBwYUFxYUBwYGIwciJicmNDc3NjIXFhQHBwYUFxYWMzI2Nzc2NCcmNDc2MhcWFAcHBgYjARQGDAUtLXoWOR8fORYtLTgKGwoKCjgaGg0gEhIgDXoaGgkJBQwHdR85Fi0tOAobCgoKOBoaDSASEiANehoaCQkKGwotLXoWOR8BMwUFLYEuehYXFxYugC44CQkKGwo4GkoaDQ0NDXoaShoKGwoFBe8XFi6ALjgJCQobCjgaShoNDQ0NehpKGgobCgoKLYEuehYXAAAADACWAAEAAAAAAAEACAAAAAEAAAAAAAIAAwAIAAEAAAAAAAMACAAAAAEAAAAAAAQACAAAAAEAAAAAAAUAAQALAAEAAAAAAAYACAAAAAMAAQQJAAEAEAAMAAMAAQQJAAIABgAcAAMAAQQJAAMAEAAMAAMAAQQJAAQAEAAMAAMAAQQJAAUAAgAiAAMAAQQJAAYAEAAMYW5jaG9yanM0MDBAAGEAbgBjAGgAbwByAGoAcwA0ADAAMABAAAAAAwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAABAAH//wAP) format("truetype"); }',e.sheet.cssRules.length)}}(),t=document.querySelectorAll("[id]"),i=[].map.call(t,function(A){return A.id}),o=0;o<e.length;o++)if(this.hasAnchorJSLink(e[o]))d.push(o);else{if(e[o].hasAttribute("id"))n=e[o].getAttribute("id");else if(e[o].hasAttribute("data-anchor-id"))n=e[o].getAttribute("data-anchor-id");else{for(c=r=this.urlify(e[o].textContent),a=0;void 0!==s&&(c=r+"-"+a),a+=1,-1!==(s=i.indexOf(c)););s=void 0,i.push(c),e[o].setAttribute("id",c),n=c}n.replace(/-/g," "),(h=document.createElement("a")).className="anchorjs-link "+this.options.class,h.setAttribute("aria-label",this.options.ariaLabel),h.setAttribute("data-anchorjs-icon",this.options.icon),this.options.titleText&&(h.title=this.options.titleText),u=document.querySelector("base")?window.location.pathname+window.location.search:"",u=this.options.base||u,h.href=u+"#"+n,"always"===l&&(h.style.opacity="1"),""===this.options.icon&&(h.style.font="1em/1 anchorjs-icons","left"===this.options.placement&&(h.style.lineHeight="inherit")),"left"===this.options.placement?(h.style.position="absolute",h.style.marginLeft="-1em",h.style.paddingRight="0.5em",e[o].insertBefore(h,e[o].firstChild)):(h.style.paddingLeft="0.375em",e[o].appendChild(h))}for(o=0;o<d.length;o++)e.splice(d[o]-o,1);return this.elements=this.elements.concat(e),this},this.remove=function(A){for(var e,t,i=p(A),n=0;n<i.length;n++)(t=i[n].querySelector(".anchorjs-link"))&&(-1!==(e=this.elements.indexOf(i[n]))&&this.elements.splice(e,1),i[n].removeChild(t));return this},this.removeAll=function(){this.remove(this.elements)},this.urlify=function(A){return this.options.truncate||f(this.options),A.trim().replace(/\'/gi,"").replace(/[& +$,:;=?@"#{}|^~[`%!'<>\]\.\/\(\)\*\\\n\t\b\v]/g,"-").replace(/-{2,}/g,"-").substring(0,this.options.truncate).replace(/^-+|-+$/gm,"").toLowerCase()},this.hasAnchorJSLink=function(A){var e=A.firstChild&&-1<(" "+A.firstChild.className+" ").indexOf(" anchorjs-link "),t=A.lastChild&&-1<(" "+A.lastChild.className+" ").indexOf(" anchorjs-link ");return e||t||!1}}}); |
| </script> |
| |
| </head> |
| <body> |
| <header> |
| <!--#include virtual="/includes/promotion_banner.html" --> |
| <div class="container"> |
| <button type='button' class='menu' title='Docs menu'> |
| <img src="/assets/images/menu-icon.svg" width="18" height="12" alt="menu icon" /> |
| </button> |
| <div class='home'> |
| <a href="/" class='home' title='Apache Ignite home'> |
| <img src="/assets/images/apache_ignite_logo.svg" alt="Apache Ignite logo" width="103" height="36" > |
| </a> |
| </div> |
| |
| |
| |
| <select id="product-selector"> |
| <option value="/docs/ignite2/latest" >Ignite 2</option> |
| <option value="/docs/ignite3/latest"selected>Ignite 3</option> |
| </select> |
| |
| <select id="version-selector"> |
| <option value="3.1.0">3.1.0</option> |
| </select> |
| |
| |
| <nav id="api-docs"><ul> |
| <li><a href="#">APIs</a> |
| <nav class='dropdown'> |
| <ul> |
| |
| <li class="dropdown-item"><a href="/releases/ignite3/3.1.0/javadoc/">Java</a></li> |
| <li class="dropdown-item"><a href="/releases/ignite3/3.1.0/dotnetdoc/">C#/.NET</a></li> |
| <li class="dropdown-item"><a href="/releases/ignite3/3.1.0/cppdoc/">C++</a></li> |
| <li class="dropdown-item"><a href="/releases/ignite3/3.1.0/openapi.yaml">OpenAPI</a></li> |
| |
| </ul> |
| </nav> |
| </li> |
| |
| <li><a href="#">Examples</a> |
| <nav class="dropdown"> |
| <ul> |
| <li class="dropdown-item"><a href="https://github.com/apache/ignite/tree/master/examples" target="_blank" rel="noopener" title="Apache Ignite Java examples">Java</a></li> |
| |
| <li class="dropdown-item"><a href="https://github.com/apache/ignite/tree/master/modules/platforms/dotnet/examples" target="_blank" rel="noopener" title="Apache Ignite C#/.NET examples">C#/.NET</a></li> |
| |
| <li class="dropdown-item"><a href="https://github.com/apache/ignite/tree/master/modules/platforms/cpp/examples" target="_blank" rel="noopener" title="Apache Ignite C++ examples">C++</a></li> |
| |
| <li class="dropdown-item"><a href="https://github.com/apache/ignite/tree/master/modules/platforms/python/examples" target="_blank" rel="noopener" title="Apache Ignite Python examples">Python</a></li> |
| |
| <li class="dropdown-item"><a href="https://github.com/apache/ignite/tree/master/modules/platforms/nodejs/examples" target="_blank" rel="noopener" title="Apache Ignite NodeJS examples">NodeJS</a></li> |
| |
| <li class="dropdown-item"><a href="https://github.com/apache/ignite/tree/master/modules/platforms/php/examples" target="_blank" rel="noopener" title="Apache Ignite PHP examples">PHP</a></li> |
| </ul> |
| </nav> |
| </li></ul> |
| </nav> |
| |
| |
| |
| <div id="search-button"></div> |
| |
| <nav id="lang-selector"><ul> |
| <li><a href="#"><img src="/assets/images/icon_lang_en_75x75.jpg" alt="English language icon" width="25" height="25" /><span>▼</span></a> |
| <nav class="dropdown"> |
| <li class="dropdown-item"><a href="/docs/latest/" ><img src="/assets/images/icon_lang_en_75x75.jpg" alt="English language icon" width="25" height="25" /><span>English</span></a></li> |
| <li class="dropdown-item"><a href="https://www.ignite-service.cn/doc/java/" target="_blank" rel="noopener"><img src="/assets/images/icon_lang_cn_75x75.jpg" width="25" height="25" alt="Chinese language icon" /><span>Chinese</span></a></li> |
| </nav> |
| </li></ul> |
| </nav> |
| |
| <button type='button' class='top-nav-toggle'>⋮</button> |
| |
| </div> |
| <div id="search-wrapper"> |
| <div id="search"></div> |
| </div> |
| </header> |
| |
| <link rel="stylesheet" href="/assets/css/docs.css"> |
| <section class='page-docs'> |
| |
| |
| |
| |
| |
| |
| <nav class='left-nav' data-swiftype-index='false'> |
| |
| |
| <li> |
| |
| |
| <a href="/docs/ignite3/latest/index" class='' >About Apache Ignite 3</a> |
| |
| </li> |
| |
| <li> |
| |
| |
| |
| |
| |
| |
| <button type='button' class='group-toggle collapsed '>Installation<img class="state-indicator" src="/assets/images/left-nav-arrow.svg" width="6" height="10"></button> |
| <nav class='nav-group collapsed'> |
| |
| |
| |
| |
| |
| |
| |
| <li> |
| |
| <a href="/docs/ignite3/latest/installation/installing-using-zip" |
| class='' |
| >Installing Using ZIP Archive</a> |
| |
| </li> |
| |
| |
| |
| |
| |
| |
| <li> |
| |
| <a href="/docs/ignite3/latest/installation/deb-rpm" |
| class='' |
| >Installing DEB or RPM package</a> |
| |
| </li> |
| |
| |
| |
| |
| |
| |
| <li> |
| |
| <a href="/docs/ignite3/latest/installation/installing-using-docker" |
| class='' |
| >Installing Docker</a> |
| |
| </li> |
| |
| |
| |
| |
| |
| |
| <li> |
| |
| <a href="/docs/ignite3/latest/installation/migration-from-ai3-1" |
| class='' |
| >Migration From Ignite 3.0</a> |
| |
| </li> |
| |
| |
| |
| |
| |
| |
| <li> |
| |
| |
| |
| <button |
| type='button' |
| class='collapsed '>Migration From Ignite 2<img class="state-indicator" src="/assets/images/left-nav-arrow.svg" width="6" height="10"></button> |
| <nav class="sub_pages collapsed"> |
| |
| |
| |
| |
| |
| <li><a href="/docs/ignite3/latest/installation/migration-from-ai2/config" class=''>Configuration Migration</a></li> |
| |
| |
| |
| |
| <li><a href="/docs/ignite3/latest/installation/migration-from-ai2/persistent-migration" class=''>Persistent Data Migration</a></li> |
| |
| |
| |
| |
| <li><a href="/docs/ignite3/latest/installation/migration-from-ai2/ai2-functions" class=''>SQL Function Comparison</a></li> |
| |
| </nav> |
| |
| </li> |
| |
| |
| |
| </nav> |
| |
| </li> |
| |
| <li> |
| |
| |
| |
| |
| |
| |
| <button type='button' class='group-toggle collapsed '>Getting Started<img class="state-indicator" src="/assets/images/left-nav-arrow.svg" width="6" height="10"></button> |
| <nav class='nav-group collapsed'> |
| |
| |
| |
| |
| |
| |
| |
| <li> |
| |
| <a href="/docs/ignite3/latest/quick-start/getting-started-guide" |
| class='' |
| >Quick Start</a> |
| |
| </li> |
| |
| |
| |
| |
| |
| |
| <li> |
| |
| <a href="/docs/ignite3/latest/quick-start/start-cluster" |
| class='' |
| >Start Ignite 3 Cluster</a> |
| |
| </li> |
| |
| |
| |
| |
| |
| |
| <li> |
| |
| <a href="/docs/ignite3/latest/quick-start/explore-sql" |
| class='' |
| >Explore SQL Capabilities</a> |
| |
| </li> |
| |
| |
| |
| |
| |
| |
| <li> |
| |
| <a href="/docs/ignite3/latest/quick-start/persist-data" |
| class='' |
| >Persist Your Data</a> |
| |
| </li> |
| |
| |
| |
| |
| |
| |
| <li> |
| |
| <a href="/docs/ignite3/latest/quick-start/java-api" |
| class='' |
| >Use Java API</a> |
| |
| </li> |
| |
| |
| |
| </nav> |
| |
| </li> |
| |
| <li> |
| |
| |
| <a href="/docs/ignite3/latest/quick-start/embedded-mode" class='' >Embedded Mode</a> |
| |
| </li> |
| |
| <li> |
| |
| |
| <a href="/docs/ignite3/latest/ignite-cli-tool" class='' >Ignite CLI Tool</a> |
| |
| </li> |
| |
| <li> |
| |
| |
| |
| |
| |
| |
| <button type='button' class='group-toggle collapsed '>Developers Guide<img class="state-indicator" src="/assets/images/left-nav-arrow.svg" width="6" height="10"></button> |
| <nav class='nav-group collapsed'> |
| |
| |
| |
| |
| |
| |
| |
| <li> |
| |
| <a href="/docs/ignite3/latest/developers-guide/table-api" |
| class='' |
| >Table API</a> |
| |
| </li> |
| |
| |
| |
| |
| |
| |
| <li> |
| |
| |
| |
| <button |
| type='button' |
| class='collapsed '>Clients<img class="state-indicator" src="/assets/images/left-nav-arrow.svg" width="6" height="10"></button> |
| <nav class="sub_pages collapsed"> |
| |
| |
| |
| |
| |
| <li><a href="/docs/ignite3/latest/developers-guide/clients/overview" class=''>Overview</a></li> |
| |
| |
| |
| |
| <li><a href="/docs/ignite3/latest/developers-guide/clients/java" class=''>Java Clients</a></li> |
| |
| |
| |
| |
| <li><a href="/docs/ignite3/latest/developers-guide/clients/dotnet" class=''>.NET Clients</a></li> |
| |
| |
| |
| |
| <li><a href="/docs/ignite3/latest/developers-guide/clients/cpp" class=''>C++ Clients</a></li> |
| |
| |
| |
| |
| <li><a href="/docs/ignite3/latest/developers-guide/clients/python" class=''>Python Clients</a></li> |
| |
| |
| |
| |
| <li><a href="/docs/ignite3/latest/developers-guide/clients/ado" class=''>ADO.NET Integration</a></li> |
| |
| |
| |
| |
| <li><a href="/docs/ignite3/latest/developers-guide/clients/linq" class=''>.NET LINQ Queries</a></li> |
| |
| |
| |
| |
| <li><a href="/docs/ignite3/latest/developers-guide/clients/jdbc-driver" class=''>JDBC Driver</a></li> |
| |
| </nav> |
| |
| </li> |
| |
| |
| |
| |
| |
| |
| <li> |
| |
| |
| |
| <button |
| type='button' |
| class='collapsed '>Working with SQL<img class="state-indicator" src="/assets/images/left-nav-arrow.svg" width="6" height="10"></button> |
| <nav class="sub_pages collapsed"> |
| |
| |
| |
| |
| |
| <li><a href="/docs/ignite3/latest/developers-guide/sql/calcite-based-sql-engine" class=''>Introduction</a></li> |
| |
| |
| |
| |
| <li><a href="/docs/ignite3/latest/developers-guide/sql/sql-api" class=''>SQL API</a></li> |
| |
| |
| |
| |
| <li><a href="/docs/ignite3/latest/developers-guide/sql/jdbc-driver" class=''>JDBC Driver</a></li> |
| |
| |
| |
| |
| <li><a href="/docs/ignite3/latest/developers-guide/sql/odbc/odbc-driver" class=''>ODBC Driver</a></li> |
| |
| |
| |
| |
| <li><a href="/docs/ignite3/latest/developers-guide/sql/odbc/connection-string" class=''>ODBC Connection String</a></li> |
| |
| |
| |
| |
| <li><a href="/docs/ignite3/latest/developers-guide/sql/odbc/querying-modifying-data" class=''>Querying and Modifying Data</a></li> |
| |
| |
| |
| |
| <li><a href="/docs/ignite3/latest/developers-guide/sql/odbc/specification" class=''>Standard Conformance</a></li> |
| |
| </nav> |
| |
| </li> |
| |
| |
| |
| |
| |
| |
| <li> |
| |
| <a href="/docs/ignite3/latest/developers-guide/java-to-tables" |
| class='' |
| >Tables from Java Classes</a> |
| |
| </li> |
| |
| |
| |
| |
| |
| |
| <li> |
| |
| |
| |
| <button |
| type='button' |
| class='collapsed '>Distributed Computing<img class="state-indicator" src="/assets/images/left-nav-arrow.svg" width="6" height="10"></button> |
| <nav class="sub_pages collapsed"> |
| |
| |
| |
| |
| |
| <li><a href="/docs/ignite3/latest/developers-guide/compute/compute" class=''>Compute API</a></li> |
| |
| |
| |
| |
| <li><a href="/docs/ignite3/latest/developers-guide/compute/serialization" class=''>Serialization</a></li> |
| |
| </nav> |
| |
| </li> |
| |
| |
| |
| |
| |
| |
| <li> |
| |
| <a href="/docs/ignite3/latest/developers-guide/transactions" |
| class='' |
| >Transactions</a> |
| |
| </li> |
| |
| |
| |
| |
| |
| |
| <li> |
| |
| <a href="/docs/ignite3/latest/developers-guide/data-streamer" |
| class='' |
| >Data Streaming</a> |
| |
| </li> |
| |
| |
| |
| |
| |
| |
| <li> |
| |
| <a href="/docs/ignite3/latest/developers-guide/code-deployment/code-deployment" |
| class='' |
| >Code Deployment</a> |
| |
| </li> |
| |
| |
| |
| |
| |
| |
| <li> |
| |
| |
| |
| <button |
| type='button' |
| class='collapsed '>Events<img class="state-indicator" src="/assets/images/left-nav-arrow.svg" width="6" height="10"></button> |
| <nav class="sub_pages collapsed"> |
| |
| |
| |
| |
| |
| <li><a href="/docs/ignite3/latest/developers-guide/events/overview" class=''>Overview</a></li> |
| |
| |
| |
| |
| <li><a href="/docs/ignite3/latest/developers-guide/events/events-list" class=''>Events List</a></li> |
| |
| </nav> |
| |
| </li> |
| |
| |
| |
| |
| |
| |
| <li> |
| |
| <a href="/docs/ignite3/latest/developers-guide/rest/rest-api" |
| class='' |
| >REST API</a> |
| |
| </li> |
| |
| |
| |
| </nav> |
| |
| </li> |
| |
| <li> |
| |
| |
| |
| |
| |
| |
| <button type='button' class='group-toggle collapsed '>Administrator's Guide<img class="state-indicator" src="/assets/images/left-nav-arrow.svg" width="6" height="10"></button> |
| <nav class='nav-group collapsed'> |
| |
| |
| |
| |
| |
| |
| |
| <li> |
| |
| |
| |
| <button |
| type='button' |
| class='collapsed '>Ignite Configuration<img class="state-indicator" src="/assets/images/left-nav-arrow.svg" width="6" height="10"></button> |
| <nav class="sub_pages collapsed"> |
| |
| |
| |
| |
| |
| <li><a href="/docs/ignite3/latest/administrators-guide/config/node-config" class=''>Node Configuration Parameters</a></li> |
| |
| |
| |
| |
| <li><a href="/docs/ignite3/latest/administrators-guide/config/cluster-config" class=''>Cluster Configuration Parameters</a></li> |
| |
| |
| |
| |
| <li><a href="/docs/ignite3/latest/administrators-guide/config/cli-config" class=''>CLI Configuration</a></li> |
| |
| </nav> |
| |
| </li> |
| |
| |
| |
| |
| |
| |
| <li> |
| |
| |
| |
| <button |
| type='button' |
| class='collapsed '>Distributed Storage<img class="state-indicator" src="/assets/images/left-nav-arrow.svg" width="6" height="10"></button> |
| <nav class="sub_pages collapsed"> |
| |
| |
| |
| |
| |
| <li><a href="/docs/ignite3/latest/administrators-guide/storage/storage-overview" class=''>Storage Overview</a></li> |
| |
| |
| |
| |
| <li><a href="/docs/ignite3/latest/administrators-guide/storage/storage-profiles" class=''>Storage Profiles</a></li> |
| |
| |
| |
| |
| <li><a href="/docs/ignite3/latest/administrators-guide/storage/data-partitions" class=''>Data Partitions</a></li> |
| |
| |
| |
| |
| <li><a href="/docs/ignite3/latest/administrators-guide/storage/distribution-zones" class=''>Distribution Zones</a></li> |
| |
| </nav> |
| |
| </li> |
| |
| |
| |
| |
| |
| |
| <li> |
| |
| |
| |
| <button |
| type='button' |
| class='collapsed '>Storage Engines<img class="state-indicator" src="/assets/images/left-nav-arrow.svg" width="6" height="10"></button> |
| <nav class="sub_pages collapsed"> |
| |
| |
| |
| |
| |
| <li><a href="/docs/ignite3/latest/administrators-guide/storage/engines/storage-engines" class=''>Storage Engines Overview</a></li> |
| |
| |
| |
| |
| <li><a href="/docs/ignite3/latest/administrators-guide/storage/engines/aipersist" class=''>AIPersist Engine</a></li> |
| |
| |
| |
| |
| <li><a href="/docs/ignite3/latest/administrators-guide/storage/engines/rocksdb" class=''>RocksDB Engine</a></li> |
| |
| |
| |
| |
| <li><a href="/docs/ignite3/latest/administrators-guide/storage/engines/aimem" class=''>AIMem Engine</a></li> |
| |
| </nav> |
| |
| </li> |
| |
| |
| |
| |
| |
| |
| <li> |
| |
| <a href="/docs/ignite3/latest/administrators-guide/lifecycle" |
| class='' |
| >Cluster Lifecycle</a> |
| |
| </li> |
| |
| |
| |
| |
| |
| |
| <li> |
| |
| <a href="/docs/ignite3/latest/administrators-guide/colocation" |
| class='' |
| >Data Colocation</a> |
| |
| </li> |
| |
| |
| |
| |
| |
| |
| <li> |
| |
| <a href="/docs/ignite3/latest/administrators-guide/disaster-recovery" |
| class='' |
| >Disaster Recovery for Partitions</a> |
| |
| </li> |
| |
| |
| |
| |
| |
| |
| <li> |
| |
| <a href="/docs/ignite3/latest/administrators-guide/system-groups-recovery" |
| class='' |
| >Disaster Recovery for System Groups</a> |
| |
| </li> |
| |
| |
| |
| |
| |
| |
| <li> |
| |
| <a href="/docs/ignite3/latest/administrators-guide/cluster-security" |
| class='' |
| >Cluster Security</a> |
| |
| </li> |
| |
| |
| |
| |
| |
| |
| <li> |
| |
| |
| |
| <button |
| type='button' |
| class='collapsed '>Security and Authentication<img class="state-indicator" src="/assets/images/left-nav-arrow.svg" width="6" height="10"></button> |
| <nav class="sub_pages collapsed"> |
| |
| |
| |
| |
| |
| <li><a href="/docs/ignite3/latest/administrators-guide/security/ssl-tls" class=''>SSL/TLS</a></li> |
| |
| |
| |
| |
| <li><a href="/docs/ignite3/latest/administrators-guide/security/authentication" class=''>Authentication</a></li> |
| |
| </nav> |
| |
| </li> |
| |
| |
| |
| |
| |
| |
| <li> |
| |
| |
| |
| <button |
| type='button' |
| class='collapsed '>Metrics and Monitoring<img class="state-indicator" src="/assets/images/left-nav-arrow.svg" width="6" height="10"></button> |
| <nav class="sub_pages collapsed"> |
| |
| |
| |
| |
| |
| <li><a href="/docs/ignite3/latest/administrators-guide/metrics/configuring-metrics" class=''>Configuring Metrics</a></li> |
| |
| |
| |
| |
| <li><a href="/docs/ignite3/latest/administrators-guide/metrics/metrics-list" class=''>Metrics List</a></li> |
| |
| |
| |
| |
| <li><a href="/docs/ignite3/latest/administrators-guide/metrics/system-views" class=''>System Views</a></li> |
| |
| </nav> |
| |
| </li> |
| |
| |
| |
| |
| |
| |
| <li> |
| |
| <a href="/docs/ignite3/latest/administrators-guide/handling-exceptions" |
| class='' |
| >Handling Exceptions</a> |
| |
| </li> |
| |
| |
| |
| </nav> |
| |
| </li> |
| |
| <li> |
| |
| |
| |
| |
| |
| |
| <button type='button' class='group-toggle collapsed '>SQL Reference<img class="state-indicator" src="/assets/images/left-nav-arrow.svg" width="6" height="10"></button> |
| <nav class='nav-group collapsed'> |
| |
| |
| |
| |
| |
| |
| |
| <li> |
| |
| <a href="/docs/ignite3/latest/sql-reference/ddl" |
| class='' |
| >Data Definition Language (DDL)</a> |
| |
| </li> |
| |
| |
| |
| |
| |
| |
| <li> |
| |
| <a href="/docs/ignite3/latest/sql-reference/dml" |
| class='' |
| >Data Manipulation Language (DML)</a> |
| |
| </li> |
| |
| |
| |
| |
| |
| |
| <li> |
| |
| <a href="/docs/ignite3/latest/sql-reference/transactions" |
| class='' |
| >Transactions</a> |
| |
| </li> |
| |
| |
| |
| |
| |
| |
| <li> |
| |
| <a href="/docs/ignite3/latest/sql-reference/distribution-zones" |
| class='' |
| >Distribution Zones</a> |
| |
| </li> |
| |
| |
| |
| |
| |
| |
| <li> |
| |
| <a href="/docs/ignite3/latest/sql-reference/data-types" |
| class='' |
| >Data Types</a> |
| |
| </li> |
| |
| |
| |
| |
| |
| |
| <li> |
| |
| <a href="/docs/ignite3/latest/sql-reference/operators-and-functions" |
| class='' |
| >Supported Operators and Functions</a> |
| |
| </li> |
| |
| |
| |
| |
| |
| |
| <li> |
| |
| <a href="/docs/ignite3/latest/sql-reference/operational-commands" |
| class='' |
| >Operational Commands</a> |
| |
| </li> |
| |
| |
| |
| |
| |
| |
| <li> |
| |
| <a href="/docs/ignite3/latest/sql-reference/grammar-reference" |
| class='' |
| >Grammar Reference</a> |
| |
| </li> |
| |
| |
| |
| |
| |
| |
| <li> |
| |
| <a href="/docs/ignite3/latest/sql-reference/keywords" |
| class='' |
| >Keywords</a> |
| |
| </li> |
| |
| |
| |
| |
| |
| |
| <li> |
| |
| <a href="/docs/ignite3/latest/sql-reference/sql-conformance" |
| class='' |
| >SQL Conformance</a> |
| |
| </li> |
| |
| |
| |
| |
| |
| |
| <li> |
| |
| |
| |
| <button |
| type='button' |
| class='collapsed '>Explain Statement<img class="state-indicator" src="/assets/images/left-nav-arrow.svg" width="6" height="10"></button> |
| <nav class="sub_pages collapsed"> |
| |
| |
| |
| |
| |
| <li><a href="/docs/ignite3/latest/sql-reference/explain-statement" class=''>Explain Statement</a></li> |
| |
| |
| |
| |
| <li><a href="/docs/ignite3/latest/sql-reference/explain-operators-list" class=''>List Of Operators</a></li> |
| |
| </nav> |
| |
| </li> |
| |
| |
| |
| </nav> |
| |
| </li> |
| |
| <li> |
| |
| |
| |
| |
| |
| |
| <button type='button' class='group-toggle expanded parent'>SQL Performance Tuning<img class="state-indicator" src="/assets/images/left-nav-arrow.svg" width="6" height="10"></button> |
| <nav class='nav-group expanded'> |
| |
| |
| |
| |
| |
| |
| |
| <li> |
| |
| <a href="/docs/ignite3/latest/sql-tuning/sql-tuning" |
| class='active' |
| >SQL Performance Tuning</a> |
| |
| </li> |
| |
| |
| |
| |
| |
| |
| <li> |
| |
| <a href="/docs/ignite3/latest/sql-tuning/using-explain" |
| class='' |
| >Improving Queries</a> |
| |
| </li> |
| |
| |
| |
| </nav> |
| |
| </li> |
| |
| <li> |
| |
| |
| <a href="/docs/ignite3/latest/general-tips" class='' >General Configuration Tips</a> |
| |
| </li> |
| |
| <li> |
| |
| |
| <a href="/docs/ignite3/latest/glossary/glossary" class='' >Glossary</a> |
| |
| </li> |
| |
| </nav> |
| <div class="left-nav__overlay"></div> |
| |
| |
| <article data-swiftype-index='true'> |
| <a class='edit-link' href="https://github.com/apache/ignite-3/tree/main/docs/_docs/sql-tuning/sql-tuning.adoc" target="_blank">Edit</a> |
| |
| <h1>SQL Performance Tuning</h1> |
| |
| <div class="sect1"> |
| <h2 id="optimizer-hints">Optimizer Hints</h2> |
| <div class="sectionbody"> |
| <div class="paragraph"> |
| <p>The query optimizer tries to execute the fastest execution plan. However, you can know about the data design, application design or data distribution in your cluster better. SQL hints can help the optimizer to make optimizations more rationally or build execution plan faster.</p> |
| </div> |
| <div class="admonitionblock note"> |
| <table> |
| <tr> |
| <td class="icon"> |
| <div class="title">Note</div> |
| </td> |
| <td class="content"> |
| <div class="paragraph"> |
| <p>SQL hints are optional to apply and might be skipped in some cases.</p> |
| </div> |
| </td> |
| </tr> |
| </table> |
| </div> |
| <div class="sect2"> |
| <h3 id="hints-format">Hints format</h3> |
| <div class="paragraph"> |
| <p>SQL hints are defined by a special comment /*+ HINT */, referred to as a <em>hint block</em>. Spaces before and after the |
| hint name are required. The hint block must be placed right after the operator. Several hints for one relation operator are not supported.</p> |
| </div> |
| <div class="paragraph"> |
| <p>Example:</p> |
| </div> |
| <div class="listingblock"> |
| <div class="content"> |
| <pre class="rouge highlight"><code data-lang="SQL">SELECT /*+ NO_INDEX */ T1.* FROM TBL1 where T1.V1=? and T1.V2=?</code></pre> |
| </div> |
| </div> |
| <div class="sect3"> |
| <h4 id="hint-parameters">Hint parameters</h4> |
| <div class="paragraph"> |
| <p>Hint parameters, if required, are placed in brackets after the hint name and separated by commas.</p> |
| </div> |
| <div class="paragraph"> |
| <p>The hint parameter can be quoted. Quoted parameter is case-sensitive. The quoted and unquoted parameters cannot be |
| defined for the same hint.</p> |
| </div> |
| <div class="paragraph"> |
| <p>Example:</p> |
| </div> |
| <div class="listingblock"> |
| <div class="content"> |
| <pre class="rouge highlight"><code data-lang="SQL">SELECT /*+ FORCE_INDEX(TBL1_IDX2,TBL2_IDX1) */ T1.V1, T2.V1 FROM TBL1 T1, TBL2 T2 WHERE T1.V1 = T2.V1 AND T1.V2 > ? AND T2.V2 > ?; |
| |
| SELECT /*+ FORCE_INDEX('TBL2_idx1') */ T1.V1, T2.V1 FROM TBL1 T1, TBL2 T2 WHERE T1.V1 = T2.V1 AND T1.V2 > ? AND T2.V2 > ?;</code></pre> |
| </div> |
| </div> |
| </div> |
| </div> |
| <div class="sect2"> |
| <h3 id="hints-errors">Hints errors</h3> |
| <div class="paragraph"> |
| <p>The optimizer tries to apply every hint and its parameters, if possible. But it skips the hint or hint parameter if:</p> |
| </div> |
| <div class="ulist"> |
| <ul> |
| <li> |
| <p>The hint is not supported.</p> |
| </li> |
| <li> |
| <p>Required hint parameters are not passed.</p> |
| </li> |
| <li> |
| <p>The hint parameters have been passed, but the hint does not support any parameter.</p> |
| </li> |
| <li> |
| <p>The hint parameter is incorrect or refers to a nonexistent object, such as a nonexistent index or table.</p> |
| </li> |
| <li> |
| <p>The current hints or current parameters are incompatible with the previous ones, such as forcing the use and disabling of the same index.</p> |
| </li> |
| </ul> |
| </div> |
| <div class="paragraph"> |
| <p>If a <code>FORCE_INDEX</code> hint references an index that does not exist, the following error will be thrown:</p> |
| </div> |
| <div class="listingblock"> |
| <div class="content"> |
| <pre class="rouge highlight"><code data-lang="java"><span class="nc">Hints</span> <span class="n">mentioned</span> <span class="n">indexes</span> <span class="s">"IDX_NOT_FOUND1"</span><span class="o">,</span> <span class="s">"IDX_NOT_FOUND2"</span> <span class="n">were</span> <span class="n">not</span> <span class="n">found</span><span class="o">.</span></code></pre> |
| </div> |
| </div> |
| </div> |
| <div class="sect2"> |
| <h3 id="supported-hints">Supported hints</h3> |
| <div class="sect3"> |
| <h4 id="force_index-no_index">FORCE_INDEX / NO_INDEX</h4> |
| <div class="paragraph"> |
| <p>Forces or disables index scan.</p> |
| </div> |
| <div class="sect4"> |
| <h5 id="parameters">Parameters:</h5> |
| <div class="ulist"> |
| <ul> |
| <li> |
| <p>Empty. To force an index scan for every underlying table. Optimizer will choose any available index. Or to disable all indexes.</p> |
| </li> |
| <li> |
| <p>Single index name to use or skip exactly this index.</p> |
| </li> |
| <li> |
| <p>Several index names. They can relate to different tables. The optimizer will choose indexes for scanning or skip them all.</p> |
| </li> |
| </ul> |
| </div> |
| </div> |
| <div class="sect4"> |
| <h5 id="examples">Examples:</h5> |
| <div class="listingblock"> |
| <div class="content"> |
| <pre class="rouge highlight"><code data-lang="SQL">SELECT /*+ FORCE_INDEX */ T1.* FROM TBL1 T1 WHERE T1.V1 = T2.V1 AND T1.V2 > ?; |
| |
| SELECT /*+ FORCE_INDEX(TBL1_IDX2, TBL2_IDX1) */ T1.V1, T2.V1 FROM TBL1 T1, TBL2 T2 WHERE T1.V1 = T2.V1 AND T1.V2 > ? AND T2.V2 > ?; |
| |
| SELECT /*+ NO_INDEX */ T1.* FROM TBL1 T1 WHERE T1.V1 = T2.V1 AND T1.V2 > ?; |
| |
| SELECT /*+ NO_INDEX(TBL1_IDX2, TBL2_IDX1) */ T1.V1, T2.V1 FROM TBL1 T1, TBL2 T2 WHERE T1.V1 = T2.V1 AND T1.V2 > ? AND T2.V2 > ?;</code></pre> |
| </div> |
| </div> |
| <div class="admonitionblock note"> |
| <table> |
| <tr> |
| <td class="icon"> |
| <div class="title">Note</div> |
| </td> |
| <td class="content"> |
| The query cannot have both <code>FORCE_INDEX</code> and <code>NO_INDEX</code> hints at the same time. |
| </td> |
| </tr> |
| </table> |
| </div> |
| </div> |
| </div> |
| </div> |
| </div> |
| </div> |
| <div class="sect1"> |
| <h2 id="using-explain-statement">Using EXPLAIN Statement</h2> |
| <div class="sectionbody"> |
| |
| </div> |
| </div> |
| <div class="sect1"> |
| <h2 id="explain-plan-for-statement">EXPLAIN PLAN FOR Statement</h2> |
| <div class="sectionbody"> |
| <div class="paragraph"> |
| <p>Apache Ignite supports the <a href="/docs/ignite3/latest/sql-reference/operational-commands#explain"><code>EXPLAIN PLAN FOR</code></a> statement that can be used to read the execution plan of a query.</p> |
| </div> |
| <div class="paragraph"> |
| <p>Use this command to analyse your queries for possible optimization, for example:</p> |
| </div> |
| <div class="listingblock"> |
| <div class="content"> |
| <pre class="rouge highlight"><code data-lang="sql"><span class="k">EXPLAIN</span> <span class="n">PLAN</span> <span class="k">FOR</span> <span class="k">SELECT</span> <span class="n">name</span> <span class="k">FROM</span> <span class="n">Person</span> <span class="k">WHERE</span> <span class="n">age</span> <span class="o">=</span> <span class="mi">26</span><span class="p">;</span></code></pre> |
| </div> |
| </div> |
| <div class="paragraph"> |
| <p>Here is how the results of the explanation may look like:</p> |
| </div> |
| <div class="listingblock"> |
| <div class="content"> |
| <pre>╔═══════════════════════════════╗ |
| ║ PLAN ║ |
| ╠═══════════════════════════════╣ |
| ║ Exchange ║ |
| ║ distribution: single ║ |
| ║ est. row count: 333000 ║ |
| ║ TableScan ║ |
| ║ table: [PUBLIC, PERSON] ║ |
| ║ filters: =(AGE, 26) ║ |
| ║ fields: [$f0] ║ |
| ║ projects: [NAME] ║ |
| ║ est. row count: 333000 ║ |
| ╚═══════════════════════════════╝</pre> |
| </div> |
| </div> |
| </div> |
| </div> |
| <div class="sect1"> |
| <h2 id="explain-mapping-for-statement">EXPLAIN MAPPING FOR Statement</h2> |
| <div class="sectionbody"> |
| <div class="paragraph"> |
| <p>Apache Ignite supports the <a href="/docs/ignite3/latest/sql-reference/operational-commands#explain"><code>EXPLAIN MAPPING FOR</code></a> statement that can be used to track how the query is split and what nodes the subqueries are executed on.</p> |
| </div> |
| <div class="paragraph"> |
| <p>Use this command if you need an insight into how the query is broken down and executed across multiple nodes in the distributed cluster.</p> |
| </div> |
| <div class="listingblock"> |
| <div class="content"> |
| <pre class="rouge highlight"><code data-lang="sql"><span class="k">EXPLAIN</span> <span class="n">MAPPING</span> <span class="k">FOR</span> <span class="k">SELECT</span> <span class="n">name</span> <span class="k">FROM</span> <span class="n">Person</span> <span class="k">WHERE</span> <span class="n">age</span> <span class="o">=</span> <span class="mi">26</span><span class="p">;</span></code></pre> |
| </div> |
| </div> |
| <div class="paragraph"> |
| <p>Here is how the results of the query may look like:</p> |
| </div> |
| <div class="listingblock"> |
| <div class="content"> |
| <pre>╔═══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╗ |
| ║ PLAN ║ |
| ╠═══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╣ |
| ║ Fragment#0 root ║ |
| ║ executionNodes: [defaultNode] ║ |
| ║ remoteFragments: [1] ║ |
| ║ exchangeSourceNodes: {1=[defaultNode]} ║ |
| ║ tree: ║ |
| ║ Receiver(sourceFragment=1, exchange=1, distribution=single) ║ |
| ║ ║ |
| ║ Fragment#1 ║ |
| ║ targetNodes: [defaultNode] ║ |
| ║ executionNodes: [defaultNode] ║ |
| ║ tables: [PERSON] ║ |
| ║ partitions: {defaultNode=[0:12, 1:12, 2:12, 3:12, 4:12, 5:12, 6:12, 7:12, 8:12, 9:12, 10:12, 11:12, 12:12, 13:12, 14:12, 15:12, 16:12, 17:12, 18:12, 19:12, 20:12, 21:12, 22:12, 23:12, 24:12]} ║ |
| ║ tree: ║ |
| ║ Sender(targetFragment=0, exchange=1, distribution=single) ║ |
| ║ TableScan(name=PUBLIC.PERSON, source=2, partitions=25, distribution=random) ║ |
| ╚═══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╝</pre> |
| </div> |
| </div> |
| </div> |
| </div> |
| <div class="sect1"> |
| <h2 id="query-batching">Query Batching</h2> |
| <div class="sectionbody"> |
| <div class="paragraph"> |
| <p>Apache Ignite handles batched requests faster than individual requests, so we recommend using multi-statement execution when possible.</p> |
| </div> |
| <div class="paragraph"> |
| <p>When executing multiple queries in a single call, similar requests are automatically batched together. When writing large scripts that perform multiple different kinds of operations, we recommend the following order:</p> |
| </div> |
| <div class="ulist"> |
| <ul> |
| <li> |
| <p>All required <a href="/docs/ignite3/latest/sql-reference/ddl">DDL operations</a>;</p> |
| </li> |
| <li> |
| <p>Assigning <a href="/docs/ignite3/latest/sql-reference/access-control">access permissions</a>;</p> |
| </li> |
| <li> |
| <p>Loading data into the tables.</p> |
| </li> |
| </ul> |
| </div> |
| <div class="paragraph"> |
| <p>As execution of each statement is considered complete when the first page is ready to be returned, when working with large data sets, <code>SELECT</code> statements may be affected by later statements in the same script.</p> |
| </div> |
| </div> |
| </div> |
| <div class="sect1"> |
| <h2 id="performance-consideration-for-correlated-subqueries">Performance Consideration For Correlated Subqueries</h2> |
| <div class="sectionbody"> |
| <div class="paragraph"> |
| <p>Apache Ignite supports correlated subqueries, but the performance of certain complex correlated subqueries may be insufficient, especially when used in high-volume transactional or analytical workloads.</p> |
| </div> |
| <div class="sect2"> |
| <h3 id="what-are-correlated-subqueries">What Are Correlated Subqueries</h3> |
| <div class="paragraph"> |
| <p>A correlated subquery is a subquery that depends on values from the outer query for execution. It is evaluated once for every row of the outer query.</p> |
| </div> |
| <div class="paragraph"> |
| <p>For example, for a schema that is defined in the following way:</p> |
| </div> |
| <div class="listingblock"> |
| <div class="content"> |
| <pre class="rouge highlight"><code data-lang="sql"><span class="k">CREATE</span> <span class="k">TABLE</span> <span class="n">projects</span> <span class="p">(</span><span class="n">id</span> <span class="nb">INT</span> <span class="k">PRIMARY</span> <span class="k">KEY</span><span class="p">,</span> <span class="n">name</span> <span class="nb">VARCHAR</span><span class="p">);</span> |
| <span class="k">CREATE</span> <span class="k">TABLE</span> <span class="n">employees</span> <span class="p">(</span><span class="n">id</span> <span class="nb">INT</span> <span class="k">PRIMARY</span> <span class="k">KEY</span><span class="p">,</span> <span class="n">department_id</span> <span class="nb">INT</span><span class="p">,</span> <span class="n">name</span> <span class="nb">VARCHAR</span><span class="p">,</span> <span class="n">salary</span> <span class="nb">DECIMAl</span><span class="p">);</span> |
| <span class="k">CREATE</span> <span class="k">TABLE</span> <span class="n">departments</span> <span class="p">(</span><span class="n">id</span> <span class="nb">INT</span> <span class="k">PRIMARY</span> <span class="k">KEY</span><span class="p">,</span> <span class="n">name</span> <span class="nb">VARCHAR</span><span class="p">);</span> |
| <span class="k">CREATE</span> <span class="k">TABLE</span> <span class="n">assignments</span> <span class="p">(</span><span class="n">project_id</span> <span class="nb">INT</span><span class="p">,</span> <span class="n">employee_id</span> <span class="nb">INT</span><span class="p">,</span> <span class="k">PRIMARY</span> <span class="k">KEY</span> <span class="p">(</span><span class="n">project_id</span><span class="p">,</span> <span class="n">employee_id</span><span class="p">));</span></code></pre> |
| </div> |
| </div> |
| <div class="paragraph"> |
| <p>The correlated subquery may look like this:</p> |
| </div> |
| <div class="listingblock"> |
| <div class="content"> |
| <pre class="rouge highlight"><code data-lang="sql"><span class="k">SELECT</span> <span class="n">e</span><span class="p">.</span><span class="n">name</span><span class="p">,</span> |
| <span class="p">(</span><span class="k">SELECT</span> <span class="k">COUNT</span><span class="p">(</span><span class="o">*</span><span class="p">)</span> |
| <span class="k">FROM</span> <span class="n">assignments</span> <span class="n">a</span> |
| <span class="k">WHERE</span> <span class="n">a</span><span class="p">.</span><span class="n">employee_id</span> <span class="o">=</span> <span class="n">e</span><span class="p">.</span><span class="n">id</span> |
| <span class="p">)</span> <span class="k">AS</span> <span class="n">project_count</span> |
| <span class="k">FROM</span> <span class="n">employees</span> <span class="n">e</span><span class="p">;</span></code></pre> |
| </div> |
| </div> |
| <div class="paragraph"> |
| <p>Here, the subquery references <code>e.id</code> from the outer query, meaning it’s re-evaluated for every employee row, leading to N separate subquery executions for N employees.</p> |
| </div> |
| </div> |
| <div class="sect2"> |
| <h3 id="performance-impact">Performance Impact</h3> |
| <div class="paragraph"> |
| <p>In Apache Ignite 3, repeated subquery executions are not automatically optimized. As a result:</p> |
| </div> |
| <div class="ulist"> |
| <ul> |
| <li> |
| <p>Scalar subqueries may become bottlenecks.</p> |
| </li> |
| <li> |
| <p>Even small tables can cause high CPU and memory consumption when repeatedly queried.</p> |
| </li> |
| <li> |
| <p>Certain queries may perform slower than expected.</p> |
| </li> |
| </ul> |
| </div> |
| </div> |
| <div class="sect2"> |
| <h3 id="improving-performance">Improving Performance</h3> |
| <div class="paragraph"> |
| <p>In general, highly-selective outer queries with cheap scalar subqueries (like single-row index lookup) will perform just fine. Here is an example:</p> |
| </div> |
| <div class="listingblock"> |
| <div class="content"> |
| <pre class="rouge highlight"><code data-lang="sql"><span class="c1">-- This query returns an employee along with the name of the department they belong to.</span> |
| <span class="c1">-- It uses a correlated scalar subquery to resolve the department name.</span> |
| <span class="c1">--</span> |
| <span class="c1">-- Note the predicate `e.id = ?`, which filters by the employee's primary key.</span> |
| <span class="c1">-- This makes the outer query highly selective —- typically returning only a single row.</span> |
| <span class="c1">--</span> |
| <span class="c1">-- Because the subquery is evaluated only once (or a very small number of times),</span> |
| <span class="c1">-- using a correlated scalar subquery is safe and has negligible performance impact</span> |
| <span class="c1">-- in this case. There's no need to rewrite it using a join.</span> |
| <span class="k">SELECT</span> <span class="n">e</span><span class="p">.</span><span class="o">*</span><span class="p">,</span> |
| <span class="p">(</span><span class="k">SELECT</span> <span class="n">name</span> |
| <span class="k">FROM</span> <span class="n">departments</span> |
| <span class="k">WHERE</span> <span class="n">id</span> <span class="o">=</span> <span class="n">e</span><span class="p">.</span><span class="n">department_id</span> |
| <span class="p">)</span> <span class="k">AS</span> <span class="n">employees_department</span> |
| <span class="k">FROM</span> <span class="n">employees</span> <span class="n">e</span> |
| <span class="k">WHERE</span> <span class="n">e</span><span class="p">.</span><span class="n">id</span> <span class="o">=</span> <span class="o">?</span><span class="p">;</span></code></pre> |
| </div> |
| </div> |
| <div class="paragraph"> |
| <p>Similar query but without predicate may result in lower performance. If the query with predicate finishes in <code>0.007s</code>, similar query without predicate could take up to <code>2.4s</code>.</p> |
| </div> |
| <div class="paragraph"> |
| <p>Here is another example:</p> |
| </div> |
| <div class="listingblock"> |
| <div class="content"> |
| <pre class="rouge highlight"><code data-lang="sql"><span class="c1">-- This query returns all employees along with the name of the department they</span> |
| <span class="c1">-- belong to.</span> |
| <span class="k">SELECT</span> <span class="n">e</span><span class="p">.</span><span class="o">*</span><span class="p">,</span> |
| <span class="p">(</span><span class="k">SELECT</span> <span class="n">name</span> |
| <span class="k">FROM</span> <span class="n">departments</span> |
| <span class="k">WHERE</span> <span class="n">id</span> <span class="o">=</span> <span class="n">e</span><span class="p">.</span><span class="n">department_id</span> |
| <span class="p">)</span> <span class="k">AS</span> <span class="n">employees_department</span> |
| <span class="k">FROM</span> <span class="n">employees</span> <span class="n">e</span><span class="p">;</span></code></pre> |
| </div> |
| </div> |
| <div class="paragraph"> |
| <p>Query like the one above may easily be rewritten with regular <code>JOIN</code>:</p> |
| </div> |
| <div class="listingblock"> |
| <div class="content"> |
| <pre class="rouge highlight"><code data-lang="sql"><span class="c1">-- Equivalent query to the previous example, but uses a `LEFT JOIN` instead of a</span> |
| <span class="c1">-- correlated subquery. This rewrite is valid as long as the subquery in the original</span> |
| <span class="c1">-- version would return at most one row.</span> |
| <span class="c1">--</span> |
| <span class="c1">-- If multiple rows exist in the `departments` table for the same `id`, the original</span> |
| <span class="c1">-- scalar subquery would result in a runtime error (due to a non-scalar result), while</span> |
| <span class="c1">-- the join version would produce duplicated rows in the output.</span> |
| <span class="c1">--</span> |
| <span class="c1">-- In our case, `departments.id` is a primary key, so the join is safe and will return</span> |
| <span class="c1">-- at most one matching department per employee.</span> |
| <span class="c1">--</span> |
| <span class="c1">-- A `LEFT JOIN` is used to ensure that employees with no matching department are still</span> |
| <span class="c1">-- returned. If it's guaranteed that every employee has a valid department reference,</span> |
| <span class="c1">-- an `INNER JOIN` may be used instead, which is slightly more efficient.</span> |
| <span class="k">SELECT</span> <span class="n">e</span><span class="p">.</span><span class="o">*</span><span class="p">,</span> |
| <span class="n">d</span><span class="p">.</span><span class="n">name</span> <span class="k">AS</span> <span class="n">employees_department</span> |
| <span class="k">FROM</span> <span class="n">employees</span> <span class="n">e</span> |
| <span class="k">LEFT</span> <span class="k">JOIN</span> <span class="n">departments</span> <span class="n">d</span> <span class="k">ON</span> <span class="n">d</span><span class="p">.</span><span class="n">id</span> <span class="o">=</span> <span class="n">e</span><span class="p">.</span><span class="n">department_id</span><span class="p">;</span></code></pre> |
| </div> |
| </div> |
| <div class="paragraph"> |
| <p>Rewritten query on the same environment finishes significantly faster.</p> |
| </div> |
| </div> |
| <div class="sect2"> |
| <h3 id="examples-of-improved-queries">Examples of Improved Queries</h3> |
| <div class="paragraph"> |
| <p>The first example shows how you can correctly query the database without evaluating each row:</p> |
| </div> |
| <div class="listingblock"> |
| <div class="content"> |
| <pre class="rouge highlight"><code data-lang="sql"><span class="c1">-- This query returns all employees without assigned projects.</span> |
| <span class="c1">--</span> |
| <span class="c1">-- Finishes in 3.2s (assuming there is an index on `assignments(employee_id)`;</span> |
| <span class="c1">-- without the index, execution time increases significantly —- up to 12s).</span> |
| <span class="k">SELECT</span> <span class="n">e</span><span class="p">.</span><span class="n">id</span><span class="p">,</span> <span class="n">e</span><span class="p">.</span><span class="n">name</span> |
| <span class="k">FROM</span> <span class="n">employees</span> <span class="n">e</span> |
| <span class="k">WHERE</span> <span class="k">NOT</span> <span class="k">EXISTS</span> <span class="p">(</span> |
| <span class="k">SELECT</span> <span class="mi">1</span> |
| <span class="k">FROM</span> <span class="n">assignments</span> <span class="n">a</span> |
| <span class="k">WHERE</span> <span class="n">a</span><span class="p">.</span><span class="n">employee_id</span> <span class="o">=</span> <span class="n">e</span><span class="p">.</span><span class="n">id</span> |
| <span class="p">);</span> |
| |
| <span class="c1">-- Equivalent query without correlated subqueries.</span> |
| <span class="c1">-- Instead of evaluating a subquery for each row, we join the tables and compute</span> |
| <span class="c1">-- the number of assignments using aggregation. It is important to include all</span> |
| <span class="c1">-- columns that form a unique key from the outer table in the `GROUP BY` clause.</span> |
| <span class="c1">-- Otherwise, multiple rows may be grouped together incorrectly, potentially</span> |
| <span class="c1">-- affecting the result. If you're unsure about the uniqueness of specific columns,</span> |
| <span class="c1">-- include all columns from the table's `PRIMARY KEY`.</span> |
| <span class="c1">--</span> |
| <span class="c1">-- A `LEFT JOIN` is used because we want to retain employees even when there is</span> |
| <span class="c1">-- no matching assignment. An `INNER JOIN` would exclude those employees.</span> |
| <span class="c1">--</span> |
| <span class="c1">-- The `HAVING COUNT(a.employee_id) = 0` clause checks for the absence of matches.</span> |
| <span class="c1">-- You must count a column from the right-hand side of the join that is guaranteed</span> |
| <span class="c1">-- to be non-null. In this case, `a.employee_id` is suitable because the `JOIN`</span> |
| <span class="c1">-- condition (`a.employee_id = e.id`) ensures that only non-null `employee_id`s</span> |
| <span class="c1">-- are matched; nulls are excluded during the join phase.</span> |
| <span class="c1">--</span> |
| <span class="c1">-- Finishes in 0.04s.</span> |
| <span class="k">SELECT</span> <span class="n">e</span><span class="p">.</span><span class="n">id</span><span class="p">,</span> <span class="n">e</span><span class="p">.</span><span class="n">name</span> |
| <span class="k">FROM</span> <span class="n">employees</span> <span class="n">e</span> |
| <span class="k">LEFT</span> <span class="k">JOIN</span> <span class="n">assignments</span> <span class="n">a</span> <span class="k">ON</span> <span class="n">a</span><span class="p">.</span><span class="n">employee_id</span> <span class="o">=</span> <span class="n">e</span><span class="p">.</span><span class="n">id</span> |
| <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">e</span><span class="p">.</span><span class="n">id</span><span class="p">,</span> <span class="n">e</span><span class="p">.</span><span class="n">name</span> |
| <span class="k">HAVING</span> <span class="k">COUNT</span><span class="p">(</span><span class="n">a</span><span class="p">.</span><span class="n">employee_id</span><span class="p">)</span> <span class="o">=</span> <span class="mi">0</span><span class="p">;</span> |
| |
| <span class="c1">-- Similar query, but returns only employees who have at least one project assigned.</span> |
| <span class="c1">-- Note the use of `INNER JOIN`: since we are only interested in employees with a</span> |
| <span class="c1">-- matching assignment, an inner join is both sufficient and more efficient in this case.</span> |
| <span class="c1">--</span> |
| <span class="c1">-- The `HAVING COUNT(a.employee_id) > 0` condition ensures that only employees</span> |
| <span class="c1">-- with one or more matching rows in the `assignments` table are returned.</span> |
| <span class="c1">-- As with the previous example, `a.employee_id` is safe to count because it cannot be null</span> |
| <span class="c1">-- due to the join condition (`a.employee_id = e.id`) filtering out nulls.</span> |
| <span class="c1">--</span> |
| <span class="c1">-- Finishes in 0.03s.</span> |
| <span class="k">SELECT</span> <span class="n">e</span><span class="p">.</span><span class="n">id</span><span class="p">,</span> <span class="n">e</span><span class="p">.</span><span class="n">name</span> |
| <span class="k">FROM</span> <span class="n">employees</span> <span class="n">e</span> |
| <span class="k">JOIN</span> <span class="n">assignments</span> <span class="n">a</span> <span class="k">ON</span> <span class="n">a</span><span class="p">.</span><span class="n">employee_id</span> <span class="o">=</span> <span class="n">e</span><span class="p">.</span><span class="n">id</span> |
| <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">e</span><span class="p">.</span><span class="n">id</span><span class="p">,</span> <span class="n">e</span><span class="p">.</span><span class="n">name</span> |
| <span class="k">HAVING</span> <span class="k">COUNT</span><span class="p">(</span><span class="n">a</span><span class="p">.</span><span class="n">employee_id</span><span class="p">)</span> <span class="o">></span> <span class="mi">0</span><span class="p">;</span></code></pre> |
| </div> |
| </div> |
| <div class="paragraph"> |
| <p>This example demonstrates drastic performance improvement you can gain by improving your queries:</p> |
| </div> |
| <div class="listingblock"> |
| <div class="content"> |
| <pre class="rouge highlight"><code data-lang="sql"><span class="c1">-- This query returns all employees whose salary is the minimum within their department.</span> |
| <span class="c1">--</span> |
| <span class="c1">-- Finishes in 18s.</span> |
| <span class="k">SELECT</span> <span class="n">e</span><span class="p">.</span><span class="o">*</span> |
| <span class="k">FROM</span> <span class="n">employees</span> <span class="n">e</span> |
| <span class="k">WHERE</span> <span class="n">e</span><span class="p">.</span><span class="n">salary</span> <span class="o">=</span> <span class="p">(</span><span class="k">SELECT</span> <span class="k">MIN</span><span class="p">(</span><span class="n">salary</span><span class="p">)</span> <span class="k">FROM</span> <span class="n">employees</span> <span class="k">WHERE</span> <span class="n">department_id</span> <span class="o">=</span> <span class="n">e</span><span class="p">.</span><span class="n">department_id</span><span class="p">);</span> |
| |
| <span class="c1">-- Equivalent query without a correlated subquery.</span> |
| <span class="c1">-- Instead of comparing each employee's salary with a scalar subquery result,</span> |
| <span class="c1">-- we precompute the minimum salary per department using a grouped subquery,</span> |
| <span class="c1">-- and then join it back to the employees table.</span> |
| <span class="c1">--</span> |
| <span class="c1">-- This rewrite is safe because:</span> |
| <span class="c1">-- - For each department, we compute the minimum salary exactly once.</span> |
| <span class="c1">-- - The join condition ensures we only return employees whose salary matches</span> |
| <span class="c1">-- the minimum salary for their department.</span> |
| <span class="c1">-- - No grouping is needed on the outer query because we're performing an equality match</span> |
| <span class="c1">-- on both `department_id` and the computed minimum salary.</span> |
| <span class="c1">--</span> |
| <span class="c1">-- This approach avoids per-row subquery evaluation and leverages set-based operations,</span> |
| <span class="c1">-- which are significantly faster.</span> |
| <span class="c1">--</span> |
| <span class="c1">-- Finishes in 0.02s.</span> |
| <span class="k">SELECT</span> <span class="n">e</span><span class="p">.</span><span class="o">*</span> |
| <span class="k">FROM</span> <span class="n">employees</span> <span class="n">e</span> |
| <span class="k">JOIN</span> <span class="p">(</span> |
| <span class="k">SELECT</span> <span class="n">department_id</span><span class="p">,</span> <span class="k">MIN</span><span class="p">(</span><span class="n">salary</span><span class="p">)</span> <span class="k">AS</span> <span class="n">min_salary</span> |
| <span class="k">FROM</span> <span class="n">employees</span> |
| <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">department_id</span> |
| <span class="p">)</span> <span class="k">AS</span> <span class="n">min_salaries_by_department</span> |
| <span class="k">ON</span> <span class="n">e</span><span class="p">.</span><span class="n">department_id</span> <span class="o">=</span> <span class="n">min_salaries_by_department</span><span class="p">.</span><span class="n">department_id</span> |
| <span class="k">AND</span> <span class="n">e</span><span class="p">.</span><span class="n">salary</span> <span class="o">=</span> <span class="n">min_salaries_by_department</span><span class="p">.</span><span class="n">min_salary</span><span class="p">;</span></code></pre> |
| </div> |
| </div> |
| </div> |
| </div> |
| </div> |
| <div class="sect1"> |
| <h2 id="dropping-cached-plans">Dropping Cached Plans</h2> |
| <div class="sectionbody"> |
| <div class="admonitionblock warning"> |
| <table> |
| <tr> |
| <td class="icon"> |
| <div class="title">Warning</div> |
| </td> |
| <td class="content"> |
| This is an experimental API. |
| </td> |
| </tr> |
| </table> |
| </div> |
| <div class="paragraph"> |
| <p>As optimizing the query plan is a resource-intensive operation, Apache Ignite caches the plan and reuses it for subsequent related queries. As data is updated, the plan may be outdated and require recalculation. By default, the plans expire after the period specified in the <code>ignite.planner.planCacheExpiresAfterSeconds</code> parameter (1800 seconds by default).</p> |
| </div> |
| <div class="paragraph"> |
| <p>To force the update earlier, you can use the <code>sql planner invalidate-cache</code> CLI tool command.</p> |
| </div> |
| <div class="listingblock"> |
| <div class="content"> |
| <pre class="rouge highlight"><code data-lang="text">sql planner invalidate-cache --tables=PUBLIC.Person</code></pre> |
| </div> |
| </div> |
| </div> |
| </div> |
| <div class="copyright"> |
| © 2025 The Apache Software Foundation.<br/> |
| Apache, Apache Ignite, the Apache feather and the Apache Ignite logo are either registered trademarks or trademarks of The Apache Software Foundation. |
| |
| </div> |
| |
| </article> |
| <nav class="right-nav" data-swiftype-index='false'> |
| <div class="toc-wrapper"> |
| <ul class="sectlevel1"> |
| <li><a href="#optimizer-hints">Optimizer Hints</a> |
| <ul class="sectlevel2"> |
| <li><a href="#hints-format">Hints format</a> |
| <ul class="sectlevel3"> |
| <li><a href="#hint-parameters">Hint parameters</a></li> |
| </ul> |
| </li> |
| <li><a href="#hints-errors">Hints errors</a></li> |
| <li><a href="#supported-hints">Supported hints</a> |
| <ul class="sectlevel3"> |
| <li><a href="#force_index-no_index">FORCE_INDEX / NO_INDEX</a> |
| <ul class="sectlevel4"> |
| <li><a href="#parameters">Parameters:</a></li> |
| <li><a href="#examples">Examples:</a></li> |
| </ul> |
| </li> |
| </ul> |
| </li> |
| </ul> |
| </li> |
| <li><a href="#using-explain-statement">Using EXPLAIN Statement</a></li> |
| <li><a href="#explain-plan-for-statement">EXPLAIN PLAN FOR Statement</a></li> |
| <li><a href="#explain-mapping-for-statement">EXPLAIN MAPPING FOR Statement</a></li> |
| <li><a href="#query-batching">Query Batching</a></li> |
| <li><a href="#performance-consideration-for-correlated-subqueries">Performance Consideration For Correlated Subqueries</a> |
| <ul class="sectlevel2"> |
| <li><a href="#what-are-correlated-subqueries">What Are Correlated Subqueries</a></li> |
| <li><a href="#performance-impact">Performance Impact</a></li> |
| <li><a href="#improving-performance">Improving Performance</a></li> |
| <li><a href="#examples-of-improved-queries">Examples of Improved Queries</a></li> |
| </ul> |
| </li> |
| <li><a href="#dropping-cached-plans">Dropping Cached Plans</a></li> |
| </ul> |
| </div> |
| |
| |
| <nav class="promo-nav"> |
| <!--#include virtual="/includes/docs_rightnav_promotion.html" --> |
| <!--a href="#" data-trigger-bugyard-feedback="true" id="doc-feedback-btn">Docs Feedback</a--> |
| </nav> |
| |
| </nav> |
| |
| </section> |
| <script type='module' src='/assets/js/code-copy-to-clipboard.js' async crossorigin></script> |
| |
| <script> |
| // inits deep anchors -- needs to be done here because of https://www.bryanbraun.com/anchorjs/#dont-run-it-too-late |
| anchors.add('.page-docs h1, .page-docs h2, .page-docs h3:not(.discrete), .page-docs h4, .page-docs h5'); |
| anchors.options = { |
| placement: 'right', |
| visible: 'always' |
| }; |
| </script> |
| |
| <script type='module' src='/assets/js/index.js?1763137388' async crossorigin></script> |
| <script type='module' src='/assets/js/versioning.js?1763137388' async crossorigin></script> |
| <script type='module' src='/assets/js/railroad-diagram.js?1763137388' async></script> |
| <script type='module' src='/assets/js/search.js?1763137388' defer crossorigin></script> |
| |
| <link rel="stylesheet" href="/assets/css/styles.css?1763137388" media="print" onload="this.media='all'"> |
| <noscript><link media="all" rel="stylesheet" href="/assets/css/styles.css?1763137388"></noscript> |
| |
| <link rel="stylesheet" href="/assets/css/docsearch.min.css" media="print" onload="this.media='all'"> |
| <noscript><link media="all" rel="stylesheet" href="/assets/css/docsearch.min.css"></noscript> |
| |
| |
| </body> |
| |
| </html> |
