{"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 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"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}]}}