.
\nDo you think that it’s better to write semi-join SQL statements with IN(), EXISTS(), or to do a JOIN? Usually, the optimizer will evaluate the cost and do the transformation for you. And in this area, one more transformation has been introduced in 12c which is the Partial Join Evaluation (PJE).<\/p>\n
First, let’s have a look at the 11g behaviour. For that example, I use the SCOTT schema, but I hire a lot more employees in departement 40:<\/p>\n
\nSQL> alter table EMP modify empno number(10);\nTable altered.\nSQL> insert into EMP(empno,deptno) select rownum+10000,40 from EMP,(select * from dual connect by level\n<\/code><\/pre>\nWhy department 40? I’ll explain it below, but I let you think about it before. In the default SCOTT schema, there is a department 40 in DEPT table, but which has no employees in EMP. And the new transformation is not useful in that case.<\/p>\n
11g behaviour<\/h3>\n
Now, I’m running the following query to check all the departments that have at least one employee:
\nI can write it with IN:<\/p>\n
\nPLAN_TABLE_OUTPUT\n ------------------------------------------------------------------------\n SQL_ID 6y71msam9w32r, child number 0\n -------------------------------------\n select distinct deptno,dname from dept\n where deptno in ( select deptno from emp)\n \nPlan hash value: 1754319153\n \n------------------------------------------------------------------------\n| Id | Operation | Name | Starts | E-Rows | A-Rows | Buffers |\n------------------------------------------------------------------------\n| 0 | SELECT STATEMENT | | 1 | | 4 | 15 |\n|* 1 | HASH JOIN SEMI | | 1 | 4 | 4 | 15 |\n| 2 | TABLE ACCESS FULL| DEPT | 1 | 4 | 4 | 7 |\n| 3 | TABLE ACCESS FULL| EMP | 1 | 15068 | 388 | 8 |\n------------------------------------------------------------------------\nPredicate Information (identified by operation id):\n ---------------------------------------------------\n \n 1 - access(\"DEPTNO\"=\"DEPTNO\")\n<\/code><\/pre>\nor with EXISTS:<\/p>\n
\nPLAN_TABLE_OUTPUT\n------------------------------------------------------------------------\nSQL_ID cbpa3zjtzfzrn, child number 0\n-------------------------------------\nselect distinct deptno,dname from dept \n where exists ( select 1 from emp where emp.deptno=dept.deptno)\n \nPlan hash value: 1754319153\n \n------------------------------------------------------------------------\n| Id | Operation | Name | Starts | E-Rows | A-Rows | Buffers |\n------------------------------------------------------------------------\n| 0 | SELECT STATEMENT | | 1 | | 4 | 15 |\n|* 1 | HASH JOIN SEMI | | 1 | 4 | 4 | 15 |\n| 2 | TABLE ACCESS FULL| DEPT | 1 | 4 | 4 | 7 |\n| 3 | TABLE ACCESS FULL| EMP | 1 | 15068 | 388 | 8 |\n------------------------------------------------------------------------\nPredicate Information (identified by operation id):\n---------------------------------------------------\n \n 1 - access(\"DEPTNO\"=\"DEPTNO\")\n<\/code><\/pre>\nBoth are good. We didn’t have to read the whole EMP table. I have 15000 rows in my table, I do a full scan on it, but look at the A-Rows: only 388 rows were actually read.<\/p>\n
The HASH JOIN first read the DEPT table in order to build the hash table. So it already knows that we cannot have more than 4 distinct departments.<\/p>\n
Then we do the join to EMP just to check which of those departments have an employee. But we can stop as soon as we find the 4 departments. This is the reason why we have read only 388 rows here. And this is exactly what a Semi Join is: we don’t need all the matching rows, we return at most one row per matching pair.<\/p>\n
Ok. What if we write the join ourselves?<\/p>\n
\nPLAN_TABLE_OUTPUT\n-------------------------------------------------------------------------\nSQL_ID 2xjj9jybqja87, child number 1\n-------------------------------------\nselect distinct deptno,dname from dept join emp using(deptno)\n \nPlan hash value: 2962452962\n \n-------------------------------------------------------------------------\n| Id | Operation | Name | Starts | E-Rows | A-Rows | Buffers |\n-------------------------------------------------------------------------\n| 0 | SELECT STATEMENT | | 1 | | 4 | 129 |\n| 1 | HASH UNIQUE | | 1 | 15068 | 4 | 129 |\n|* 2 | HASH JOIN | | 1 | 15068 | 14014 | 129 |\n| 3 | TABLE ACCESS FULL| DEPT | 1 | 4 | 4 | 7 |\n| 4 | TABLE ACCESS FULL| EMP | 1 | 15068 | 14014 | 122 |\n-------------------------------------------------------------------------\n \nPredicate Information (identified by operation id):\n---------------------------------------------------\n \n 2 - access(\"DEPT\".\"DEPTNO\"=\"EMP\".\"DEPTNO\")\n<\/code><\/pre>\nBad luck. We have to read all the rows. More rows and more buffers.<\/p>\n
12c behaviour<\/h3>\n
Let’s do the same in 12.1.0.1:<\/p>\n
\nPLAN_TABLE_OUTPUT\n-------------------------------------------------------------------------\nSQL_ID 2xjj9jybqja87, child number 0\n-------------------------------------\nselect distinct deptno,dname from dept join emp using(deptno)\n \nPlan hash value: 1629510749\n \n-------------------------------------------------------------------------\n| Id | Operation | Name | Starts | E-Rows | A-Rows | Buffers |\n-------------------------------------------------------------------------\n| 0 | SELECT STATEMENT | | 1 | | 4 | 14 |\n| 1 | HASH UNIQUE | | 1 | 4 | 4 | 14 |\n|* 2 | HASH JOIN SEMI | | 1 | 4 | 4 | 14 |\n| 3 | TABLE ACCESS FULL| DEPT | 1 | 4 | 4 | 7 |\n| 4 | TABLE ACCESS FULL| EMP | 1 | 15068 | 388 | 7 |\n-------------------------------------------------------------------------\n \nPredicate Information (identified by operation id):\n---------------------------------------------------\n \n 2 - access(\"DEPT\".\"DEPTNO\"=\"EMP\".\"DEPTNO\")\n<\/code><\/pre>\nSame plan but less rows have been read. If we check the outlines, we see the new feature:<\/p>\n
\n PARTIAL_JOIN(@\"SEL$58A6D7F6\" \"EMP\"@\"SEL$1\")\n<\/code><\/pre>\nAnd here is what we see in the optimizer trace:<\/p>\n
\nOPTIMIZER STATISTICS AND COMPUTATIONS\nPJE: Checking validity of partial join eval on query block SEL$58A6D7F6 (#1)\nPJE: Passed validity of partial join eval by query block SEL$58A6D7F6 (#1)\nPJE: Partial join eval conversion for query block SEL$58A6D7F6 (#1).\nPJE: Table marked for partial join eval: EMP[EMP]#1\n<\/code><\/pre>\nThe hints that control the feature are PARTIAL_JOIN and NO_PARTIAL_JOIN and the transformation is enabled by _optimizer_partial_join_eval which appeared in 12c<\/i>.<\/p>\n
But of course, the optimization is useful only when we have all the values at the beginning of the table. This is why I added at least one employee in department 40. If there are some rows in DEPT that have no matching row in EMP, then Oracle cannot know the result before reaching the end of the table.<\/p>\n","protected":false},"excerpt":{"rendered":"
By Franck Pachot . Do you think that it’s better to write semi-join SQL statements with IN(), EXISTS(), or to do a JOIN? Usually, the optimizer will evaluate the cost and do the transformation for you. And in this area, one more transformation has been introduced in 12c which is the Partial Join Evaluation (PJE). […]<\/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":[217,209],"type_dbi":[],"class_list":["post-2158","post","type-post","status-publish","format-standard","hentry","category-database-management","category-oracle","tag-cost-based-optimizer","tag-oracle-12c"],"acf":[],"yoast_head":"\n
Partial Join Evaluation in Oracle 12c - dbi Blog<\/title>\n<meta name=\"description\" content=\"A new CBO tranformation was introduced in Oracle 12c: Partial Join Evaluation (PJE). Let's have a look at it.\" \/>\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\/partial-join-evaluation-in-oracle-12c\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Partial Join Evaluation in Oracle 12c\" \/>\n<meta property=\"og:description\" content=\"A new CBO tranformation was introduced in Oracle 12c: Partial Join Evaluation (PJE). Let's have a look at it.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/partial-join-evaluation-in-oracle-12c\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2014-07-13T23:21: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\/partial-join-evaluation-in-oracle-12c\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/partial-join-evaluation-in-oracle-12c\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"Partial Join Evaluation in Oracle 12c\",\"datePublished\":\"2014-07-13T23:21:00+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/partial-join-evaluation-in-oracle-12c\/\"},\"wordCount\":432,\"commentCount\":0,\"keywords\":[\"Cost Based Optimizer\",\"Oracle 12c\"],\"articleSection\":[\"Database management\",\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/partial-join-evaluation-in-oracle-12c\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/partial-join-evaluation-in-oracle-12c\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/partial-join-evaluation-in-oracle-12c\/\",\"name\":\"Partial Join Evaluation in Oracle 12c - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2014-07-13T23:21:00+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"description\":\"A new CBO tranformation was introduced in Oracle 12c: Partial Join Evaluation (PJE). Let's have a look at it.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/partial-join-evaluation-in-oracle-12c\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/partial-join-evaluation-in-oracle-12c\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/partial-join-evaluation-in-oracle-12c\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Partial Join Evaluation in Oracle 12c\"}]},{\"@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":"Partial Join Evaluation in Oracle 12c - dbi Blog","description":"A new CBO tranformation was introduced in Oracle 12c: Partial Join Evaluation (PJE). Let's have a look at it.","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\/partial-join-evaluation-in-oracle-12c\/","og_locale":"en_US","og_type":"article","og_title":"Partial Join Evaluation in Oracle 12c","og_description":"A new CBO tranformation was introduced in Oracle 12c: Partial Join Evaluation (PJE). Let's have a look at it.","og_url":"https:\/\/www.dbi-services.com\/blog\/partial-join-evaluation-in-oracle-12c\/","og_site_name":"dbi Blog","article_published_time":"2014-07-13T23:21: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\/partial-join-evaluation-in-oracle-12c\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/partial-join-evaluation-in-oracle-12c\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"Partial Join Evaluation in Oracle 12c","datePublished":"2014-07-13T23:21:00+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/partial-join-evaluation-in-oracle-12c\/"},"wordCount":432,"commentCount":0,"keywords":["Cost Based Optimizer","Oracle 12c"],"articleSection":["Database management","Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/partial-join-evaluation-in-oracle-12c\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/partial-join-evaluation-in-oracle-12c\/","url":"https:\/\/www.dbi-services.com\/blog\/partial-join-evaluation-in-oracle-12c\/","name":"Partial Join Evaluation in Oracle 12c - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2014-07-13T23:21:00+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"description":"A new CBO tranformation was introduced in Oracle 12c: Partial Join Evaluation (PJE). Let's have a look at it.","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/partial-join-evaluation-in-oracle-12c\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/partial-join-evaluation-in-oracle-12c\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/partial-join-evaluation-in-oracle-12c\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Partial Join Evaluation in Oracle 12c"}]},{"@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\/2158","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=2158"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/2158\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=2158"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=2158"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=2158"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=2158"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}