I had a task to clean up some data and I decided to use in-database archiving, a new feature in 12c. As I was working I noticed some unexpected resultsets along the way. I was quite puzzled for a little time, so I set up a test-case and realized that I’ve struck on a bug. The bug seems related to the ANSI re-architecture-features of the optimizer.

In my case I wanted to use in-database archiving so that, as I worked my way through different reasons to delete data, I could “soft-delete” dem by tagging the rows with the cause of the deletion. Thereby I could easily get an re-/overview later and possibly roll back the rows related to a certain cause before I commit to the final deletion:

UPDATE MyTable
SET ORA_ARCHIVE_STATE='Reason for deletion'
WHERE ...

If your’e unfamiliar with in-database archiving, you might check out the 12.1 documentation and/or Tim Hall’s blog on the subject.

Anyway – at one point, as I was doing outer joins, I started getting matches and rows I shouldn’t have gotten, and for a brief period I was really pussled and wondered if I’d forgotten everything about basic joins during the christmas holidays.

As an example, a query like the one under got 0 rows updated, although the inner query returned rows.

UPDATE MyTable
SET ORA_ARCHIVE_STATE = 'No match in MyOtherTable'
WHERE ROWID IN ( 
  SELECT MyTable.ROWID 
  FROM MyTable 
  LEFT JOIN MyOtherTable
    ON (MyTable.JoinColumn = MyOtherTable.JoinColumn)
);

Strange behaviour indeed. So I set up a test case that proved that there’s a different plan for ANSI-92 vs ANSI-89 syntax. And as a tip from my friend Harald at Oracle i turned off ANSI-rearchitecture and that also gave a correct result. I also did a test to check if it had anything to do with DDL optimization, since it does a rewrite of the query-filters.

There’s also a bug, “Bug 20544414 – wrong result of row archival option using outer join condition (Doc ID 20544414.8)”, but that’s related to too few rows being returned and in addition it is reported fixed in 12.2.0.1, which I was running.

So – short story – if you don’t want to go throught the case below; you need to turn off the ANSI re-architecture features to get correct outer-join-results when using in-database archiving:

ALTER SESSION SET "_optimizer_ansi_rearchitecture"=FALSE;

Of course, setting this parameter is going to affect other plans as well, so proceed with caution.

Complete test-case follows:

SQL> ALTER SYSTEM FLUSH SHARED_POOL;
System FLUSH altered.
SQL> SELECT banner FROM v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
PL/SQL Release 12.2.0.1.0 - Production
CORE 12.2.0.1.0 Production
TNS for Linux: Version 12.2.0.1.0 - Production
NLSRTL Version 12.2.0.1.0 - Production

SQL> CREATE TABLE t1 (ID)
  2  AS
  3  SELECT ROWNUM
  4  FROM dual
  5  CONNECT BY LEVEL <= 5;
Table T1 created.

SQL> CREATE TABLE t2 (ID)
  2  AS
  3  SELECT ROWNUM
  4  FROM dual
  5  CONNECT BY LEVEL <= 5;
Table T2 created.

SQL> ALTER TABLE t1 ROW ARCHIVAL;
Table T1 altered.

SQL> SELECT * FROM t1;
        ID
----------
         1
         2
         3
         4
         5
SQL> UPDATE t1 SET ora_archive_state = 'xyx' WHERE MOD(ID, 2) = 0;
2 rows updated.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM t1;
        ID
----------
         1
         3
         5
SQL> SELECT * FROM t2;
        ID
----------
         1
         2
         3
         4
         5
SQL> -- Left outer join
SQL> SELECT /*+gather_plan_statistics indbarch1*/ t1.id, t2.id, t1.ora_archive_state
  2  FROM t1
  3  LEFT JOIN t2 ON (t1.ID = t2.ID);
        ID         ID ORA_ARCHIV
---------- ---------- ----------
         1          1 0
         3          3 0
         5          5 0
         2            xyx
         4            xyx
SQL> -- Should have gotten only 3 rows!
SQL> SELECT sql_text, sql_id
  2  FROM V$SQL
  3  WHERE SQL_FULLTEXT LIKE '%indbarch1%';
SQL_TEXT                                                                                             SQL_ID
---------------------------------------------------------------------------------------------------- -------------
SELECT /*+gather_plan_statistics indbarch1*/ t1.id, t2.id, t1.ora_archive_state FROM t1 LEFT JOIN t2 5u475z7g0gxph
ON (t1.ID = t2.ID)
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('5u475z7g0gxph',NULL,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  5u475z7g0gxph, child number 0
-------------------------------------
SELECT /*+gather_plan_statistics indbarch1*/ t1.id, t2.id,
t1.ora_archive_state FROM t1 LEFT JOIN t2 ON (t1.ID = t2.ID)
Plan hash value: 1823443478
----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      5 |00:00:00.01 |      18 |       |       |          |
|*  1 |  HASH JOIN OUTER   |      |      1 |      5 |      5 |00:00:00.01 |      18 |  1030K|  1030K|  852K (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |      5 |      5 |00:00:00.01 |       9 |       |       |          |
|   3 |   TABLE ACCESS FULL| T2   |      1 |      5 |      5 |00:00:00.01 |       9 |       |       |          |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."ID"="T2"."ID" AND DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00002$",0)),NULL,NVL("T1"."ORA
              _ARCHIVE_STATE",'0'),'0',NVL("T1"."ORA_ARCHIVE_STATE",'0'),'1',"T1"."ORA_ARCHIVE_STATE")=CASE  WHEN
              ("T2"."ID" IS NOT NULL) THEN '0' ELSE '0' END )
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold

27 rows selected.
SQL> -- That decode with the case doesn't look right to me...

SQL> -- ANSI-89 standard works:
SQL> SELECT /*+gather_plan_statistics indbarch1 ansi-89*/ t1.ID, t2.ID, t1.ora_archive_state
  2  FROM t1, t2
  3  WHERE t1.ID = t2.ID(+);
        ID         ID ORA_ARCHIV
---------- ---------- ----------
         1          1 0
         3          3 0
         5          5 0
SQL> -- Expected result gotten.
SQL> SELECT sql_id, sql_text
  2  FROM V$SQL
  3  WHERE SQL_FULLTEXT LIKE '%indbarch1 ansi-89%';
SQL_ID        SQL_TEXT
------------- ----------------------------------------------------------------------------------------------------
20xta80r07r17 SELECT /*+gather_plan_statistics indbarch1 ansi-89*/ t1.ID, t2.ID, t1.ora_archive_state FROM t1, t2
              WHERE t1.ID = t2.ID(+)

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('20xta80r07r17',NULL,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  20xta80r07r17, child number 0
-------------------------------------

SELECT /*+gather_plan_statistics indbarch1 ansi-89*/ t1.ID, t2.ID,
t1.ora_archive_state FROM t1, t2 WHERE t1.ID = t2.ID(+)
Plan hash value: 1823443478
----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      3 |00:00:00.01 |      18 |       |       |          |
|*  1 |  HASH JOIN OUTER   |      |      1 |      1 |      3 |00:00:00.01 |      18 |  1101K|  1101K|  862K (0)|
|*  2 |   TABLE ACCESS FULL| T1   |      1 |      1 |      3 |00:00:00.01 |       9 |       |       |          |
|   3 |   TABLE ACCESS FULL| T2   |      1 |      5 |      5 |00:00:00.01 |       9 |       |       |          |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."ID"="T2"."ID")
   2 - filter(DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00002$",0)),NULL,NVL("T1"."ORA_ARCHIVE_STATE",'0'),'0'
              ,NVL("T1"."ORA_ARCHIVE_STATE",'0'),'1',"T1"."ORA_ARCHIVE_STATE")='0')
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold

27 rows selected.
SQL> -- Workaround: disable ansi re-architecture:
SQL> ALTER SESSION SET "_optimizer_ansi_rearchitecture"=FALSE;
Session altered.

SQL> -- Left outer join:
SQL> SELECT /*+gather_plan_statistics indbarch1 no re-arch*/ t1.id, t2.id, t1.ora_archive_state
  2  FROM t1
  3  LEFT JOIN t2 ON (t1.ID = t2.ID);
        ID         ID ORA_ARCHIV
---------- ---------- ----------
         1          1 0
         3          3 0
         5          5 0
SQL> -- Now we have a correct result-set. :-)
SQL> SELECT sql_id, sql_text
  2  FROM V$SQL
  3  WHERE SQL_FULLTEXT LIKE '%indbarch1 no re-arch%';
SQL_ID        SQL_TEXT
------------- ----------------------------------------------------------------------------------------------------
3v8r8qu0aqgk1 SELECT /*+gather_plan_statistics indbarch1 no re-arch*/ t1.id, t2.id, t1.ora_archive_state FROM t1 L
              EFT JOIN t2 ON (t1.ID = t2.ID)

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('3v8r8qu0aqgk1',null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  3v8r8qu0aqgk1, child number 1
-------------------------------------

SELECT /*+gather_plan_statistics indbarch1 no re-arch*/ t1.id, t2.id,
t1.ora_archive_state FROM t1 LEFT JOIN t2 ON (t1.ID = t2.ID)
Plan hash value: 1823443478
----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      3 |00:00:00.01 |      18 |       |       |          |
|*  1 |  HASH JOIN OUTER   |      |      1 |      1 |      3 |00:00:00.01 |      18 |  1101K|  1101K|  877K (0)|
|*  2 |   TABLE ACCESS FULL| T1   |      1 |      1 |      3 |00:00:00.01 |       9 |       |       |          |
|   3 |   TABLE ACCESS FULL| T2   |      1 |      5 |      5 |00:00:00.01 |       9 |       |       |          |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."ID"="T2"."ID")
   2 - filter(DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00002$",0)),NULL,NVL("T1"."ORA_ARCHIVE_STATE",'0'),'0'
              ,NVL("T1"."ORA_ARCHIVE_STATE",'0'),'1',"T1"."ORA_ARCHIVE_STATE")='0')
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold

27 rows selected.

SQL> -- Switching the default back on for further tests:
SQL> ALTER SESSION SET "_optimizer_ansi_rearchitecture"=TRUE;
Session altered.

SQL> -- Right outer join
SQL> SELECT /*+gather_plan_statistics indbarch2*/
  2  t1.id, t2.id, t1.ora_archive_state
  3  FROM t1
  4  RIGHT JOIN t2 ON (t1.ID = t2.ID);
        ID         ID ORA_ARCHIV
---------- ---------- ----------
         1          1 0
         3          3 0
         5          5 0
                    2 0
                    4 0
SQL> -- ORA_ARCHIVE_STATE doesn't look correct

SQL> -- Inner join
SQL> SELECT /*+gather_plan_statistics indbarch3*/ t1.id, t2.id, t1.ora_archive_state
  2  FROM t2
  3  JOIN t1 ON (t1.ID = t2.ID);
        ID         ID ORA_ARCHIV
---------- ---------- ----------
         1          1 0
         3          3 0
         5          5 0

SQL> SELECT sql_id, sql_text
  2  FROM V$SQL
  3  WHERE SQL_FULLTEXT LIKE '%indbarch3%';
SQL_ID        SQL_TEXT
------------- ----------------------------------------------------------------------------------------------------
90jbchxr3rgqh SELECT /*+gather_plan_statistics indbarch3*/ t1.id, t2.id, t1.ora_archive_state FROM t2 JOIN t1 ON (
              t1.ID = t2.ID)

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('90jbchxr3rgqh',NULL,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  90jbchxr3rgqh, child number 0
-------------------------------------
SELECT /*+gather_plan_statistics indbarch3*/ t1.id, t2.id,
t1.ora_archive_state FROM t2 JOIN t1 ON (t1.ID = t2.ID)
Plan hash value: 1838229974
----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      3 |00:00:00.01 |      18 |       |       |          |
|*  1 |  HASH JOIN         |      |      1 |      1 |      3 |00:00:00.01 |      18 |  1190K|  1190K|  892K (0)|
|*  2 |   TABLE ACCESS FULL| T1   |      1 |      1 |      3 |00:00:00.01 |       9 |       |       |          |
|   3 |   TABLE ACCESS FULL| T2   |      1 |      5 |      5 |00:00:00.01 |       9 |       |       |          |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."ID"="T2"."ID")
   2 - filter(DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00002$",0)),NULL,NVL("T1"."ORA_ARCHIVE_STATE",'0'),'0'
              ,NVL("T1"."ORA_ARCHIVE_STATE",'0'),'1',"T1"."ORA_ARCHIVE_STATE")='0')
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold

27 rows selected.

SQL> DROP TABLE t1;
Table T1 dropped.

SQL> DROP TABLE t2;
Table T2 dropped.

Same test with modified create table statements to see if it’s related to the DDL-optimization in 12c. As it turns out, it isn’t related. Bug is still there.

SQL> ALTER SYSTEM FLUSH SHARED_POOL;
System FLUSH altered.

SQL> CREATE TABLE t1 (ID NUMBER)
  2  ROW ARCHIVAL;
Table T1 created.

SQL> INSERT INTO t1
  2  SELECT ROWNUM
  3  FROM dual
  4  CONNECT BY LEVEL <= 5;
5 rows inserted. 

SQL> CREATE TABLE t2 (ID)
  2  AS
  3  SELECT ROWNUM
  4  FROM dual
  5  CONNECT BY LEVEL <= 5;
Table T2 created.

SQL> SELECT * FROM t1;
        ID
----------
         1
         2
         3
         4
         5
SQL> UPDATE t1 SET ora_archive_state = 'xyx' WHERE MOD(ID, 2) = 0;
2 rows updated.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM t1;
        ID
----------
         1
         3
         5
SQL> SELECT * FROM t2;
        ID
----------
         1
         2
         3
         4
         5
SQL> -- Left outer join
SQL> SELECT /*+gather_plan_statistics indbarch1*/ t1.id, t2.id, t1.ora_archive_state
  2  FROM t1
  3  LEFT JOIN t2 ON (t1.ID = t2.ID);
        ID         ID ORA_ARCHIV
---------- ---------- ----------
         1          1 0
         3          3 0
         5          5 0
         2            xyx
         4            xyx
SQL> -- Should have gotten only 3 rows!
SQL> SELECT sql_id, sql_text
  2  FROM V$SQL
  3  WHERE SQL_FULLTEXT LIKE '%indbarch1%';
SQL_ID        SQL_TEXT
------------- ----------------------------------------------------------------------------------------------------
5u475z7g0gxph SELECT /*+gather_plan_statistics indbarch1*/ t1.id, t2.id, t1.ora_archive_state FROM t1 LEFT JOIN t2
               ON (t1.ID = t2.ID)

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('5u475z7g0gxph',NULL,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  5u475z7g0gxph, child number 0
-------------------------------------

SELECT /*+gather_plan_statistics indbarch1*/ t1.id, t2.id,
t1.ora_archive_state FROM t1 LEFT JOIN t2 ON (t1.ID = t2.ID)
Plan hash value: 1823443478
----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      5 |00:00:00.01 |      14 |       |       |          |
|*  1 |  HASH JOIN OUTER   |      |      1 |      5 |      5 |00:00:00.01 |      14 |  1143K|  1143K|  595K (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |      5 |      5 |00:00:00.01 |       5 |       |       |          |
|   3 |   TABLE ACCESS FULL| T2   |      1 |      5 |      5 |00:00:00.01 |       9 |       |       |          |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."ID"="T2"."ID" AND "T1"."ORA_ARCHIVE_STATE"=CASE  WHEN ("T2"."ID" IS NOT NULL) THEN
              '0' ELSE '0' END )
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold

27 rows selected.

SQL> -- That case-statement doesn't look right to me...
SQL> -- Right outer join
SQL> SELECT /*+gather_plan_statistics indbarch2*/
  2  t1.id, t2.id, t1.ora_archive_state
  3  FROM t1
  4  RIGHT JOIN t2 ON (t1.ID = t2.ID);
        ID         ID ORA_ARCHIV
---------- ---------- ----------
         1          1 0
         3          3 0
         5          5 0
                    2
                    4
SQL> -- ORA_ARCHIVE_STATE does look correct now
SQL> -- Inner join
SQL> SELECT /*+gather_plan_statistics indbarch3*/ t1.id, t2.id, t1.ora_archive_state
  2  FROM t2
  3  JOIN t1 ON (t1.ID = t2.ID);
        ID         ID ORA_ARCHIV
---------- ---------- ----------
         1          1 0
         3          3 0
         5          5 0

SQL> SELECT sql_id, sql_text
  2  FROM V$SQL
  3  WHERE SQL_FULLTEXT LIKE '%indbarch3%';
SQL_ID        SQL_TEXT
------------- ----------------------------------------------------------------------------------------------------
90jbchxr3rgqh SELECT /*+gather_plan_statistics indbarch3*/ t1.id, t2.id, t1.ora_archive_state FROM t2 JOIN t1 ON (
              t1.ID = t2.ID)

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('90jbchxr3rgqh',NULL,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  90jbchxr3rgqh, child number 0
-------------------------------------

SELECT /*+gather_plan_statistics indbarch3*/ t1.id, t2.id,
t1.ora_archive_state FROM t2 JOIN t1 ON (t1.ID = t2.ID)
Plan hash value: 1838229974
----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      3 |00:00:00.01 |      47 |       |       |          |
|*  1 |  HASH JOIN         |      |      1 |      3 |      3 |00:00:00.01 |      47 |  1190K|  1190K|  599K (0)|
|*  2 |   TABLE ACCESS FULL| T1   |      1 |      3 |      3 |00:00:00.01 |       5 |       |       |          |
|   3 |   TABLE ACCESS FULL| T2   |      1 |      5 |      5 |00:00:00.01 |       9 |       |       |          |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."ID"="T2"."ID")
   2 - filter("T1"."ORA_ARCHIVE_STATE"='0')
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold

27 rows selected.

SQL> DROP TABLE t1;
Table T1 dropped.

SQL> DROP TABLE t2;
Table T2 dropped.