. and query it from different parts of the UNION ALL which generates the script:<\/p>\n The rowum in the WITH clause is used later to add the comma for all rows except the first one – using decode(n,1,’ ‘,’,’)<\/p>\n But this relied on two assumptions:<\/p>\n Of course, it would be better to add a number on each part and an ORDER BY at the end of the query. But for better readability, I didn’t.<\/p>\n However, the first assumption is wrong since 12.2 where the optimizer introduced In-Memory Cursor Duration Temp Tables. In the query above, the CTE is materialized because it is used multiple times. And this optimization keeps it in memory. But it seems that this structure does not keep the rows ordered.<\/p>\n Here is an example where I have a 4 rows table:<\/p>\n When I put the same query in a WITH clause, with its ORDER BY, and query it from 2 union all statements, the rows are not ordered anymore:<\/p>\n The execution plan shows the materialization of the CTE result, and mentions that it is an In-Memory Cursor Duration Temp Table (CURSOR DURATION MEMORY)<\/p>\n If the CTE is not materialized, the rows are ordered, bit the table is read two times:<\/p>\n If the CTE is materialized, but the new feature to keep it in memory is disabled, the rows are ordered (but probably by chance – there si no guarantee):<\/p>\n We should never rely on the preservation of the row order except when explicitly documented (like rownum over a subquery with order by). And this In-Memory Cursor Duration Temporary table also works in parallel and RAC, so we can understand that the rows are not read in the same order as they were stored. This is always a problem when we migrate. When something works by chance in a version and not in the newer versions, people usually incriminate the migration, rather than recognizing an old bug in the application, just because it was working before.<\/p>\n","protected":false},"excerpt":{"rendered":" By Franck Pachot . For a previous post I’ve run on 18c a script of mine to get the V$MYSTAT delta values between two queries. This script (new version available on GitHub) generates the queries to store some values and subtract them on the next execution. But I had to fix it for 18c because […]<\/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":[1394,61,222,96],"type_dbi":[],"class_list":["post-11398","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-cte","tag-18c","tag-in-memory","tag-oracle"],"acf":[],"yoast_head":"\n
\nFor a previous post<\/a> I’ve run on 18c a script of mine to get the V$MYSTAT delta values between two queries. This script (new version available on GitHub<\/a>) generates the queries to store some values and subtract them on the next execution. But I had to fix it for 18c because I relied on some order by in a CTE which is lost in 18c.
\nThe idea was to get the statistic names in a Common Table Expression (CTE):<\/p>\n\nwith stats as (\n select rownum n,stat_id,name from (select stat_id,name from v$statname where name in (&names) order by stat_id)\n)\n<\/code><\/pre>\n\nselect 'select ' from dual\nunion all\nselect ' '||decode(n,1,' ',',')||'\"CUR'||stat_id||'\" - '||'&'||'LAG'||stat_id||' \"DIF'||stat_id||'\"' from stats\nunion all\nselect ' '||',nvl(\"CUR'||stat_id||'\",0) \"CUR'||stat_id||'\"' from stats\nunion all\n<\/code><\/pre>\n\n
\nSQL> create table DEMO as select rownum id from xmltable('1 to 4');\nTable DEMO created.\n \nSQL> select * from DEMO order by id;\n \n ID\n----------\n 1\n 2\n 3\n 4\n<\/code><\/pre>\n\nSQL> with cte as ( select * from DEMO order by id)\n select rownum,id from cte\n union all\n select rownum,id from cte ;\n \n ROWNUM ID\n---------- ----------\n 1 4\n 2 3\n 3 2\n 4 1\n 1 4\n 2 3\n 3 2\n 4 1\n<\/code><\/pre>\n\nSQL> select * from dbms_xplan.display_cursor(format=>'+projection');\n \nPLAN_TABLE_OUTPUT\n---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\nSQL_ID 9uwc06ana6trn, child number 0\n-------------------------------------\nwith cte as ( select * from DEMO order by id) select rownum,id from\ncte union all select rownum,id from cte\n \nPlan hash value: 4025392480\n \n----------------------------------------------------------------------------------------------------------------------\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |\n----------------------------------------------------------------------------------------------------------------------\n| 0 | SELECT STATEMENT | | | | 4 (100)| |\n| 1 | TEMP TABLE TRANSFORMATION | | | | | |\n| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6640_23D74B | | | | |\n| 3 | SORT ORDER BY | | 4 | 12 | 4 (25)| 00:00:01 |\n| 4 | TABLE ACCESS FULL | DEMO | 4 | 12 | 3 (0)| 00:00:01 |\n| 5 | UNION-ALL | | | | | |\n| 6 | COUNT | | | | | |\n| 7 | VIEW | | 4 | 52 | 2 (0)| 00:00:01 |\n| 8 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6640_23D74B | 4 | 12 | 2 (0)| 00:00:01 |\n| 9 | COUNT | | | | | |\n| 10 | VIEW | | 4 | 52 | 2 (0)| 00:00:01 |\n| 11 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6640_23D74B | 4 | 12 | 2 (0)| 00:00:01 |\n----------------------------------------------------------------------------------------------------------------------\n \nColumn Projection Information (identified by operation id):\n-----------------------------------------------------------\n 1 - STRDEF[22], STRDEF[22]\n 2 - SYSDEF[4], SYSDEF[0], SYSDEF[1], SYSDEF[120], SYSDEF[0]\n 3 - (#keys=1) \"DEMO\".\"ID\"[NUMBER,22]\n 4 - (rowset=256) \"DEMO\".\"ID\"[NUMBER,22]\n 5 - STRDEF[22], STRDEF[22]\n 6 - \"ID\"[NUMBER,22], ROWNUM[8]\n 7 - \"ID\"[NUMBER,22]\n 8 - \"C0\"[NUMBER,22]\n 9 - \"ID\"[NUMBER,22], ROWNUM[8]\n 10 - \"ID\"[NUMBER,22]\n 11 - \"C0\"[NUMBER,22]\n<\/code><\/pre>\n\/*+ inline *\/<\/h3>\n
\nSQL> with cte as ( select \/*+ inline *\/ * from DEMO order by id)\n 2 select rownum,id from cte\n 3 union all\n 4 select rownum,id from cte ;\n \n ROWNUM ID\n---------- ----------\n 1 1\n 2 2\n 3 3\n 4 4\n 1 1\n 2 2\n 3 3\n 4 4\n \n8 rows selected.\n \nSQL>\nSQL> select * from dbms_xplan.display_cursor(format=>'+projection');\n \nPLAN_TABLE_OUTPUT\n---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\nSQL_ID 0dad0vwg3k0th, child number 0\n-------------------------------------\nwith cte as ( select \/*+ inline *\/ * from DEMO order by id) select\nrownum,id from cte union all select rownum,id from cte\n \nPlan hash value: 2913170750\n \n------------------------------------------------------------------------------\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |\n------------------------------------------------------------------------------\n| 0 | SELECT STATEMENT | | | | 8 (100)| |\n| 1 | UNION-ALL | | | | | |\n| 2 | COUNT | | | | | |\n| 3 | VIEW | | 4 | 52 | 4 (25)| 00:00:01 |\n| 4 | SORT ORDER BY | | 4 | 12 | 4 (25)| 00:00:01 |\n| 5 | TABLE ACCESS FULL| DEMO | 4 | 12 | 3 (0)| 00:00:01 |\n| 6 | COUNT | | | | | |\n| 7 | VIEW | | 4 | 52 | 4 (25)| 00:00:01 |\n| 8 | SORT ORDER BY | | 4 | 12 | 4 (25)| 00:00:01 |\n| 9 | TABLE ACCESS FULL| DEMO | 4 | 12 | 3 (0)| 00:00:01 |\n------------------------------------------------------------------------------\n \nColumn Projection Information (identified by operation id):\n-----------------------------------------------------------\n \n 1 - STRDEF[22], STRDEF[22]\n 2 - \"ID\"[NUMBER,22], ROWNUM[8]\n 3 - \"ID\"[NUMBER,22]\n 4 - (#keys=1) \"DEMO\".\"ID\"[NUMBER,22]\n 5 - (rowset=256) \"DEMO\".\"ID\"[NUMBER,22]\n 6 - \"ID\"[NUMBER,22], ROWNUM[8]\n 7 - \"ID\"[NUMBER,22]\n 8 - (#keys=1) \"DEMO\".\"ID\"[NUMBER,22]\n 9 - (rowset=256) \"DEMO\".\"ID\"[NUMBER,22]\n<\/code><\/pre>\n“_in_memory_cdt”=off<\/h3>\n
\nSQL> alter session set \"_in_memory_cdt\"=off;\n \nSession altered.\n \nSQL>\nSQL> with cte as ( select \/*+ materialize *\/ * from DEMO order by id)\n 2 select rownum,id from cte\n 3 union all\n 4 select rownum,id from cte ;\n \n ROWNUM ID\n---------- ----------\n 1 1\n 2 2\n 3 3\n 4 4\n 1 1\n 2 2\n 3 3\n 4 4\n \n8 rows selected.\n \nSQL> select * from dbms_xplan.display_cursor(format=>'+projection');\n \nPLAN_TABLE_OUTPUT\n---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\nSQL_ID 9uwc06ana6trn, child number 1\n-------------------------------------\nwith cte as ( select * from DEMO order by id) select rownum,id from\ncte union all select rownum,id from cte\n \nPlan hash value: 4025392480\n \n--------------------------------------------------------------------------------------------------------\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |\n--------------------------------------------------------------------------------------------------------\n| 0 | SELECT STATEMENT | | | | 4 (100)| |\n| 1 | TEMP TABLE TRANSFORMATION | | | | | |\n| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D664D_23D74B | | | | |\n| 3 | SORT ORDER BY | | 4 | 12 | 4 (25)| 00:00:01 |\n| 4 | TABLE ACCESS FULL | DEMO | 4 | 12 | 3 (0)| 00:00:01 |\n| 5 | UNION-ALL | | | | | |\n| 6 | COUNT | | | | | |\n| 7 | VIEW | | 4 | 52 | 2 (0)| 00:00:01 |\n| 8 | TABLE ACCESS FULL | SYS_TEMP_0FD9D664D_23D74B | 4 | 12 | 2 (0)| 00:00:01 |\n| 9 | COUNT | | | | | |\n| 10 | VIEW | | 4 | 52 | 2 (0)| 00:00:01 |\n| 11 | TABLE ACCESS FULL | SYS_TEMP_0FD9D664D_23D74B | 4 | 12 | 2 (0)| 00:00:01 |\n--------------------------------------------------------------------------------------------------------\n \nColumn Projection Information (identified by operation id):\n-----------------------------------------------------------\n \n 1 - STRDEF[22], STRDEF[22]\n 2 - SYSDEF[4], SYSDEF[0], SYSDEF[1], SYSDEF[120], SYSDEF[0]\n 3 - (#keys=1) \"DEMO\".\"ID\"[NUMBER,22]\n 4 - (rowset=256) \"DEMO\".\"ID\"[NUMBER,22]\n 5 - STRDEF[22], STRDEF[22]\n 6 - \"ID\"[NUMBER,22], ROWNUM[8]\n 7 - \"ID\"[NUMBER,22]\n 8 - \"C0\"[NUMBER,22]\n 9 - \"ID\"[NUMBER,22], ROWNUM[8]\n 10 - \"ID\"[NUMBER,22]\n 11 - \"C0\"[NUMBER,22]\n<\/code><\/pre>\nSo what?<\/h3>\n