{"id":11082,"date":"2018-04-13T21:01:17","date_gmt":"2018-04-13T19:01:17","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/covering-indexes-in-oracle-and-branch-size\/"},"modified":"2018-04-13T21:01:17","modified_gmt":"2018-04-13T19:01:17","slug":"covering-indexes-in-oracle-and-branch-size","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/covering-indexes-in-oracle-and-branch-size\/","title":{"rendered":"Covering indexes in Oracle, and branch size"},"content":{"rendered":"<h2>By Franck Pachot<\/h2>\n<p>.<br \/>\nA covering index is an index that contains all the columns required by your query, so that you don&#8217;t have to do a TABLE ACCESS BY INDEX ROWID, which is the major cost of an index range scan. You don&#8217;t need any special feature to do that in Oracle. Just add the required columns at the end of the index. In the execution plan you will see the columns used as index keys for the range scan displayed in &#8216;access&#8217; predicates, and the further filtering done on the remaining columns with &#8216;filter&#8217; predicates. The &#8216;projection&#8217; shows the columns that are returned in the rowset result.<br \/>\nHowever you may have seen that SQL Server has a special &#8216;INCLUDE&#8217; keyword to separate those non-key columns added only for filtering or projection but not for access. What does it bring that Oracle doesn&#8217;t have?<br \/>\n<!--more--><br \/>\nAn index entry is composed of a key and data associated to the key. The index is sorted on the key. The data for each key have no special order, like in a heap table. The idea of the SQL Server INCLUDE keyword is to separate the columns belonging to the key and the columns belonging to the data. It is not mandatory. You can add all columns to the key but depending on the implementation, the benefit can be:<\/p>\n<ul>\n<li>some data types may not be allowed in the key but allowed as data<\/li>\n<li>sorting the data when not required may be a performance overhead<\/li>\n<li>there can be limitations on the size of the key<\/li>\n<li>having a larger key may require more space in the branches<\/li>\n<li>adding sorted columns may change the clustering factor<\/li>\n<\/ul>\n<p>In Oracle, there are very few data types that cannot be indexed (like LONG). The limitation on the size of the key may come into play for large 12c Extended Datatypes. You can substring them, but that defeats the goal of covering indexes. I see two reasons why &#8216;INCLUDE&#8217; indexes can be useful. The first reason is about the clustering factor. The second about sorting the whole index entry and referencing it from the branches. I&#8217;ll detail those reasons later, but first here is an example.<\/p>\n<pre><code>\nSQL&gt; create table DEMO (UNIQU ,RANGE ,RANDOM_TEXT ,CONSTANT_TEXT ) as select rownum UNIQU , mod(rownum,4) RANGE , dbms_random.string('u',80) RANDOM_TEXT , lpad('x',80,'x') CONSTANT_TEXT from xmltable('1 to 100000');\nTable DEMO created.\nSQL&gt; commit;\nCommit complete.\n<\/code><\/pre>\n<p>This table has an all-distinct-values column UNIQ, a few-distinct-values on (RANGE) and I&#8217;ll use them for the key. And I&#8217;ve two columns I&#8217;ll add as additional column for covering queries: one is with lot of distinct values (RANDOM_TEXT) and the other has few distinct values (CONSTANT_TEXT).<br \/>\nThe first rows look like this:<\/p>\n<pre><code>\nSQL&gt; select * from DEMO order by ROWID fetch first 5 rows only;\n  UNIQU   RANGE RANDOM_TEXT                                                                        CONSTANT_TEXT\n  -----   ----- --------------------------------------------------------------------------------   --------------------------------------------------------------------------------\n      1       1 XCFNWCRCFBEPJPSHREUVVVTBUCCXLZMRPJPNQDTHWYRZRUORBPDOBCIRFHLICETULTCZTMPOCMUNQITV   xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx\n      2       2 XUSPNDOMPQKOIRCVDDTVYAGKRDGIXOSVUNMRAQLSRQGYKOFEXRQMCPXPYZYKRHHKDXGIINOUUAUJOLOO   xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx\n      3       3 ZBCVFTDSRUFIUTSIWOOOBWIRMEFUXNWLADAPUPFNPVYDLPQTOUZVXJKMGIPCGZESXFXOIYVMKNSMMZKB   xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx\n      4       0 VOIRCXFVSRVZQRZDRLQRHZWNGQJAAWJXWXJKRCJVPWYDJSZLJIOEWAMCFSRCUPSPPEKITJYHHOUQSVYQ   xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx\n      5       1 UUSAMEVRWNLPGCUVMJWVVPDAENRYKIWWMIHTUJSZRQASMTYOVQNCGZGZIJZWNSOJVSIBMMUEAXOHJCOA   xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx\n<\/code><\/pre>\n<p>I&#8217;m adding indexes fo access on RANGE as the index key, with only the key, or covering the random or constant text:<\/p>\n<pre><code>\nSQL&gt; create index DEMO_RANGE                   on DEMO(RANGE)               pctfree 50;\nIndex DEMO_RANGE created.\nSQL&gt; create index DEMO_RANGE_COVERING_RANDOM   on DEMO(RANGE,RANDOM_TEXT)   pctfree 50;\nIndex DEMO_RANGE_COVERING_RANDOM created.\nSQL&gt; create index DEMO_RANGE_COVERING_CONSTANT on DEMO(RANGE,CONSTANT_TEXT) pctfree 50;\nIndex DEMO_RANGE_COVERING_CONSTANT created.\n<\/code><\/pre>\n<p>An additional one adding the unique column in-between:<\/p>\n<pre><code>\nSQL&gt; create index DEMO_RANGE_COVERING_WITH_PK  on DEMO(RANGE,UNIQU,CONSTANT_TEXT) pctfree 50;\nIndex DEMO_RANGE_COVERING_WITH_PK created.\n<\/code><\/pre>\n<p>And now for access with the unique column as a key:<\/p>\n<pre><code>\nSQL&gt; create index DEMO_UNIQU_COVERING_RANDOM   on DEMO(UNIQU,RANDOM_TEXT)   pctfree 50;\nIndex DEMO_UNIQU_COVERING_RANDOM created.\nSQL&gt; create index DEMO_UNIQU_COVERING_CONSTANT on DEMO(UNIQU,CONSTANT_TEXT) pctfree 50;\nIndex DEMO_UNIQU_COVERING_CONSTANT created.\n<\/code><\/pre>\n<p>Here are some interesting stats:<\/p>\n<pre><code>\nSQL&gt; exec dbms_stats.gather_table_stats(user,'DEMO');\nPL\/SQL procedure successfully completed.\nSQL&gt; select index_name,blevel,leaf_blocks,num_rows,clustering_factor from user_indexes where table_name='DEMO' order by 2,3;\nINDEX_NAME                       BLEVEL   LEAF_BLOCKS   NUM_ROWS   CLUSTERING_FACTOR\n-------------------------------- ------   -----------   --------   -----------------\nDEMO_RANGE                            1           353     100000                9757\nDEMO_RANGE_COVERING_RANDOM            2          2440     100000               99967\nDEMO_RANGE_COVERING_CONSTANT          2          2440     100000                9757\nDEMO_UNIQU_COVERING_RANDOM            2          2500     100000                2440\nDEMO_UNIQU_COVERING_CONSTANT          2          2500     100000                2440\nDEMO_RANGE_COVERING_WITH_PK           2          2565     100000                9757\n6 rows selected.\n<\/code><\/pre>\n<h3>Leaf size<\/h3>\n<p>About the size, the covering indexes have approximately the same number of leaf blocks because the included column (RANDOM_TEXT or CONSTANT_TEXT) has the same size (80 bytes). Of course, the non-covering index is smaller (but will need table access to query additional column). The key on UNIQU is slightly larger than the one on RANGE because the numbers go higher. The index with 3 columns is the largest.<\/p>\n<h3>Clustering factor<\/h3>\n<p>About the clustering factor, there&#8217;s one outlier here which deserves an explanation. But before that, you must understand that this higher clustering factor is not important for a query using the covering index, such as a SELECT RANDOM_TEXT WHERE RANGE=0, because in that case you don&#8217;t read the table. However for some queries you may cover only the filter predicates and go to the table for projection.<br \/>\nBut the big problem is that when you add a column to an index to address a specific query, you don&#8217;t want to risk a side effect on another query, and changing the clustering factor is a risk here. One solution is to keep the old non-covering index (DEMO_RANGE) but then the side effect is on DML overhead.<\/p>\n<p>To understand the change in clustering factor we must go deeper on Oracle index key and data implementation. The &#8216;data&#8217; part exists in Oracle indexes even when not specified explicitely with an INCLUDE clause. The ROWID is the data part. An index entry associates a key (the indexed columns) with a pointer to the table row (the ROWID). At least, this is for UNIQUE indexes where each key is unique.<\/p>\n<p>Non-unique indexes are a special case. Actually, Oracle implements only unique key indexes. When the indexed columns are not unique, the ROWID is stored on the key part of the index entry, and there is no data part. You should read <a href=\"https:\/\/richardfoote.wordpress.com\/2007\/12\/18\/differences-between-unique-and-non-unique-indexes-part-i\/\" target=\"_blank\" rel=\"noopener noreferrer\">Richard Foote, Differences between Unique and Non-Unique Indexes<\/a> for detailed explanation.<\/p>\n<h3>Branch size<\/h3>\n<p>The previous statistics displayed only the number of branch level, which was the same, but we can have more detail about the branch size with an ANALYZE INDEX.<\/p>\n<p>The non-covering index has only one branch block, the root, which references all the 353 leaf blocks containing the 100000 entries, with an average of 5479\/352=15 bytes per branch entry:<\/p>\n<pre><code>\nSQL&gt; analyze index DEMO_RANGE validate structure offline;\nIndex DEMO_RANGE analyzed.\nSQL&gt; select  height,blocks,lf_blks,lf_rows_len,br_rows,br_blks,br_rows_len,most_repeated_key,btree_space,used_space,pct_used,rows_per_key,blks_gets_per_access,opt_cmpr_pctsave,lf_uncmp_rows_len,lf_uncmp_blks from index_stats\n  HEIGHT   BLOCKS   LF_BLKS   LF_ROWS_LEN   BR_ROWS   BR_BLKS   BR_ROWS_LEN   MOST_REPEATED_KEY   BTREE_SPACE   USED_SPACE   PCT_USED   ROWS_PER_KEY   BLKS_GETS_PER_ACCESS   OPT_CMPR_PCTSAVE   LF_UNCMP_ROWS_LEN   LF_UNCMP_BLKS\n  ------   ------   -------   -----------   -------   -------   -----------   -----------------   -----------   ----------   --------   ------------   --------------------   ----------------   -----------------   -------------\n       2      384       353       1375000       352         1          5479               25000       2830616      1380479         49          25000                12502.5                 19             1375000             353\n<\/code><\/pre>\n<p>The covering index with lot of distinct values for the non-key columns has more branch blocks, with an average of 34623\/2439=14 bytes per branch entry:<\/p>\n<pre><code>\nSQL&gt; analyze index DEMO_RANGE_COVERING_RANDOM validate structure offline;\nIndex DEMO_RANGE_COVERING_RANDOM analyzed.\nSQL&gt; select  height,blocks,lf_blks,lf_rows_len,br_rows,br_blks,br_rows_len,most_repeated_key,btree_space,used_space,pct_used,rows_per_key,blks_gets_per_access,opt_cmpr_pctsave,lf_uncmp_rows_len,lf_uncmp_blks from index_stats\n  HEIGHT   BLOCKS   LF_BLKS   LF_ROWS_LEN   BR_ROWS   BR_BLKS   BR_ROWS_LEN   MOST_REPEATED_KEY   BTREE_SPACE   USED_SPACE   PCT_USED   ROWS_PER_KEY   BLKS_GETS_PER_ACCESS   OPT_CMPR_PCTSAVE   LF_UNCMP_ROWS_LEN   LF_UNCMP_BLKS\n  ------   ------   -------   -----------   -------   -------   -----------   -----------------   -----------   ----------   --------   ------------   --------------------   ----------------   -----------------   -------------\n       3     2560      2440       9475000      2439         6         34623                   1      19558408      9509623         49              1                      4                  2             9475000            2440\n<\/code><\/pre>\n<p>Here the number of branches is higher only because there are more leaves (as we have more columns), but not because of the size in the branch entries, which are even smaller. They are smaller because the branch does not have to store the full value of all columns in order to identify one leaf block. Then, only the first bytes are needed and not the full 80 bytes of them.<\/p>\n<p>The covering index with few of distinct values for the non-key columns has a lot more branch blocks, with an average of 234755\/2439=96 bytes per branch entry:<\/p>\n<pre><code>\nSQL&gt; analyze index DEMO_RANGE_COVERING_CONSTANT validate structure offline;\nIndex DEMO_RANGE_COVERING_CONSTANT analyzed.\nSQL&gt; select  height,blocks,lf_blks,lf_rows_len,br_rows,br_blks,br_rows_len,most_repeated_key,btree_space,used_space,pct_used,rows_per_key,blks_gets_per_access,opt_cmpr_pctsave,lf_uncmp_rows_len,lf_uncmp_blks from index_stats\n&nbsp;\n  HEIGHT   BLOCKS   LF_BLKS   LF_ROWS_LEN   BR_ROWS   BR_BLKS   BR_ROWS_LEN   MOST_REPEATED_KEY   BTREE_SPACE   USED_SPACE   PCT_USED   ROWS_PER_KEY   BLKS_GETS_PER_ACCESS   OPT_CMPR_PCTSAVE   LF_UNCMP_ROWS_LEN   LF_UNCMP_BLKS\n  ------   ------   -------   -----------   -------   -------   -----------   -----------------   -----------   ----------   --------   ------------   --------------------   ----------------   -----------------   -------------\n       3     2560      2440       9475000      2439        31        234755               25000      19759108      9709755         50          25000                12503.5                 86             9475000            2440\n<\/code><\/pre>\n<p>So, here the size of the branch blocks is higher because we have multiple leaves blocks with the value of COVERING_CONSTANT the second column is not sufficient to identify only one leaf block. The full 80 bytes must be stored, and the rowid in addition to it.<\/p>\n<p>When the indexed column has only unique values, there is no need to store more in the branches (not the additional columns, not the rowid) and only 12 bytes are needed here on average:<\/p>\n<pre><code>\nSQL&gt; analyze index DEMO_UNIQU_COVERING_RANDOM validate structure offline;\nIndex DEMO_UNIQU_COVERING_RANDOM analyzed.\nSQL&gt; select  height,blocks,lf_blks,lf_rows_len,br_rows,br_blks,br_rows_len,most_repeated_key,btree_space,used_space,pct_used,rows_per_key,blks_gets_per_access,opt_cmpr_pctsave,lf_uncmp_rows_len,lf_uncmp_blks from index_stats\n&nbsp;\n  HEIGHT   BLOCKS   LF_BLKS   LF_ROWS_LEN   BR_ROWS   BR_BLKS   BR_ROWS_LEN   MOST_REPEATED_KEY   BTREE_SPACE   USED_SPACE   PCT_USED   ROWS_PER_KEY   BLKS_GETS_PER_ACCESS   OPT_CMPR_PCTSAVE   LF_UNCMP_ROWS_LEN   LF_UNCMP_BLKS\n  ------   ------   -------   -----------   -------   -------   -----------   -----------------   -----------   ----------   --------   ------------   --------------------   ----------------   -----------------   -------------\n       3     2560      2500       9688892      2499         5         29737                   1      20030140      9718629         49              1                      4                  0             9688892            2500\n<\/code><\/pre>\n<p>As the second column is not needed, the size of branch is the same whether we use RANDOM_TEXT or CONSTANT_TEXT:<\/p>\n<pre><code>\nSQL&gt; analyze index DEMO_UNIQU_COVERING_CONSTANT validate structure offline;\nIndex DEMO_UNIQU_COVERING_CONSTANT analyzed.\nSQL&gt; select  height,blocks,lf_blks,lf_rows_len,br_rows,br_blks,br_rows_len,most_repeated_key,btree_space,used_space,pct_used,rows_per_key,blks_gets_per_access,opt_cmpr_pctsave,lf_uncmp_rows_len,lf_uncmp_blks from index_stats\n&nbsp;\n  HEIGHT   BLOCKS   LF_BLKS   LF_ROWS_LEN   BR_ROWS   BR_BLKS   BR_ROWS_LEN   MOST_REPEATED_KEY   BTREE_SPACE   USED_SPACE   PCT_USED   ROWS_PER_KEY   BLKS_GETS_PER_ACCESS   OPT_CMPR_PCTSAVE   LF_UNCMP_ROWS_LEN   LF_UNCMP_BLKS\n  ------   ------   -------   -----------   -------   -------   -----------   -----------------   -----------   ----------   --------   ------------   --------------------   ----------------   -----------------   -------------\n       3     2560      2500       9688892      2499         5         29737                   1      20030140      9718629         49              1                      4                  0             9688892            2500\n<\/code><\/pre>\n<p>Now, the last one is my workaround for the higher size when adding a column that do not have a lot of distinct values: just add a column before with more distinct values. Here I use the UNIQU one, but you probably have one that can be useful for your queries.<\/p>\n<pre><code>\nSQL&gt; analyze index DEMO_RANGE_COVERING_WITH_PK validate structure offline;\nIndex DEMO_RANGE_COVERING_WITH_PK analyzed.\nSQL&gt; select  height,blocks,lf_blks,lf_rows_len,br_rows,br_blks,br_rows_len,most_repeated_key,btree_space,used_space,pct_used,rows_per_key,blks_gets_per_access,opt_cmpr_pctsave,lf_uncmp_rows_len,lf_uncmp_blks from index_stats\n&nbsp;\n  HEIGHT   BLOCKS   LF_BLKS   LF_ROWS_LEN   BR_ROWS   BR_BLKS   BR_ROWS_LEN   MOST_REPEATED_KEY   BTREE_SPACE   USED_SPACE   PCT_USED   ROWS_PER_KEY   BLKS_GETS_PER_ACCESS   OPT_CMPR_PCTSAVE   LF_UNCMP_ROWS_LEN   LF_UNCMP_BLKS\n  ------   ------   -------   -----------   -------   -------   -----------   -----------------   -----------   ----------   --------   ------------   --------------------   ----------------   -----------------   -------------\n       3     2688      2565       9963892      2564         6         37456                   1      20557908     10001348         49              1                      4                  2             9963892            2565\n<\/code><\/pre>\n<p>Now you get the idea. When creating an index, or adding columns for covering index, and you have the choice of column order, then try to have their first bytes selective enough so that the branch needs only a small substring to identify each leaf block (or lower level branches).<\/p>\n<h3>Block dumps<\/h3>\n<p>If you want to see the details about the branch length, here are some info from block dumps. I got them with the following:<\/p>\n<pre><code>\nSQL&gt; column value new_value tracefile\nSQL&gt; select value from v$diag_info where name='Default Trace File';\nVALUE\n\/u01\/app\/oracle\/diag\/rdbms\/cdb1\/CDB1\/trace\/CDB1_ora_6799.trc\nSQL&gt; exec for i in (select header_file, header_block from dba_segments where owner='DEMO' and segment_name='DEMO_RANGE') loop execute immediate 'alter system dump datafile '||i.header_file||' block '||(i.header_block+1); end loop;\nPL\/SQL procedure successfully completed.\nSQL&gt; host tail -20  &amp;tracefile\n<\/code><\/pre>\n<p>Here is the last branch entry for the root block of DEMO_RANGE where the first column is not very selective and then the rowid is required in the branch:<\/p>\n<pre><code>\nrow#351[3279] dba: 113261807=0x6c03cef\ncol 0; len 2; (2):  c1 04\ncol 1; len 6; (6):  07 00 05 7b 00 25\n<\/code><\/pre>\n<p>Here is the last branch entry for the root block of DEMO_RANGE_COVERING_RANDOM where instead of the rowid the 3 first bytes of the RANDOM_TEXT column are sufficient:<\/p>\n<pre><code>\nrow#3[8006] dba: 113263037=0x6c041bd\ncol 0; len 2; (2):  c1 04\ncol 1; len 3; (3):  53 51 52\ncol 2; TERM\n<\/code><\/pre>\n<p>Here is the last branch entry for the root block of DEMO_RANGE_COVERING_CONSTANT where the full 80 bytes of CONSTANT_TEXT are not even sufficient, and the ROWID is needed as a 3rd column:<\/p>\n<pre><code>\nrow#28[5316] dba: 117444566=0x7000fd6\ncol 0; len 2; (2):  c1 04\ncol 1; len 80; (80):\n 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78\n 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78\n 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78\n 78 78 78 78 78\ncol 2; len 6; (6):  07 00 05 43 00 25\n<\/code><\/pre>\n<p>Here is the last branch entry for the root block of DEMO_UNIQU_COVERING_CONSTANT where the first column is sufficient:<\/p>\n<pre><code>\nrow#2[8026] dba: 117447160=0x70019f8\ncol 0; len 4; (4):  c3 09 0d 04\ncol 1; TERM\n<\/code><\/pre>\n<h3>So what?<\/h3>\n<p>We probably don&#8217;t need a feature like SQL Server INCLUDE indexes in most of the cases. However, this may require thinking about the order of columns, mainly:<\/p>\n<ul>\n<li>ensure that selective columns appear as early as possible (without compromising the index access efficiency of course) in order to lower the bytes required to address branches and leaves<\/li>\n<li>when adding columns, try to add first a column that will keep the clustering factor you had with the rowid, such as a date of insert<\/li>\n<\/ul>\n<h3>Added 14-APR-2018<\/h3>\n<p>The conclusion above was only focused at columns added for covering indexes (I wrote it after reading wrong things in <a href=\"https:\/\/stackoverflow.com\/questions\/5108651\/include-equivalent-in-oracle\" target=\"_blank\" rel=\"noopener noreferrer\">this stackoverflow thread<\/a>), and it is not a general statement about putting selective columns first, which is a common misconception. Columns like this CONSTANT_TEXT (which is an extreme case of non-selective) can have a better index compression (Enterprise Edition feature) when in front. Read this tread, answers and links from Richard Foote: <a href=\"https:\/\/twitter.com\/OracleSK\/status\/984906294879539200\" target=\"_blank\" rel=\"noopener noreferrer\">https:\/\/twitter.com\/OracleSK\/status\/984906294879539200<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>By Franck Pachot . A covering index is an index that contains all the columns required by your query, so that you don&#8217;t have to do a TABLE ACCESS BY INDEX ROWID, which is the major cost of an index range scan. You don&#8217;t need any special feature to do that in Oracle. Just add [&hellip;]<\/p>\n","protected":false},"author":27,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[59],"tags":[1341,1342,91,96],"type_dbi":[],"class_list":["post-11082","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-covering","tag-include","tag-index","tag-oracle"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.4) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>Covering indexes in Oracle, and branch size - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/covering-indexes-in-oracle-and-branch-size\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Covering indexes in Oracle, and branch size\" \/>\n<meta property=\"og:description\" content=\"By Franck Pachot . A covering index is an index that contains all the columns required by your query, so that you don&#8217;t have to do a TABLE ACCESS BY INDEX ROWID, which is the major cost of an index range scan. You don&#8217;t need any special feature to do that in Oracle. Just add [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/covering-indexes-in-oracle-and-branch-size\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2018-04-13T19:01:17+00:00\" \/>\n<meta name=\"author\" content=\"Oracle Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Oracle Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"13 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/covering-indexes-in-oracle-and-branch-size\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/covering-indexes-in-oracle-and-branch-size\\\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"Covering indexes in Oracle, and branch size\",\"datePublished\":\"2018-04-13T19:01:17+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/covering-indexes-in-oracle-and-branch-size\\\/\"},\"wordCount\":1490,\"commentCount\":0,\"keywords\":[\"covering\",\"include\",\"index\",\"Oracle\"],\"articleSection\":[\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/covering-indexes-in-oracle-and-branch-size\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/covering-indexes-in-oracle-and-branch-size\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/covering-indexes-in-oracle-and-branch-size\\\/\",\"name\":\"Covering indexes in Oracle, and branch size - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2018-04-13T19:01:17+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/covering-indexes-in-oracle-and-branch-size\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/covering-indexes-in-oracle-and-branch-size\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/covering-indexes-in-oracle-and-branch-size\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Covering indexes in Oracle, and branch size\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/66ab87129f2d357f09971bc7936a77ee\",\"name\":\"Oracle Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"caption\":\"Oracle Team\"},\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/oracle-team\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Covering indexes in Oracle, and branch size - dbi Blog","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:\/\/www.dbi-services.com\/blog\/covering-indexes-in-oracle-and-branch-size\/","og_locale":"en_US","og_type":"article","og_title":"Covering indexes in Oracle, and branch size","og_description":"By Franck Pachot . A covering index is an index that contains all the columns required by your query, so that you don&#8217;t have to do a TABLE ACCESS BY INDEX ROWID, which is the major cost of an index range scan. You don&#8217;t need any special feature to do that in Oracle. Just add [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/covering-indexes-in-oracle-and-branch-size\/","og_site_name":"dbi Blog","article_published_time":"2018-04-13T19:01:17+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"13 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/covering-indexes-in-oracle-and-branch-size\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/covering-indexes-in-oracle-and-branch-size\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"Covering indexes in Oracle, and branch size","datePublished":"2018-04-13T19:01:17+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/covering-indexes-in-oracle-and-branch-size\/"},"wordCount":1490,"commentCount":0,"keywords":["covering","include","index","Oracle"],"articleSection":["Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/covering-indexes-in-oracle-and-branch-size\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/covering-indexes-in-oracle-and-branch-size\/","url":"https:\/\/www.dbi-services.com\/blog\/covering-indexes-in-oracle-and-branch-size\/","name":"Covering indexes in Oracle, and branch size - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2018-04-13T19:01:17+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/covering-indexes-in-oracle-and-branch-size\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/covering-indexes-in-oracle-and-branch-size\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/covering-indexes-in-oracle-and-branch-size\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Covering indexes in Oracle, and branch size"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee","name":"Oracle Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","caption":"Oracle Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/11082","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/27"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=11082"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/11082\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=11082"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=11082"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=11082"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=11082"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}