{"id":182,"date":"2020-10-31T19:02:43","date_gmt":"2020-10-31T19:02:43","guid":{"rendered":"https:\/\/maboc.nl\/?p=182"},"modified":"2020-11-02T08:01:00","modified_gmt":"2020-11-02T08:01:00","slug":"index-range-scan","status":"publish","type":"post","link":"https:\/\/maboc.nl\/?p=182","title":{"rendered":"Index Range Scan"},"content":{"rendered":"<p><a href=\"https:\/\/maboc.nl\/?p=154\" target=\"_blank\" rel=\"noopener noreferrer\">Index index<\/a><\/p>\n<p>OK, let&#8217;s talk about the Index Range Scan. The word range reflects that Oracle will scan through (a part) the index. As opposed to an <a href=\"https:\/\/maboc.nl\/?p=156\" target=\"_blank\" rel=\"noopener noreferrer\">Index Unique Scan<\/a> where Oracle exactly knows what to look for and where, it just decends through the tree-structure.<\/p>\n<p>The Index Range Scan does not know how many rows will be returned (0, 1 or more).\u00a0 Just as in the post for the <a href=\"https:\/\/maboc.nl\/?p=156\" target=\"_blank\" rel=\"noopener noreferrer\">Index Unique Scan<\/a> I will use <a href=\"https:\/\/maboc.nl\/?p=158\" target=\"_blank\" rel=\"noopener noreferrer\">this<\/a> setup. In short: a table with (among others) a column N with integers. There is a index on this column. It actually is a unique index, but for this demonstration that does not matter.<\/p>\n<p>Let&#8217;s see this index in action:<\/p>\n<pre style=\"font-family: courier; font-size: smaller;\">(2020-10-31 19:53:52) MARTIJN@_DUN &gt; explain plan for select * from index_demo where n&gt;90000;\r\n(2020-10-31 19:54:28) MARTIJN@_DUN &gt;\r\n(2020-10-31 19:54:29) MARTIJN@_DUN &gt;  select * from table(dbms_xplan.display());\r\n\r\nPLAN_TABLE_OUTPUT\r\n--------------------------------------------------------------------------------------------------------------\r\nPlan hash value: 1600936347\r\n\r\n------------------------------------------------------------------------------------------------------\r\n| Id  | Operation                           | Name           | Rows  | Bytes | Cost (%CPU)| Time     |\r\n------------------------------------------------------------------------------------------------------\r\n|   0 | SELECT STATEMENT                    |                | 10000 |   371K|    82   (0)| 00:00:01 |\r\n|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| INDEX_DEMO     | 10000 |   371K|    82   (0)| 00:00:01 |\r\n|*  2 |   INDEX RANGE SCAN                  | INDEX_DEMO_U_N | 10000 |       |    22   (0)| 00:00:01 |\r\n------------------------------------------------------------------------------------------------------\r\n\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n\r\n   2 - access(\"N\"&gt;90000)\r\n(2020-10-31 19:54:38) MARTIJN@_DUN &gt;\r\n<\/pre>\n<p>And there you have it, an Index Range Scan. When there is a suitable index Oracle can use this access method.<\/p>\n<p>One more thing to notice: line 2 in the explain plan. Oracle will try to batch (as opposed to one-by-one) the reading of the rows as much as possible, to be as fast as possible.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Index index OK, let&#8217;s talk about the Index Range Scan. The word range reflects that Oracle will scan through (a part) the index. As opposed to an Index Unique Scan where Oracle exactly knows what to look for and where, it just decends through the tree-structure. The Index Range Scan does not know how many [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[32,5],"tags":[8,34,24],"class_list":["post-182","post","type-post","status-publish","format-standard","hentry","category-index","category-oracle","tag-index","tag-index-range-scan","tag-oracle"],"_links":{"self":[{"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/posts\/182","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/maboc.nl\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=182"}],"version-history":[{"count":9,"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/posts\/182\/revisions"}],"predecessor-version":[{"id":192,"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/posts\/182\/revisions\/192"}],"wp:attachment":[{"href":"https:\/\/maboc.nl\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=182"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/maboc.nl\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=182"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/maboc.nl\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=182"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}