{"id":4513,"date":"2015-03-27T07:57:00","date_gmt":"2015-03-27T06:57:00","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/index-on-truncdate-do-you-still-need-old-index-1\/"},"modified":"2015-03-27T07:57:00","modified_gmt":"2015-03-27T06:57:00","slug":"index-on-truncdate-do-you-still-need-old-index-1","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/index-on-truncdate-do-you-still-need-old-index-1\/","title":{"rendered":"Index on SUBSTR(string,1,n) – do you still need old index?"},"content":{"rendered":"

By Franck Pachot<\/h2>\n

.
\nIn a previous post<\/a> I’ve shown that from 12.1.0.2 when you have an index on trunc(date) you don’t need additional index. If you need the column with full precision, then you can add it to the index on trunc(). A comment from Rainer Stenzel asked if that optimization is available for other functions. And Mohamed Houri has linked to his post where he shows that it’s the same with a trunc() on a number.<\/p>\n

Besides that, there is the same kind of optimization with SUBSTR(string,1,n) so here is the demo, with a little warning at the end.<\/p>\n

I start with the same testcase as the previous post.<\/p>\n

SQL> create table DEMO as select prod_id,prod_name,prod_eff_from +rownum\/0.3 prod_date from sh.products,(select * from dual connect by level>=1000);\nTable created.\n\nSQL> create index PROD_NAME on DEMO(prod_name);\nIndex created.\n\nSQL> create index PROD_DATE on DEMO(prod_date);\nIndex created.\n<\/code><\/pre>\n
 <\/p>\n
string>Z<\/h3>\n
I’ve an index on the PROD_NAME and I can use it with equality or inequality predicates:<\/p>\n
SQL> set autotrace on explain\nSQL> select distinct prod_name from DEMO where prod_name > 'Z';\nno rows selected\n\nExecution Plan\n----------------------------------------------------------\nPlan hash value: 72593368\n\n--------------------------------------------------------\n| Id  | Operation          | Name      | Rows  | Bytes |\n--------------------------------------------------------\n|   0 | SELECT STATEMENT   |           |     1 |    27 |\n|   1 |  SORT UNIQUE NOSORT|           |     1 |    27 |\n|*  2 |   INDEX RANGE SCAN | PROD_NAME |     1 |    27 |\n--------------------------------------------------------\n\nPredicate Information (identified by operation id):\n---------------------------------------------------\n\n   2 - access(\"PROD_NAME\">'Z')\n\n<\/code><\/pre>\n
 
\nAnd I also can use it with a LIKE when there is no starting joker:<\/p>\n
SQL> select distinct prod_name from DEMO where prod_name like 'Z%';\nno rows selected\n\nExecution Plan\n----------------------------------------------------------\nPlan hash value: 72593368\n\n--------------------------------------------------------\n| Id  | Operation          | Name      | Rows  | Bytes |\n--------------------------------------------------------\n|   0 | SELECT STATEMENT   |           |     1 |    27 |\n|   1 |  SORT UNIQUE NOSORT|           |     1 |    27 |\n|*  2 |   INDEX RANGE SCAN | PROD_NAME |     1 |    27 |\n--------------------------------------------------------\n\nPredicate Information (identified by operation id):\n---------------------------------------------------\n\n   2 - access(\"PROD_NAME\" LIKE 'Z%')\n       filter(\"PROD_NAME\" LIKE 'Z%')\n\n<\/code><\/pre>\n
 
\nThat optimization is available for several releases (9.2 if I remember well but I didn’ check).<\/p>\n
substr(string,1,n)<\/h3>\n
But sometimes, when we want to check if a column starts with a string, the application uses SUBSTR instead of LIKE:<\/p>\n
SQL> select distinct prod_name from DEMO where substr(prod_name,1,1) = 'Z';\nno rows selected\n\nExecution Plan\n----------------------------------------------------------\nPlan hash value: 1665545956\n\n--------------------------------------------------------\n| Id  | Operation          | Name      | Rows  | Bytes |\n--------------------------------------------------------\n|   0 | SELECT STATEMENT   |           |     1 |    27 |\n|   1 |  SORT UNIQUE NOSORT|           |     1 |    27 |\n|*  2 |   INDEX FULL SCAN  | PROD_NAME |     1 |    27 |\n--------------------------------------------------------\n\nPredicate Information (identified by operation id):\n---------------------------------------------------\n\n   2 - filter(SUBSTR(\"PROD_NAME\",1,1)='Z')\n\n<\/code><\/pre>\n
 
\nBut – as you see – there is no access predicate here. The whole index has to be read.
\nOf course, I can use a function based index for that:<\/p>\n
SQL> create index PROD_NAME_SUBSTR on DEMO( substr(prod_name,1,1) );\nIndex created.\n\nSQL> select distinct prod_name from DEMO where substr(prod_name,1,1) = 'Z';\nno rows selected\n\nExecution Plan\n----------------------------------------------------------\nPlan hash value: 4209586087\n\n-------------------------------------------------------------------------\n| Id  | Operation                    | Name             | Rows  | Bytes |\n-------------------------------------------------------------------------\n|   0 | SELECT STATEMENT             |                  |     1 |    31 |\n|   1 |  HASH UNIQUE                 |                  |     1 |    31 |\n|   2 |   TABLE ACCESS BY INDEX ROWID| DEMO             |     1 |    31 |\n|*  3 |    INDEX RANGE SCAN          | PROD_NAME_SUBSTR |     1 |       |\n-------------------------------------------------------------------------\n\nPredicate Information (identified by operation id):\n---------------------------------------------------\n\n   3 - access(SUBSTR(\"PROD_NAME\",1,1)='Z')\n<\/code><\/pre>\n
 <\/p>\n
One index only?<\/h3>\n
Then, as in the previous post about TRUNC I’ll check if that new index is sufficient. Let’s fdrop the first one.<\/p>\n
SQL> drop index PROD_NAME;\nIndex dropped.\n<\/code><\/pre>\n
The previous index is dropped. Let’s see if the index on SUBSTR can be used with an equality predicate:<\/p>\n
SQL> select distinct prod_name from DEMO where prod_name = 'Zero';\nno rows selected\n\nExecution Plan\n----------------------------------------------------------\nPlan hash value: 953445334\n\n-------------------------------------------------------------------------\n| Id  | Operation                    | Name             | Rows  | Bytes |\n-------------------------------------------------------------------------\n|   0 | SELECT STATEMENT             |                  |     1 |    27 |\n|   1 |  SORT UNIQUE NOSORT          |                  |     1 |    27 |\n|*  2 |   TABLE ACCESS BY INDEX ROWID| DEMO             |     1 |    27 |\n|*  3 |    INDEX RANGE SCAN          | PROD_NAME_SUBSTR |     1 |       |\n-------------------------------------------------------------------------\n\nPredicate Information (identified by operation id):\n---------------------------------------------------\n\n   2 - filter(\"PROD_NAME\"='Zero')\n   3 - access(SUBSTR(\"PROD_NAME\",1,1)='Z')\n\n<\/code><\/pre>\n
 
\nGood. The index on substring is used for index range scan on the prefix, and then the filter occurs on the result. This is fine as long as the prefix is selective enough.
\nIt is also available with inequality:<\/p>\n
SQL> select distinct prod_name from DEMO where prod_name > 'Z';\nno rows selected\n\n...<\/code><\/pre>\n
Predicate Information (identified by operation id):\n---------------------------------------------------\n\n   2 - filter(\"PROD_NAME\">'Z')\n   3 - access(SUBSTR(\"PROD_NAME\",1,1)>='Z')\n\n<\/code><\/pre>\n
And we can use it even when using a substring with a different number of characters:<\/code><\/pre>\n
SQL> select distinct prod_name from DEMO where substr(prod_name,1,4) = 'Zero';\nno rows selected<\/code><\/pre>\n
...\n\nPredicate Information (identified by operation id):\n---------------------------------------------------\n\n   2 - filter(SUBSTR(\"PROD_NAME\",1,4)='Zero')\n   3 - access(SUBSTR(\"PROD_NAME\",1,1)='Z')\n\n<\/code><\/pre>\n
 
\nHowever, if we use the LIKE syntax:<\/strong><\/p>\n
SQL> select distinct prod_name from DEMO where prod_name like 'Z%';\nno rows selected\n\nExecution Plan\n----------------------------------------------------------\nPlan hash value: 51067428\n\n---------------------------------------------------\n| Id  | Operation          | Name | Rows  | Bytes |\n---------------------------------------------------\n|   0 | SELECT STATEMENT   |      |     1 |    27 |\n|   1 |  HASH UNIQUE       |      |     1 |    27 |\n|*  2 |   TABLE ACCESS FULL| DEMO |     1 |    27 |\n---------------------------------------------------\n\nPredicate Information (identified by operation id):\n---------------------------------------------------\n\n   2 - filter(\"PROD_NAME\" LIKE 'Z%')\n\n<\/code><\/pre>\n
 
\nThe LIKE snytax does not allow to filter from the index on SUBSTR. So there are cases where we have to keep all indexes. Index on full column for LIKE predicates, and index on substring for SUBSTR predicates.
\nNote that indexes on SUBSTR are mandatory when you have columns larger than your block size, which is probably the case if you allow extended datatypes (VARCHAR2 up to 32k)<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
By Franck Pachot . In a previous post I’ve shown that from 12.1.0.2 when you have an index on trunc(date) you don’t need additional index. If you need the column with full precision, then you can add it to the index on trunc(). A comment from Rainer Stenzel asked if that optimization is available for […]<\/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":[198,59],"tags":[96],"type_dbi":[],"class_list":["post-4513","post","type-post","status-publish","format-standard","hentry","category-database-management","category-oracle","tag-oracle"],"acf":[],"yoast_head":"\nIndex on SUBSTR(string,1,n) - do you still need old index? - 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\/index-on-truncdate-do-you-still-need-old-index-1\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Index on SUBSTR(string,1,n) - do you still need old index?\" \/>\n<meta property=\"og:description\" content=\"By Franck Pachot . In a previous post I’ve shown that from 12.1.0.2 when you have an index on trunc(date) you don’t need additional index. If you need the column with full precision, then you can add it to the index on trunc(). A comment from Rainer Stenzel asked if that optimization is available for […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/index-on-truncdate-do-you-still-need-old-index-1\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2015-03-27T06:57:00+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=\"4 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\/index-on-truncdate-do-you-still-need-old-index-1\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/index-on-truncdate-do-you-still-need-old-index-1\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"Index on SUBSTR(string,1,n) – do you still need old index?\",\"datePublished\":\"2015-03-27T06:57:00+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/index-on-truncdate-do-you-still-need-old-index-1\/\"},\"wordCount\":400,\"commentCount\":0,\"keywords\":[\"Oracle\"],\"articleSection\":[\"Database management\",\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/index-on-truncdate-do-you-still-need-old-index-1\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/index-on-truncdate-do-you-still-need-old-index-1\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/index-on-truncdate-do-you-still-need-old-index-1\/\",\"name\":\"Index on SUBSTR(string,1,n) - do you still need old index? - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2015-03-27T06:57:00+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/index-on-truncdate-do-you-still-need-old-index-1\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/index-on-truncdate-do-you-still-need-old-index-1\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/index-on-truncdate-do-you-still-need-old-index-1\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Index on SUBSTR(string,1,n) – do you still need old index?\"}]},{\"@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":"Index on SUBSTR(string,1,n) - do you still need old index? - 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\/index-on-truncdate-do-you-still-need-old-index-1\/","og_locale":"en_US","og_type":"article","og_title":"Index on SUBSTR(string,1,n) - do you still need old index?","og_description":"By Franck Pachot . In a previous post I’ve shown that from 12.1.0.2 when you have an index on trunc(date) you don’t need additional index. If you need the column with full precision, then you can add it to the index on trunc(). A comment from Rainer Stenzel asked if that optimization is available for […]","og_url":"https:\/\/www.dbi-services.com\/blog\/index-on-truncdate-do-you-still-need-old-index-1\/","og_site_name":"dbi Blog","article_published_time":"2015-03-27T06:57:00+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/index-on-truncdate-do-you-still-need-old-index-1\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/index-on-truncdate-do-you-still-need-old-index-1\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"Index on SUBSTR(string,1,n) – do you still need old index?","datePublished":"2015-03-27T06:57:00+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/index-on-truncdate-do-you-still-need-old-index-1\/"},"wordCount":400,"commentCount":0,"keywords":["Oracle"],"articleSection":["Database management","Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/index-on-truncdate-do-you-still-need-old-index-1\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/index-on-truncdate-do-you-still-need-old-index-1\/","url":"https:\/\/www.dbi-services.com\/blog\/index-on-truncdate-do-you-still-need-old-index-1\/","name":"Index on SUBSTR(string,1,n) - do you still need old index? - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2015-03-27T06:57:00+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/index-on-truncdate-do-you-still-need-old-index-1\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/index-on-truncdate-do-you-still-need-old-index-1\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/index-on-truncdate-do-you-still-need-old-index-1\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Index on SUBSTR(string,1,n) – do you still need old index?"}]},{"@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\/4513","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=4513"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/4513\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=4513"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=4513"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=4513"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=4513"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}