.
\nMore and more we can see crazy queries generated by ORM frameworks or BI query generators. They are build to be easily generated rather than being optimal. Then, the optimizer has to implement more and more transformations to get an efficient execution plan. Here is one new that appeared in Oracle 12cR2: Subquery Elimination when the subquery do not filter any rows.
\n
\nA semi-join is a join where we do not need to match with all rows, but only one. We write it with an EXISTS subquery or a =ANY or =SOME one, which is equivalent.<\/p>\n
Here is the behaviour in 12.1.0.2 when the subquery do not filter any row because it reads the same table as the outer one, without any predicate:<\/p>\n
SQL> select * from dbms_xplan.display_cursor(null,null,'allstats last +outline');\nPLAN_TABLE_OUTPUT\n-----------------\nSQL_ID az1jcra46h5ua, child number 1\n-------------------------------------\nselect * from EMP where ename in (select ename from EMP)\n \nPlan hash value: 977554918\n \n----------------------------------------------------------------------------------------------------------------\n| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |\n----------------------------------------------------------------------------------------------------------------\n| 0 | SELECT STATEMENT | | 1 | | 14 |00:00:00.01 | 13 | | | |\n|* 1 | HASH JOIN SEMI | | 1 | 14 | 14 |00:00:00.01 | 13 | 1098K| 1098K| 889K (0)|\n| 2 | TABLE ACCESS FULL| EMP | 1 | 14 | 14 |00:00:00.01 | 7 | | | |\n| 3 | TABLE ACCESS FULL| EMP | 1 | 14 | 14 |00:00:00.01 | 6 | | | |\n----------------------------------------------------------------------------------------------------------------<\/code><\/pre>\nWe read 2 times the same table, join all rows and finally return as result exacly the same rows as those coming from the first full scan. This is not efficient.<\/p>\n
12.2<\/h3>\n
Here is the same query in 12.2 where we can see that the table is scanned only once because the optimizer knows that the subquery do not filter anything:<\/p>\n
\nSQL> select * from dbms_xplan.display_cursor(null,null,'allstats last +outline');\nPLAN_TABLE_OUTPUT\nSQL_ID az1jcra46h5ua, child number 0\n-------------------------------------\nselect * from EMP where ename in (select ename from EMP)\n \nPlan hash value: 3956160932\n \n------------------------------------------------------------------------------------\n| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |\n------------------------------------------------------------------------------------\n| 0 | SELECT STATEMENT | | 1 | | 14 |00:00:00.01 | 9 |\n|* 1 | TABLE ACCESS FULL| EMP | 1 | 14 | 14 |00:00:00.01 | 9 |\n------------------------------------------------------------------------------------\n<\/code><\/pre>\nThis is mentioned in the outline hints with ELIMINATE_SQ:<\/p>\n
\nOutline Data\n-------------\n \n \/*+\n BEGIN_OUTLINE_DATA\n IGNORE_OPTIM_EMBEDDED_HINTS\n OPTIMIZER_FEATURES_ENABLE('12.2.0.1')\n DB_VERSION('12.2.0.1')\n ALL_ROWS\n OUTLINE_LEAF(@\"SEL$D0DB0F9A\")\n ELIMINATE_SQ(@\"SEL$2\")\n OUTLINE(@\"SEL$1\")\n OUTLINE(@\"SEL$2\")\n FULL(@\"SEL$D0DB0F9A\" \"EMP\"@\"SEL$1\")\n END_OUTLINE_DATA\n *\/\n<\/code><\/pre>\nThis feature can be disabled with the following paramter:<\/p>\n
\nSQL> alter session set \"_optimizer_eliminate_subquery\"=false;\nSession altered.\n<\/code><\/pre>\nOr with the following hint:<\/p>\n
\nSQL> select * from EMP where ename in (select \/*+ NO_ELIMINATE_SQ *\/ ename from EMP);\n<\/code><\/pre>\nFinally here is what you can see in the CDB trace:<\/p>\n
\nSQE: Trying SQ elimination.\nSQE: Trying SQ elimination.\nSQE: Query valid for SQ elimination:******* UNPARSED QUERY IS *******\nSELECT \"EMP\".\"EMPNO\" \"EMPNO\",\"EMP\".\"ENAME\" \"ENAME\",\"EMP\".\"JOB\" \"JOB\",\"EMP\".\"MGR\" \"MGR\",\"EMP\".\"HIREDATE\" \"HIREDATE\",\"EMP\".\"SAL\" \"SAL\",\"EMP\".\"COMM\" \"COMM\",\"EMP\".\"DEPTNO\" \"DEPTNO\" FROM \"SCOTT\".\"EMP\" \"EMP\" WHERE \"EMP\".\"ENAME\"=ANY (SELECT \"EMP\".\"ENAME\" \"ENAME\" FROM \"SCOTT\".\"EMP\" \"EMP\")\nRegistered qb: SEL$D0DB0F9A 0xe7035778 (SUBQUERY ELIMINATE SEL$1; SEL$2)\n---------------------\nQUERY BLOCK SIGNATURE\n---------------------\n signature (): qb_name=SEL$D0DB0F9A nbfros=1 flg=0\n fro(0): flg=0 objn=73253 hint_alias=\"EMP\"@\"SEL$1\"\n \nSQE: Query after SQ elimination:******* UNPARSED QUERY IS *******\nSELECT \"EMP\".\"EMPNO\" \"EMPNO\",\"EMP\".\"ENAME\" \"ENAME\",\"EMP\".\"JOB\" \"JOB\",\"EMP\".\"MGR\" \"MGR\",\"EMP\".\"HIREDATE\" \"HIREDATE\",\"EMP\".\"SAL\" \"SAL\",\"EMP\".\"COMM\" \"COMM\",\"EMP\".\"DEPTNO\" \"DEPTNO\" FROM \"SCOTT\".\"EMP\" \"EMP\" WHERE 0=0 AND \"EMP\".\"ENAME\" IS NOT NULL\n<\/code><\/pre>\nThis example is simple and it is obvious that the SQL should be re-written. But with large generated queries, on complex views, this is the kind of thing that can be seen in the resulting query and this transformation will help to avoid unnecessary work.<\/p>\n","protected":false},"excerpt":{"rendered":"
By Franck Pachot . More and more we can see crazy queries generated by ORM frameworks or BI query generators. They are build to be easily generated rather than being optimal. Then, the optimizer has to implement more and more transformations to get an efficient execution plan. Here is one new that appeared in Oracle […]<\/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":[736,209,584],"type_dbi":[],"class_list":["post-10711","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-cbo","tag-oracle-12c","tag-transformation"],"acf":[],"yoast_head":"\n
12cR2 Subquery Elimination - 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\/12cr2-subquery-elimination\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"12cR2 Subquery Elimination\" \/>\n<meta property=\"og:description\" content=\"By Franck Pachot . More and more we can see crazy queries generated by ORM frameworks or BI query generators. They are build to be easily generated rather than being optimal. Then, the optimizer has to implement more and more transformations to get an efficient execution plan. Here is one new that appeared in Oracle […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/12cr2-subquery-elimination\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2017-12-21T20:27:19+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=\"3 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\/12cr2-subquery-elimination\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/12cr2-subquery-elimination\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"12cR2 Subquery Elimination\",\"datePublished\":\"2017-12-21T20:27:19+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/12cr2-subquery-elimination\/\"},\"wordCount\":266,\"commentCount\":0,\"keywords\":[\"CBO\",\"Oracle 12c\",\"Transformation\"],\"articleSection\":[\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/12cr2-subquery-elimination\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/12cr2-subquery-elimination\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/12cr2-subquery-elimination\/\",\"name\":\"12cR2 Subquery Elimination - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2017-12-21T20:27:19+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/12cr2-subquery-elimination\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/12cr2-subquery-elimination\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/12cr2-subquery-elimination\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"12cR2 Subquery Elimination\"}]},{\"@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":"12cR2 Subquery Elimination - 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\/12cr2-subquery-elimination\/","og_locale":"en_US","og_type":"article","og_title":"12cR2 Subquery Elimination","og_description":"By Franck Pachot . More and more we can see crazy queries generated by ORM frameworks or BI query generators. They are build to be easily generated rather than being optimal. Then, the optimizer has to implement more and more transformations to get an efficient execution plan. Here is one new that appeared in Oracle […]","og_url":"https:\/\/www.dbi-services.com\/blog\/12cr2-subquery-elimination\/","og_site_name":"dbi Blog","article_published_time":"2017-12-21T20:27:19+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/12cr2-subquery-elimination\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/12cr2-subquery-elimination\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"12cR2 Subquery Elimination","datePublished":"2017-12-21T20:27:19+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/12cr2-subquery-elimination\/"},"wordCount":266,"commentCount":0,"keywords":["CBO","Oracle 12c","Transformation"],"articleSection":["Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/12cr2-subquery-elimination\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/12cr2-subquery-elimination\/","url":"https:\/\/www.dbi-services.com\/blog\/12cr2-subquery-elimination\/","name":"12cR2 Subquery Elimination - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2017-12-21T20:27:19+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/12cr2-subquery-elimination\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/12cr2-subquery-elimination\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/12cr2-subquery-elimination\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"12cR2 Subquery Elimination"}]},{"@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\/10711","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=10711"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/10711\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=10711"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=10711"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=10711"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=10711"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}