{"id":15430,"date":"2020-03-26T08:02:07","date_gmt":"2020-03-26T08:02:07","guid":{"rendered":"https:\/\/dbtut.com\/?p=15430"},"modified":"2020-03-27T08:42:06","modified_gmt":"2020-03-27T08:42:06","slug":"index-scans-in-oracle","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2020\/03\/26\/index-scans-in-oracle\/","title":{"rendered":"INDEX SCANS IN ORACLE"},"content":{"rendered":"

The index scans are the table access paths used by the oracle optimizer to generate the best execution plan. There are different types of scans that are used by the optimizer, but it all depends upon the selectivity and the predicate we provide in our SQL queries.<\/span><\/p>\n

By far the best way to access \u00a0a row is to get the data by ROWID which is stored in the block if we \u00a0know the exact row_id . But these ROWID are being changed as we perform some DML operations. So until we are sure that this is the exact ROWID we cant use the ROWID to fetch the data from the table.<\/span><\/p>\n

There are other methods which are used by the optimizer to fetch the data from the table.<\/p>\n

FULL TABLE SCAN in ORACLE<\/span><\/span><\/h3>\n

During a full table scan the optimizer scan\/read all the blocks in a sequential manner and every row is examined whether its satisfy the where predicate in the query. The speed of the FTS depends upon the parameter .DB_FILE_MULTIBLOCK_READ_COUNT . Default its set to 1 but if we see that we are selecting the the whole data from the tables and we only need FTS we can increase it to 4 because the optimizer reads the data sequentially.Its not necessary that every time a FTS performed by the optimizer it\u2019s a bad thing but it depends upon the amount of data where doing a FTS cost less while using a Index cost is more. There are other factors where Optimizer do\u00a0 FTS like:<\/p>\n

    \n
  • Lack of index<\/li>\n
  • Small Table<\/li>\n
  • Stale Stats<\/li>\n
  • Selectivity and the predicate used in the where clause.<\/li>\n<\/ul>\n

    INDEX UNIQUE SCAN in Oracle<\/h3>\n

    Index unique scan used by the optimizer when the output of the query is utmost is one row. For unique scan to work the index should be created on primary or foreign key and the index column must be used in where clause or in other words we can say that primary key constraint must be specified with the equality condition.<\/p>\n

    INDEX RANGE SCAN in ORACLE<\/h3>\n

    Index range scan is used by the optimizer when the result consists of multiple rows. If the data we quried is bounded from one side or both side or the query consist of a group or ordered by Index scan can be used by the optimizer. The group by or order by clause should be used only on the indexing columns . When the optimizer uses \u00a0Index range scan it does not do any sorting because all the data is already available on the Index .<\/p>\n

    INDEX FULL SCAN in ORACLE<\/h3>\n

    Index full scan is used by the optimizer when it reads all the data from the index and then it reads the data from the table . Optimizer uses the Index full scan when group by or order by is used only with the indexed column and if query requires a sort merge join. Optimzer used Index full scan only when the order by predicates and index column should match sequentially. Just like Index unique scan full scan does not do any sorting .<\/p>\n

    INDEX FAST FULL SCAN in ORACLE<\/h3>\n

    If the query request only the columns existing in the index then the optimizer used the Fast full scan . Hints can be used to force the optimizer to choose the fast full scan . Index fast full scan reads multiple blocks simultaneously in unordered manner so sorting will be needing whereas Index full scan reads data sequentially so no sorting needed for Full scan . Index fast full scan does not read data from the table .<\/p>\n

    INDEX SKIP SCAN in ORACLE<\/h3>\n

    When there are composite indexes and if any of the index except the first one is used in the where predicate then the Skip scan is used by the optimizer .<\/p>\n

    In the next post i will be explaining all these scans with the examples .<\/p>\n

    <\/div>\n

    <\/path><\/svg><\/i> \"Loading\"<\/p>\n

    <\/div>\n","protected":false},"excerpt":{"rendered":"

    The index scans are the table access paths used by the oracle optimizer to generate the best execution plan. There are different types of scans that are used by the optimizer, but it all depends upon the selectivity and the predicate we provide in our SQL queries. By far the best way to access \u00a0a …<\/p>\n

    <\/div>\n

    <\/path><\/svg><\/i> \"Loading\"<\/p>\n

    <\/div>\n","protected":false},"author":481,"featured_media":15436,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"footnotes":""},"categories":[4],"tags":[9390,9391,9395,9394,9393,188,9396,9392,1498,6011],"class_list":["post-15430","post","type-post","status-publish","format-standard","has-post-thumbnail","","category-oracle","tag-access-paths-in-oracle","tag-full-table-scan-in-oracle","tag-index-fast-full-scan-in-oracle","tag-index-full-scan-in-oracle","tag-index-range-scan-in-oracle","tag-index-scan","tag-index-skip-scan-in-oracle","tag-index-unique-scan-in-oracle","tag-oracle","tag-query-optimizer-fixes"],"aioseo_notices":[],"a3_pvc":{"activated":true,"total_views":3687,"today_views":1},"yoast_head":"\nINDEX SCANS IN ORACLE - Database Tutorials<\/title>\n<meta name=\"description\" content=\"INDEX SCANS IN ORACLE\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/dbtut.com\/index.php\/2020\/03\/26\/index-scans-in-oracle\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"INDEX SCANS IN ORACLE - Database Tutorials\" \/>\n<meta property=\"og:description\" content=\"INDEX SCANS IN ORACLE\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbtut.com\/index.php\/2020\/03\/26\/index-scans-in-oracle\/\" \/>\n<meta property=\"og:site_name\" content=\"Database Tutorials\" \/>\n<meta property=\"article:published_time\" content=\"2020-03-26T08:02:07+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-03-27T08:42:06+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/dbtut.com\/wp-content\/uploads\/2020\/03\/Ads\u0131z-24.png\" \/>\n\t<meta property=\"og:image:width\" content=\"823\" \/>\n\t<meta property=\"og:image:height\" content=\"527\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Amit Vashishth\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Amit Vashishth\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2020\/03\/26\/index-scans-in-oracle\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2020\/03\/26\/index-scans-in-oracle\/\"},\"author\":{\"name\":\"Amit Vashishth\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/498d05a2429aa44b0ee5e0707bfb8315\"},\"headline\":\"INDEX SCANS IN ORACLE\",\"datePublished\":\"2020-03-26T08:02:07+00:00\",\"dateModified\":\"2020-03-27T08:42:06+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2020\/03\/26\/index-scans-in-oracle\/\"},\"wordCount\":651,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2020\/03\/26\/index-scans-in-oracle\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2020\/03\/Ads\u0131z-24.png\",\"keywords\":[\"Access paths in oracle\",\"FULL TABLE SCAN in ORACLE\",\"INDEX FAST FULL SCAN in ORACLE\",\"INDEX FULL SCAN in ORACLE\",\"INDEX RANGE SCAN in Oracle\",\"index scan\",\"INDEX SKIP SCAN in ORACLE\",\"INDEX UNIQUE SCAN in Oracle\",\"oracle\",\"Query Optimizer Fixes\"],\"articleSection\":[\"ORACLE\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2020\/03\/26\/index-scans-in-oracle\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2020\/03\/26\/index-scans-in-oracle\/\",\"url\":\"https:\/\/dbtut.com\/index.php\/2020\/03\/26\/index-scans-in-oracle\/\",\"name\":\"INDEX SCANS IN ORACLE - Database Tutorials\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2020\/03\/26\/index-scans-in-oracle\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2020\/03\/26\/index-scans-in-oracle\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2020\/03\/Ads\u0131z-24.png\",\"datePublished\":\"2020-03-26T08:02:07+00:00\",\"dateModified\":\"2020-03-27T08:42:06+00:00\",\"description\":\"INDEX SCANS IN ORACLE\",\"breadcrumb\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2020\/03\/26\/index-scans-in-oracle\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2020\/03\/26\/index-scans-in-oracle\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2020\/03\/26\/index-scans-in-oracle\/#primaryimage\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2020\/03\/Ads\u0131z-24.png\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2020\/03\/Ads\u0131z-24.png\",\"width\":823,\"height\":527},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2020\/03\/26\/index-scans-in-oracle\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/dbtut.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"INDEX SCANS IN ORACLE\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/dbtut.com\/#website\",\"url\":\"https:\/\/dbtut.com\/\",\"name\":\"Database Tutorials\",\"description\":\"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux\",\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/dbtut.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/dbtut.com\/#organization\",\"name\":\"dbtut\",\"url\":\"https:\/\/dbtut.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"width\":223,\"height\":36,\"caption\":\"dbtut\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/498d05a2429aa44b0ee5e0707bfb8315\",\"name\":\"Amit Vashishth\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/1151b8ba4366b28f37b7864d09bc41e952f55c1e541e29bc6b499dc55f8a502d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/1151b8ba4366b28f37b7864d09bc41e952f55c1e541e29bc6b499dc55f8a502d?s=96&d=mm&r=g\",\"caption\":\"Amit Vashishth\"},\"url\":\"https:\/\/dbtut.com\/index.php\/author\/amitvashishth\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"INDEX SCANS IN ORACLE - Database Tutorials","description":"INDEX SCANS IN ORACLE","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/dbtut.com\/index.php\/2020\/03\/26\/index-scans-in-oracle\/","og_locale":"en_US","og_type":"article","og_title":"INDEX SCANS IN ORACLE - Database Tutorials","og_description":"INDEX SCANS IN ORACLE","og_url":"https:\/\/dbtut.com\/index.php\/2020\/03\/26\/index-scans-in-oracle\/","og_site_name":"Database Tutorials","article_published_time":"2020-03-26T08:02:07+00:00","article_modified_time":"2020-03-27T08:42:06+00:00","og_image":[{"width":823,"height":527,"url":"https:\/\/dbtut.com\/wp-content\/uploads\/2020\/03\/Ads\u0131z-24.png","type":"image\/png"}],"author":"Amit Vashishth","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Amit Vashishth","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/dbtut.com\/index.php\/2020\/03\/26\/index-scans-in-oracle\/#article","isPartOf":{"@id":"https:\/\/dbtut.com\/index.php\/2020\/03\/26\/index-scans-in-oracle\/"},"author":{"name":"Amit Vashishth","@id":"https:\/\/dbtut.com\/#\/schema\/person\/498d05a2429aa44b0ee5e0707bfb8315"},"headline":"INDEX SCANS IN ORACLE","datePublished":"2020-03-26T08:02:07+00:00","dateModified":"2020-03-27T08:42:06+00:00","mainEntityOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2020\/03\/26\/index-scans-in-oracle\/"},"wordCount":651,"commentCount":0,"publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2020\/03\/26\/index-scans-in-oracle\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2020\/03\/Ads\u0131z-24.png","keywords":["Access paths in oracle","FULL TABLE SCAN in ORACLE","INDEX FAST FULL SCAN in ORACLE","INDEX FULL SCAN in ORACLE","INDEX RANGE SCAN in Oracle","index scan","INDEX SKIP SCAN in ORACLE","INDEX UNIQUE SCAN in Oracle","oracle","Query Optimizer Fixes"],"articleSection":["ORACLE"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/dbtut.com\/index.php\/2020\/03\/26\/index-scans-in-oracle\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/dbtut.com\/index.php\/2020\/03\/26\/index-scans-in-oracle\/","url":"https:\/\/dbtut.com\/index.php\/2020\/03\/26\/index-scans-in-oracle\/","name":"INDEX SCANS IN ORACLE - Database Tutorials","isPartOf":{"@id":"https:\/\/dbtut.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2020\/03\/26\/index-scans-in-oracle\/#primaryimage"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2020\/03\/26\/index-scans-in-oracle\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2020\/03\/Ads\u0131z-24.png","datePublished":"2020-03-26T08:02:07+00:00","dateModified":"2020-03-27T08:42:06+00:00","description":"INDEX SCANS IN ORACLE","breadcrumb":{"@id":"https:\/\/dbtut.com\/index.php\/2020\/03\/26\/index-scans-in-oracle\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbtut.com\/index.php\/2020\/03\/26\/index-scans-in-oracle\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/index.php\/2020\/03\/26\/index-scans-in-oracle\/#primaryimage","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2020\/03\/Ads\u0131z-24.png","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2020\/03\/Ads\u0131z-24.png","width":823,"height":527},{"@type":"BreadcrumbList","@id":"https:\/\/dbtut.com\/index.php\/2020\/03\/26\/index-scans-in-oracle\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbtut.com\/"},{"@type":"ListItem","position":2,"name":"INDEX SCANS IN ORACLE"}]},{"@type":"WebSite","@id":"https:\/\/dbtut.com\/#website","url":"https:\/\/dbtut.com\/","name":"Database Tutorials","description":"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux","publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/dbtut.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/dbtut.com\/#organization","name":"dbtut","url":"https:\/\/dbtut.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","width":223,"height":36,"caption":"dbtut"},"image":{"@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/dbtut.com\/#\/schema\/person\/498d05a2429aa44b0ee5e0707bfb8315","name":"Amit Vashishth","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/1151b8ba4366b28f37b7864d09bc41e952f55c1e541e29bc6b499dc55f8a502d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/1151b8ba4366b28f37b7864d09bc41e952f55c1e541e29bc6b499dc55f8a502d?s=96&d=mm&r=g","caption":"Amit Vashishth"},"url":"https:\/\/dbtut.com\/index.php\/author\/amitvashishth\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/15430","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/users\/481"}],"replies":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/comments?post=15430"}],"version-history":[{"count":0,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/15430\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media\/15436"}],"wp:attachment":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media?parent=15430"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/categories?post=15430"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/tags?post=15430"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}