{"id":3943,"date":"2014-08-21T05:17:54","date_gmt":"2014-08-21T03:17:54","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/oracle-121021-set-to-join-conversion\/"},"modified":"2014-08-21T05:17:54","modified_gmt":"2014-08-21T03:17:54","slug":"oracle-121021-set-to-join-conversion","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/oracle-121021-set-to-join-conversion\/","title":{"rendered":"Oracle 12.1.0.2.1 Set to Join Conversion"},"content":{"rendered":"

By Franck Pachot<\/h2>\n

.
\nRecently, I described the Partial Join Evaluation<\/a> transformation that appeared last year in Oracle 12c. I did it as an introduction for another transformation that appeared long time ago in 10.1.0.3 but was not used by default. And even in the latest Oracle 12c patchset 1 (aka 12.1.0.2.0) it is still not enabled. But it’s there and you can use it if you set optimizer_features_enabled to 12.1.0.2.1 (that’s not a typo!).<\/p>\n

Yes, that number looks like the future PSU for the 12c Release 1 Patchset 1 that was available recently and has no PSU yet. Lost in the release numbers? No problem. This is only default values for the _convert_set_to_join paramter but you can also use the hint to get that transformation, which is available in previous versions as well.<\/p>\n

So what does this transformation? It transforms an INTERSECT or MINUS into a join. When the tables are large but the result is small, that transformation can bring new access path avoiding full table scans and deduplication for each branch. And thanks to the Partial Join Evaluation the performance is even better in 12c. Let’s look at an example.<\/p>\n

SQL*Plus:\u00a0Release\u00a012.1.0.2.0\u00a0Production\u00a0on\u00a0Sun\u00a0Jul\u00a027\u00a022:10:57\u00a02014<\/code><\/pre>\n
Copyright\u00a0(c)\u00a01982,\u00a02014,\u00a0Oracle.\u00a0\u00a0All\u00a0rights\u00a0reserved.\nConnected\u00a0to:\n Oracle\u00a0Database\u00a012c\u00a0Enterprise\u00a0Edition\u00a0Release\u00a012.1.0.2.0\u00a0-\u00a064bit\u00a0Production\n With\u00a0the\u00a0Partitioning,\u00a0OLAP,\u00a0Advanced\u00a0Analytics\u00a0and\u00a0Real\u00a0Application\u00a0Testing\u00a0options\nSQL>\u00a0create\u00a0table\u00a0DEMO1(n\u00a0constraint\u00a0DEMO1_N\u00a0primary\u00a0key)\u00a0as\u00a0select\u00a0rownum\u00a0n\u00a0from\u00a0(select\u00a0*\u00a0from\u00a0dual\u00a0connect\u00a0by\u00a0level\u00a0 Table\u00a0created.\nSQL>\u00a0create\u00a0table\u00a0DEMO2(n\u00a0constraint\u00a0DEMO2_N\u00a0primary\u00a0key)\u00a0as\u00a0select\u00a0rownum\u00a0n\u00a0from\u00a0dual\u00a0connect\u00a0by\u00a0level\u00a0 Table\u00a0created.\nSQL>\u00a0alter\u00a0session\u00a0set\u00a0statistics_level=all;\n Session\u00a0altered.<\/code><\/pre>\n
<\/samp><\/code><\/pre>\n
So I have two tables, one with 100000 rows and one with only 10. And I want the rows from DEMO1 which are not in DEMO2:<\/p>\n
SQL>\u00a0alter\u00a0session\u00a0set\u00a0optimizer_features_enable='12.1.0.2.1';\n Session\u00a0altered.\nSQL>\u00a0select\u00a0*\u00a0from\u00a0DEMO1\u00a0intersect\u00a0select\u00a0*\u00a0from\u00a0DEMO2;\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0N\n ----------\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a02\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a03\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a04\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a05\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a06\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a07\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a08\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a09\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a010\n10\u00a0rows\u00a0selected.<\/code><\/pre>\n
Let’s have a look at the plan:<\/p>\n
SQL>\u00a0select\u00a0*\u00a0from\u00a0table(dbms_xplan.display_cursor(format=>'allstats\u00a0last'));\nPLAN_TABLE_OUTPUT\n -----------------------------------------------------------------------------\n SQL_ID\u00a09fpg8nyjaqb5f,\u00a0child\u00a0number\u00a00\n -------------------------------------\n select\u00a0*\u00a0from\u00a0DEMO1\u00a0intersect\u00a0select\u00a0*\u00a0from\u00a0DEMO2\nPlan\u00a0hash\u00a0value:\u00a04278239763\n------------------------------------------------------------------------------\n |\u00a0Id\u00a0\u00a0|\u00a0Operation\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0Name\u00a0\u00a0\u00a0\u00a0|\u00a0Starts\u00a0|\u00a0E-Rows\u00a0|\u00a0A-Rows\u00a0||\u00a0Used-Mem\u00a0|\n ------------------------------------------------------------------------------\n |\u00a0\u00a0\u00a00\u00a0|\u00a0SELECT\u00a0STATEMENT\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\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\u00a010\u00a0||\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\n |\u00a0\u00a0\u00a01\u00a0|\u00a0\u00a0INTERSECTION\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\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\u00a010\u00a0||\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\n |\u00a0\u00a0\u00a02\u00a0|\u00a0\u00a0\u00a0SORT\u00a0UNIQUE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01\u00a0|\u00a0\u00a0\u00a0\u00a0100K|\u00a0\u00a0\u00a0\u00a0100K||\u00a04078K\u00a0(0)|\n |\u00a0\u00a0\u00a03\u00a0|\u00a0\u00a0\u00a0\u00a0TABLE\u00a0ACCESS\u00a0FULL|\u00a0DEMO1\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01\u00a0|\u00a0\u00a0\u00a0\u00a0100K|\u00a0\u00a0\u00a0\u00a0100K||\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\n |\u00a0\u00a0\u00a04\u00a0|\u00a0\u00a0\u00a0SORT\u00a0UNIQUE\u00a0NOSORT|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a010\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a010\u00a0||\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\n |\u00a0\u00a0\u00a05\u00a0|\u00a0\u00a0\u00a0\u00a0INDEX\u00a0FULL\u00a0SCAN\u00a0\u00a0|\u00a0DEMO2_N\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a010\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a010\u00a0||\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\n ------------------------------------------------------------------------------\nQuery\u00a0Block\u00a0Name\u00a0\/\u00a0Object\u00a0Alias\u00a0(identified\u00a0by\u00a0operation\u00a0id):\n -------------------------------------------------------------\n\u00a0\u00a0\u00a01\u00a0-\u00a0SET$1\n \u00a0\u00a0\u00a03\u00a0-\u00a0SEL$1\u00a0\/\u00a0DEMO1@SEL$1\n \u00a0\u00a0\u00a05\u00a0-\u00a0SEL$2\u00a0\/\u00a0DEMO2@SEL$2<\/code><\/pre>\n
This is the expected plan. There is an INTERSECTION operation that implements our INTERSECT. But look: each branch had to be deduplicated (SORT UNIQUE). Note that the SORT UNIQUE NOSORT has a funny name – it’s just a SORT UNIQUE that doesn’t have to sort because its input comes from an index. Each branch had to read all the rows. Look at the big table: we read 100000 rows and use 4MB of memory to sort them in order to deduplicate them. But it’s an intersection and we have a small table that has only 10 rows. We know that the result cannot be large. Then a more efficient way would be to read the small table and for each row check if they are in the big one – through an index access. We still have to deduplicate, but we do that at the end, on the small rowset.<\/p>\n
And this is exactly what the Set to Join Conversion is doing. Let’s force it with a hint:<\/p>\n
SQL>\u00a0select\u00a0\/*+\u00a0SET_TO_JOIN(@\"SET$1\")\u00a0*\/\u00a0*\u00a0from\u00a0DEMO1\u00a0intersect\u00a0select\u00a0*\u00a0from\u00a0DEMO2;\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0N\n ----------\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a02\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a03\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a04\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a05\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a06\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a07\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a08\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a09\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a010\n10\u00a0rows\u00a0selected.\nSQL>\u00a0select\u00a0*\u00a0from\u00a0table(dbms_xplan.display_cursor(format=>'allstats\u00a0last'));\nPLAN_TABLE_OUTPUT\n -----------------------------------------------------------------\n SQL_ID\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a001z69x8w7fmu0,\u00a0child\u00a0number\u00a00\n -------------------------------------\n select\u00a0\/*+\u00a0SET_TO_JOIN(@\"SET$1\")\u00a0*\/\u00a0*\u00a0from\u00a0DEMO1\u00a0intersect\u00a0select\u00a0*\n from\u00a0DEMO2\nPlan\u00a0hash\u00a0value:\u00a0169945296\n------------------------------------------------------------------\n |\u00a0Id\u00a0\u00a0|\u00a0Operation\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0Name\u00a0\u00a0\u00a0\u00a0|\u00a0Starts\u00a0|\u00a0E-Rows\u00a0|\u00a0A-Rows\u00a0|\n ------------------------------------------------------------------\n |\u00a0\u00a0\u00a00\u00a0|\u00a0SELECT\u00a0STATEMENT\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\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\u00a010\u00a0|\n |\u00a0\u00a0\u00a01\u00a0|\u00a0\u00a0SORT\u00a0UNIQUE\u00a0NOSORT\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a010\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a010\u00a0|\n |\u00a0\u00a0\u00a02\u00a0|\u00a0\u00a0\u00a0NESTED\u00a0LOOPS\u00a0SEMI\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a010\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a010\u00a0|\n |\u00a0\u00a0\u00a03\u00a0|\u00a0\u00a0\u00a0\u00a0INDEX\u00a0FULL\u00a0SCAN\u00a0\u00a0|\u00a0DEMO2_N\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a010\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a010\u00a0|\n |*\u00a0\u00a04\u00a0|\u00a0\u00a0\u00a0\u00a0INDEX\u00a0UNIQUE\u00a0SCAN|\u00a0DEMO1_N\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a010\u00a0|\u00a0\u00a0\u00a0\u00a0100K|\u00a0\u00a0\u00a0\u00a0\u00a010\u00a0|\n ------------------------------------------------------------------\nPredicate\u00a0Information\u00a0(identified\u00a0by\u00a0operation\u00a0id):\n ---------------------------------------------------\n\u00a0\u00a0\u00a04\u00a0-\u00a0access(\"DEMO1\".\"N\"=\"DEMO2\".\"N\")<\/code><\/pre>\n
 <\/p>\n
The intersect has been transformed to a join thanks to the Set to Join transformation, and the join has been transformed to a semi-join thanks to the Partial Join Evaluation transformation. The result is clear here:<\/p>\n
    \n
  • No full table scan on the big table because the join is able to access with an index<\/li>\n
  • No deduplication which needs a large workarea<\/li>\n
  • The join can stop as soon as one row matches thanks to the semi-join<\/li>\n
  • Deduplication occurs only on result, which is small. And here it does not even require a workarea because the rows comes sorted from the index.<\/li>\n<\/ul>\n
    We can see the SET_TO_JOIN and PARTIAL_JOIN hints in the outline:<\/p>\n
    Outline\u00a0Data\n -------------\n\u00a0\u00a0\/*+\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0BEGIN_OUTLINE_DATA\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0IGNORE_OPTIM_EMBEDDED_HINTS\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0OPTIMIZER_FEATURES_ENABLE('12.1.0.2')\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0DB_VERSION('12.1.0.2')\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ALL_ROWS\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0OUTLINE_LEAF(@\"SEL$02B15F54\")\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0MERGE(@\"SEL$1\")\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0MERGE(@\"SEL$2\")\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0OUTLINE(@\"SET$09AAA538\")\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SET_TO_JOIN(@\"SET$1\")\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0OUTLINE(@\"SEL$1\")\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0OUTLINE(@\"SEL$2\")\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0OUTLINE(@\"SET$1\")\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0INDEX(@\"SEL$02B15F54\"\u00a0\"DEMO2\"@\"SEL$2\"\u00a0(\"DEMO2\".\"N\"))\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0INDEX(@\"SEL$02B15F54\"\u00a0\"DEMO1\"@\"SEL$1\"\u00a0(\"DEMO1\".\"N\"))\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0LEADING(@\"SEL$02B15F54\"\u00a0\"DEMO2\"@\"SEL$2\"\u00a0\"DEMO1\"@\"SEL$1\")\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0USE_NL(@\"SEL$02B15F54\"\u00a0\"DEMO1\"@\"SEL$1\")\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0PARTIAL_JOIN(@\"SEL$02B15F54\"\u00a0\"DEMO1\"@\"SEL$1\")\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0END_OUTLINE_DATA\n \u00a0\u00a0*\/<\/code><\/pre>\n
    So we are in 12.1.0.2 and we need a hint for that. Let’s go to 12.1.0.2.1 (which implicitely set _convert_set_to_join=true).<\/p>\n
    PLAN_TABLE_OUTPUT\n -----------------------------------------------------------------------------\n SQL_ID\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a09fpg8nyjaqb5f,\u00a0child\u00a0number\u00a01\n -------------------------------------\n select\u00a0*\u00a0from\u00a0DEMO1\u00a0intersect\u00a0select\u00a0*\u00a0from\u00a0DEMO2\nPlan\u00a0hash\u00a0value:\u00a0118900122\n------------------------------------------------------------------------------\n |\u00a0Id\u00a0\u00a0|\u00a0Operation\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0Name\u00a0\u00a0\u00a0\u00a0|\u00a0Starts\u00a0|\u00a0E-Rows\u00a0|\u00a0A-Rows\u00a0||\u00a0Used-Mem\u00a0|\n ------------------------------------------------------------------------------\n |\u00a0\u00a0\u00a00\u00a0|\u00a0SELECT\u00a0STATEMENT\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\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\u00a010\u00a0||\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\n |\u00a0\u00a0\u00a01\u00a0|\u00a0\u00a0HASH\u00a0UNIQUE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a010\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a010\u00a0||\u00a01260K\u00a0(0)|\n |\u00a0\u00a0\u00a02\u00a0|\u00a0\u00a0\u00a0NESTED\u00a0LOOPS\u00a0SEMI\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a010\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a010\u00a0||\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\n |\u00a0\u00a0\u00a03\u00a0|\u00a0\u00a0\u00a0\u00a0INDEX\u00a0FULL\u00a0SCAN\u00a0\u00a0|\u00a0DEMO2_N\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a010\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a010\u00a0||\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\n |*\u00a0\u00a04\u00a0|\u00a0\u00a0\u00a0\u00a0INDEX\u00a0UNIQUE\u00a0SCAN|\u00a0DEMO1_N\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a010\u00a0|\u00a0\u00a0\u00a0\u00a0100K|\u00a0\u00a0\u00a0\u00a0\u00a010\u00a0||\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\n ------------------------------------------------------------------------------\nPredicate\u00a0Information\u00a0(identified\u00a0by\u00a0operation\u00a0id):\n ---------------------------------------------------\n\u00a0\u00a0\u00a04\u00a0-\u00a0access(\"DEMO1\".\"N\"=\"DEMO2\".\"N\")\nNote\n -----\n \u00a0\u00a0\u00a0-\u00a0this\u00a0is\u00a0an\u00a0adaptive\u00a0plan<\/code><\/pre>\n
    Ok, we have the Set to Join Conversion here in 12.1.0.2.1<\/p>\n
    But don’t you see another difference?
    \n.
    \n.
    \n.
    \n.
    \nThe deduplication needs a workarea here. It is not a NOSORT operation – even if the result comes from the index. It seems that the CBO cannot guarantee that the result comes sorted. The clue is in the execution plan note.<\/p>\n
    But that’s for a future blog post.<\/p>\n","protected":false},"excerpt":{"rendered":"
    By Franck Pachot . Recently, I described the Partial Join Evaluation transformation that appeared last year in Oracle 12c. I did it as an introduction for another transformation that appeared long time ago in 10.1.0.3 but was not used by default. And even in the latest Oracle 12c patchset 1 (aka 12.1.0.2.0) it is still […]<\/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,96,209,67,98],"type_dbi":[],"class_list":["post-3943","post","type-post","status-publish","format-standard","hentry","category-database-management","category-oracle","tag-cost-based-optimizer","tag-oracle","tag-oracle-12c","tag-performance","tag-sql"],"acf":[],"yoast_head":"\nOracle 12.1.0.2.1 Set to Join Conversion - dbi Blog<\/title>\n<meta name=\"description\" content=\"Set to Join Conversion transformation is available from Oracle 10g but not by default. This may change soon.\" \/>\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-121021-set-to-join-conversion\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Oracle 12.1.0.2.1 Set to Join Conversion\" \/>\n<meta property=\"og:description\" content=\"Set to Join Conversion transformation is available from Oracle 10g but not by default. This may change soon.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/oracle-121021-set-to-join-conversion\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2014-08-21T03:17:54+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=\"5 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\\\/oracle-121021-set-to-join-conversion\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-121021-set-to-join-conversion\\\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"Oracle 12.1.0.2.1 Set to Join Conversion\",\"datePublished\":\"2014-08-21T03:17:54+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-121021-set-to-join-conversion\\\/\"},\"wordCount\":605,\"commentCount\":0,\"keywords\":[\"Cost Based Optimizer\",\"Oracle\",\"Oracle 12c\",\"Performance\",\"SQL\"],\"articleSection\":[\"Database management\",\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-121021-set-to-join-conversion\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-121021-set-to-join-conversion\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-121021-set-to-join-conversion\\\/\",\"name\":\"Oracle 12.1.0.2.1 Set to Join Conversion - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2014-08-21T03:17:54+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/66ab87129f2d357f09971bc7936a77ee\"},\"description\":\"Set to Join Conversion transformation is available from Oracle 10g but not by default. This may change soon.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-121021-set-to-join-conversion\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-121021-set-to-join-conversion\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-121021-set-to-join-conversion\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Oracle 12.1.0.2.1 Set to Join Conversion\"}]},{\"@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":"Oracle 12.1.0.2.1 Set to Join Conversion - dbi Blog","description":"Set to Join Conversion transformation is available from Oracle 10g but not by default. This may change soon.","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-121021-set-to-join-conversion\/","og_locale":"en_US","og_type":"article","og_title":"Oracle 12.1.0.2.1 Set to Join Conversion","og_description":"Set to Join Conversion transformation is available from Oracle 10g but not by default. This may change soon.","og_url":"https:\/\/www.dbi-services.com\/blog\/oracle-121021-set-to-join-conversion\/","og_site_name":"dbi Blog","article_published_time":"2014-08-21T03:17:54+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-121021-set-to-join-conversion\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-121021-set-to-join-conversion\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"Oracle 12.1.0.2.1 Set to Join Conversion","datePublished":"2014-08-21T03:17:54+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-121021-set-to-join-conversion\/"},"wordCount":605,"commentCount":0,"keywords":["Cost Based Optimizer","Oracle","Oracle 12c","Performance","SQL"],"articleSection":["Database management","Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/oracle-121021-set-to-join-conversion\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-121021-set-to-join-conversion\/","url":"https:\/\/www.dbi-services.com\/blog\/oracle-121021-set-to-join-conversion\/","name":"Oracle 12.1.0.2.1 Set to Join Conversion - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2014-08-21T03:17:54+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"description":"Set to Join Conversion transformation is available from Oracle 10g but not by default. This may change soon.","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-121021-set-to-join-conversion\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/oracle-121021-set-to-join-conversion\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-121021-set-to-join-conversion\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Oracle 12.1.0.2.1 Set to Join Conversion"}]},{"@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\/3943","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=3943"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/3943\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=3943"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=3943"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=3943"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=3943"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}