{"id":1330,"date":"2016-01-14T16:49:29","date_gmt":"2016-01-14T08:49:29","guid":{"rendered":"http:\/\/www.821121.com\/?p=1330"},"modified":"2021-01-07T14:53:31","modified_gmt":"2021-01-07T06:53:31","slug":"%e5%ad%a6%e4%b9%a0%e7%ac%94%e8%ae%b0%e4%b9%8bsql-partial-range-scan-4","status":"publish","type":"post","link":"http:\/\/www.821121.com\/?p=1330","title":{"rendered":"\u5b66\u4e60\u7b14\u8bb0\u4e4bSQL-Partial Range Scan 4"},"content":{"rendered":"<h1>Partial Range Scan 4<\/h1>\n<p><!--more--><\/p>\n<h3>Replace SORT operation by (index) access pathReplace SORT operation by (index) access path<\/h3>\n<p>In order to eliminate the \u201cSORT\u201d operation from the SQL execution plan, we can add the columns used in the ORDER BY clause into the index. This way, we can take advantage of this index to avoid the Full Range Scan operation.<\/p>\n<pre><code class=\"language-sql\">SELECT ord_dept, ordqty * 1000 FROM order WHERE ord_date like &#039;2005%&#039; ORDER BY ord_dept DESC<\/code><\/pre>\n<p>In the SQL statement above, the condition used to filter (drive) the data set is using the column \u201cord_date\u201d while the column used in the ORDER BY clause is the column \u201cord_dept\u201d. If the data set returned by applying the condition \u201corde_date like \u20182005%\u2019\u201d is large, the Full Range Scan will respond slowly. However, if there is also one index on the column ord_dept, we can rewrite the SQL statement as follows,<\/p>\n<pre><code class=\"language-sql\">SELECT \/*+ INDEX_DESC (a ord_dept_index)*\/ * FROM order a WHERE a.ord_date like \u20182005%\u2019 AND ord_dept &gt; &#039; &#039;;<\/code><\/pre>\n<p>This way, we not only remove the ORDER BY clause (by using the hint INDEX_DESC) which make the Partial Range Scan possible, but also make the ord_dept the driving column and the column ord_date be the \u201cfilter\u201d column. According the principle of the Partial Range Scan, if the filtering condition causes large data volume, the execution speed will be fast.<\/p>\n<h4>Use index scan only for partial range scanUse index scan only for partial range scan<\/h4>\n<p>If all the columns used by the SQL statement are included in the index, then the optimizer can only access the index to get the data. There is no need to scan the table in this case.<br \/>\nThis is very efficient as the I\/O will be reduced.<br \/>\nAs a result, to instruct the optimizer to choose this index scan, we need to think carefully for those candidate columns that can be included in the index.<\/p>\n<h4>MAX and MIN functionsMAX and MIN functions<\/h4>\n<p>Since MAX and MIN are aggregate functions, it seems that if the SQL statement has these function used then the Partial Range Scan is impossible.<br \/>\nHowever, in the new optimizer, there is a special process operation for the MAX\/MIN function which uses the Partial Range Scan which make the MAX\/MIN have good response time.<br \/>\nFor example, the index pk_order is based on the column (deptno, seq)\u2026<\/p>\n<pre><code class=\"language-sql\">SELECT MAX(seq) + 1 FROM order WHERE deptno = &#039;1234&#039;;<\/code><\/pre>\n<p>EXECUTION PLAN <\/p>\n<pre><code class=\"language-sql\">SELECT STATEMENT \n  SORT (AGGREGATE) \n    FIRST ROW \n       INDEX (RANGE SCAN (MIN\/MAX)) OF \u2018pk_order\u2019 (UNIQUE)<\/code><\/pre>\n<p>Please note the \u201cFIRST ROW\u201d and \u201cRANGE SCAN(MIN\/MAX) in the execution plan. They make the SQL engine doesn\u2019t need to wait until all the deptno \u20181234\u2019 are scanned before returning the result.<br \/>\nThe SQL statement above is almost executed by the optimizer as the SQL statement below\u2026<\/p>\n<pre><code class=\"language-sql\">SELECT \/*+ INDEX_DESC(order pk_order) *\/ NVL(MAX(SEQ), 0) + 1 FROM order WHERE dept_no = &#039;1234&#039; AND ROWNUM =1;<\/code><\/pre>\n<p>Please note the hint \u201cINDEX_DESC\u201d and ROWNUM are used in the SQL statement \u201cexplicitly tell\u201d the optimizer to choose partial range scan.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Partial Range Scan 4<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[18,48],"tags":[],"class_list":["post-1330","post","type-post","status-publish","format-standard","hentry","category-oracle-11g","category-sql"],"_links":{"self":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts\/1330","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1330"}],"version-history":[{"count":1,"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts\/1330\/revisions"}],"predecessor-version":[{"id":1331,"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts\/1330\/revisions\/1331"}],"wp:attachment":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1330"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1330"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1330"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}