{"id":2182,"date":"2011-03-03T02:59:00","date_gmt":"2011-03-03T01:59:00","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/oracle-index-compression-eliminating-duplicate-column-values\/"},"modified":"2011-03-03T02:59:00","modified_gmt":"2011-03-03T01:59:00","slug":"oracle-index-compression-eliminating-duplicate-column-values","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/oracle-index-compression-eliminating-duplicate-column-values\/","title":{"rendered":"Oracle index compression: eliminating duplicate column values"},"content":{"rendered":"<p>Index compression\u00a0comes from Oracle 8i and has quite the same mechanism as table compression. Index compression eliminates duplicate column values in the leaf blocks. Let&#8217;s see how it works:<\/p>\n<p>We start by creating an index on an uncompressed table:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SQL&gt; create index nocomp_i1 on nocomp (object_name,object_id);\nIndex created.<\/pre>\n<p>Let&#8217;s have a look at how many leaf blocks the newly created index has:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SQL&gt; select leaf_blocks from user_indexes where index_name = 'NOCOMP_I1';\u00a0\nLEAF_BLOCKS\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 407<\/pre>\n<p>If you want to estimate the ideal compression factor and the percentage of leaf blocks that can be saved, you need to consult INDEX_STATS view after validating the newly created index (watch out, this will lock the index):<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SQL&gt; validate index nocomp_i1;\u00a0\nIndex analyzed.\u00a0\n\u00a0\nSQL&gt; select opt_cmpr_count,opt_cmpr_pctsave from index_stats;\u00a0\nOPT_CMPR_COUNT OPT_CMPR_PCTSAVE\n-------------- ----------------\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 15<\/pre>\n<p>&#8220;opt_cmpr_count&#8221; = number of columns to compress in the index to get maximum space savings in the leaf blocks.<br \/>\n&#8220;opt_cmpr_pctsave&#8221; = percentage reduction in leaf block space used if we apply this compression count.<\/p>\n<p>The previous result shows that the ideal compression factor is 1 and will reduce the number of leaf blocks by 15 %.<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SQL&gt; alter index nocomp_i1 rebuild compress 1;\u00a0\nIndex altered.\nSQL&gt; select leaf_blocks from user_indexes where index_name = 'NOCOMP_I1';\u00a0\nLEAF_BLOCKS\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 345<\/pre>\n<p>The result is correct : 407 * 15 % = 61 and 407-61 = 346!<br \/>\nIf we now validate the index again, we can verify that the compression factor of 1 is fine and no leaf blocks can be eliminated anymore:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SQL&gt; validate index nocomp_i1;\u00a0 Index analyzed.\nSQL&gt; select opt_cmpr_count,opt_cmpr_pctsave from index_stats;\u00a0\nOPT_CMPR_COUNT OPT_CMPR_PCTSAVE\n-------------- ----------------\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a01\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Index compression&nbsp;comes from Oracle 8i and has quite the same  mechanism as table compression. Index compression eliminates duplicate column values in the leaf blocks. Let&#8217;s see how it works:<\/p>\n","protected":false},"author":27,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"neve_meta_sidebar":"","neve_meta_container":"","neve_meta_enable_content_width":"","neve_meta_content_width":0,"neve_meta_title_alignment":"","neve_meta_author_avatar":"","neve_post_elements_order":"","neve_meta_disable_header":"","neve_meta_disable_footer":"","neve_meta_disable_title":"","neve_meta_reading_time":"","_themeisle_gutenberg_block_has_review":false,"footnotes":""},"categories":[4134],"tags":[33,17],"class_list":["post-2182","post","type-post","status-publish","format-standard","hentry","category-application-server","tag-oracle-10g-to-8i","tag-oracle-11g"],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v28.4 (Yoast SEO v28.7) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>Oracle index compression: eliminating duplicate column values - dbi services | Blog<\/title>\n<meta name=\"description\" content=\"Index compression comes from Oracle 8i and has quite the same mechanism as table compression. Index compression eliminates duplicate column values in the leaf blocks.\" \/>\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\/oracle-index-compression-eliminating-duplicate-column-values\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Oracle index compression: eliminating duplicate column values\" \/>\n<meta property=\"og:description\" content=\"Index compression comes from Oracle 8i and has quite the same mechanism as table compression. Index compression eliminates duplicate column values in the leaf blocks.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/oracle-index-compression-eliminating-duplicate-column-values\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi services | Blog\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/dbiservices\/\" \/>\n<meta property=\"article:published_time\" content=\"2011-03-03T01:59:00+00:00\" \/>\n<meta name=\"author\" content=\"Oracle Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@dbiservices\" \/>\n<meta name=\"twitter:site\" content=\"@dbiservices\" \/>\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=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-index-compression-eliminating-duplicate-column-values\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-index-compression-eliminating-duplicate-column-values\\\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/bf7c7eb0bec165183207237c9d0ee1d5\"},\"headline\":\"Oracle index compression: eliminating duplicate column values\",\"datePublished\":\"2011-03-03T01:59:00+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-index-compression-eliminating-duplicate-column-values\\\/\"},\"wordCount\":188,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#organization\"},\"keywords\":[\"Oracle 10g to 8i\",\"Oracle 11g\"],\"articleSection\":[\"Application Server\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-index-compression-eliminating-duplicate-column-values\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-index-compression-eliminating-duplicate-column-values\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-index-compression-eliminating-duplicate-column-values\\\/\",\"name\":\"Oracle index compression: eliminating duplicate column values - dbi services | Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2011-03-03T01:59:00+00:00\",\"description\":\"Index compression comes from Oracle 8i and has quite the same mechanism as table compression. Index compression eliminates duplicate column values in the leaf blocks.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-index-compression-eliminating-duplicate-column-values\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-index-compression-eliminating-duplicate-column-values\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-index-compression-eliminating-duplicate-column-values\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Oracle index compression: eliminating duplicate column values\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi services Blog\",\"description\":\"#BeSharing\",\"publisher\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#organization\"},\"alternateName\":\"dbi Blog\",\"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\":\"Organization\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#organization\",\"name\":\"dbi services\",\"alternateName\":\"dbi\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2026\\\/09\\\/dbi-services-logo.png\",\"contentUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2026\\\/09\\\/dbi-services-logo.png\",\"width\":512,\"height\":512,\"caption\":\"dbi services\"},\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/logo\\\/image\\\/\"},\"sameAs\":[\"https:\\\/\\\/www.facebook.com\\\/dbiservices\\\/\",\"https:\\\/\\\/x.com\\\/dbiservices\",\"https:\\\/\\\/www.linkedin.com\\\/company\\\/dbi-services\\\/\",\"https:\\\/\\\/www.youtube.com\\\/user\\\/dbiservices\"],\"description\":\"Swiss IT consulting and services company specialised in efficient data infrastructures and platforms: databases, application servers, cloud, DevOps, digitalization, analytics, data quality and monitoring. Headquartered in Del\u00e9mont with offices in Nyon, Basel, Bern and Zurich.\",\"email\":\"info@dbi-services.com\",\"telephone\":\"+41 32 422 96 00\",\"legalName\":\"dbi services SA\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/bf7c7eb0bec165183207237c9d0ee1d5\",\"name\":\"Oracle Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/12\\\/oracle-square.png\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/12\\\/oracle-square.png\",\"contentUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/12\\\/oracle-square.png\",\"caption\":\"Oracle Team\"},\"description\":\"Posts published under the Oracle Team account are collective contributions by the dbi services Oracle consultants. The practice covers the full Oracle Database lifecycle: architecture, migration and upgrade, high availability with RAC and Data Guard, backup and recovery, performance tuning, and engineered systems such as Oracle Database Appliance and Exadata. The team holds Oracle certifications up to Certified Master level and operates customer platforms under service level agreements.\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/oracle-team\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Oracle index compression: eliminating duplicate column values - dbi services | Blog","description":"Index compression comes from Oracle 8i and has quite the same mechanism as table compression. Index compression eliminates duplicate column values in the leaf blocks.","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\/oracle-index-compression-eliminating-duplicate-column-values\/","og_locale":"en_US","og_type":"article","og_title":"Oracle index compression: eliminating duplicate column values","og_description":"Index compression comes from Oracle 8i and has quite the same mechanism as table compression. Index compression eliminates duplicate column values in the leaf blocks.","og_url":"https:\/\/www.dbi-services.com\/blog\/oracle-index-compression-eliminating-duplicate-column-values\/","og_site_name":"dbi services | Blog","article_publisher":"https:\/\/www.facebook.com\/dbiservices\/","article_published_time":"2011-03-03T01:59:00+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_creator":"@dbiservices","twitter_site":"@dbiservices","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-index-compression-eliminating-duplicate-column-values\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-index-compression-eliminating-duplicate-column-values\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bf7c7eb0bec165183207237c9d0ee1d5"},"headline":"Oracle index compression: eliminating duplicate column values","datePublished":"2011-03-03T01:59:00+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-index-compression-eliminating-duplicate-column-values\/"},"wordCount":188,"commentCount":0,"publisher":{"@id":"https:\/\/www.dbi-services.com\/blog\/#organization"},"keywords":["Oracle 10g to 8i","Oracle 11g"],"articleSection":["Application Server"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/oracle-index-compression-eliminating-duplicate-column-values\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-index-compression-eliminating-duplicate-column-values\/","url":"https:\/\/www.dbi-services.com\/blog\/oracle-index-compression-eliminating-duplicate-column-values\/","name":"Oracle index compression: eliminating duplicate column values - dbi services | Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2011-03-03T01:59:00+00:00","description":"Index compression comes from Oracle 8i and has quite the same mechanism as table compression. Index compression eliminates duplicate column values in the leaf blocks.","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-index-compression-eliminating-duplicate-column-values\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/oracle-index-compression-eliminating-duplicate-column-values\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-index-compression-eliminating-duplicate-column-values\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Oracle index compression: eliminating duplicate column values"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi services Blog","description":"#BeSharing","publisher":{"@id":"https:\/\/www.dbi-services.com\/blog\/#organization"},"alternateName":"dbi Blog","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":"Organization","@id":"https:\/\/www.dbi-services.com\/blog\/#organization","name":"dbi services","alternateName":"dbi","url":"https:\/\/www.dbi-services.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2026\/09\/dbi-services-logo.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2026\/09\/dbi-services-logo.png","width":512,"height":512,"caption":"dbi services"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/dbiservices\/","https:\/\/x.com\/dbiservices","https:\/\/www.linkedin.com\/company\/dbi-services\/","https:\/\/www.youtube.com\/user\/dbiservices"],"description":"Swiss IT consulting and services company specialised in efficient data infrastructures and platforms: databases, application servers, cloud, DevOps, digitalization, analytics, data quality and monitoring. Headquartered in Del\u00e9mont with offices in Nyon, Basel, Bern and Zurich.","email":"info@dbi-services.com","telephone":"+41 32 422 96 00","legalName":"dbi services SA"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bf7c7eb0bec165183207237c9d0ee1d5","name":"Oracle Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/12\/oracle-square.png","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/12\/oracle-square.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/12\/oracle-square.png","caption":"Oracle Team"},"description":"Posts published under the Oracle Team account are collective contributions by the dbi services Oracle consultants. The practice covers the full Oracle Database lifecycle: architecture, migration and upgrade, high availability with RAC and Data Guard, backup and recovery, performance tuning, and engineered systems such as Oracle Database Appliance and Exadata. The team holds Oracle certifications up to Certified Master level and operates customer platforms under service level agreements.","url":"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/2182","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=2182"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/2182\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=2182"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=2182"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=2182"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}