MERGE and invisible columns = invisible documentation?

Oracle 12c introduced “invisible columns” to help us add columns to tables without breaking existing applications. The documentation explains how they work with SELECT and INSERT, but not MERGE. Here’s what happened when I tried MERGE.

Before: visible columns, existing SQL

Here is some simple test data with two tables. The source table will be used in the MERGE statement.

create table T_TARGET as
select 1 PK, 0 VALUE_VISIBLE from DUAL;

create table T_SOURCE as
select 1 PK, 1 VALUE_VISIBLE from DUAL
union all
select 2 PK, 2 VALUE_VISIBLE from DUAL;

Now some simple SQL just to show how things work without invisible columns. These are examples of how not to code:

  • select * is a bad practice: we should list explicitly the columns we want back.
  • insert into should also have an explicit list of columns within parentheses, just before the values() clause.
  • the insert part of the MERGE statement should also have an explicit list of columns.

If we all followed the good practice of listing columns explicitly, there would be no need for invisible columns!

> select * from T_TARGET

        PK VALUE_VISIBLE
---------- -------------
         1             0 

> insert into T_TARGET values(2,0)
1 rows inserted.

> select * from T_TARGET

        PK VALUE_VISIBLE
---------- -------------
         1             0
         2             0 

> rollback
rollback complete.

> merge into T_TARGET O
using T_SOURCE N
on (O.PK = N.PK)
when matched then update
  set VALUE_VISIBLE = N.VALUE_VISIBLE
when not matched then insert values(N.PK, N.VALUE_VISIBLE)

2 rows merged.

> select * from T_TARGET

        PK VALUE_VISIBLE
---------- -------------
         1             1
         2             2 

> rollback
rollback complete.

After: invisible column, existing SQL

When I add an invisible column, then run the same three statements, I get exactly the same results, even for the MERGE.

> alter table T_TARGET add VALUE_INVISIBLE number INVISIBLE
table T_TARGET altered.

> alter table T_SOURCE add VALUE_INVISIBLE number INVISIBLE
table T_SOURCE altered.

> update T_SOURCE set VALUE_INVISIBLE = VALUE_VISIBLE
2 rows updated.

> commit
committed.
> select * from T_TARGET

        PK VALUE_VISIBLE
---------- -------------
         1             0 

> insert into T_TARGET values(2,0)
1 rows inserted.

> select * from T_TARGET

        PK VALUE_VISIBLE
---------- -------------
         1             0
         2             0 

> rollback
rollback complete.

> merge into T_TARGET O
using T_SOURCE N
on (O.PK = N.PK)
when matched then update
  set VALUE_VISIBLE = N.VALUE_VISIBLE
when not matched then insert values(N.PK, N.VALUE_VISIBLE)

2 rows merged.

> select * from T_TARGET

        PK VALUE_VISIBLE
---------- -------------
         1             1
         2             2 

> rollback
rollback complete.

After: accessing the invisible column

The “old” SQL statements worked exactly as before. The only thing to watch out for is that the new column will be null in any newly inserted rows.

Now let’s change the SQL to work with the new invisible column. As far as SELECT and INSERT are concerned, the documentation says to just list all the columns.

> select PK, VALUE_VISIBLE, VALUE_INVISIBLE from T_TARGET

        PK VALUE_VISIBLE VALUE_INVISIBLE
---------- ------------- ---------------
         1             0                 

> insert into T_TARGET (PK, VALUE_VISIBLE, VALUE_INVISIBLE)
  values(2,0,0)
1 rows inserted.

> select PK, VALUE_VISIBLE, VALUE_INVISIBLE from T_TARGET
        PK VALUE_VISIBLE VALUE_INVISIBLE
---------- ------------- ---------------
         1             0
         2             0               0 

> rollback
rollback complete.

All right, now how do I make the MERGE work? I suppose I just have to list the columns in the insert part:

> merge into T_TARGET O
using T_SOURCE N
on (O.PK = N.PK)
when matched then update set
  VALUE_VISIBLE = N.VALUE_VISIBLE,
  VALUE_INVISIBLE = N.VALUE_INVISIBLE
when not matched then insert
  (PK, VALUE_VISIBLE, VALUE_INVISIBLE)
  values(N.PK, N.VALUE_VISIBLE, N.VALUE_INVISIBLE)

...
Error at Command Line : 9 Column : 33
Error report -
SQL Error: ORA-00904: "N"."VALUE_INVISIBLE": invalid identifier

Oops! It took me awhile to realize that “using T_SOURCE” was the same as “using (select * from T_SOURCE)“! I can’t just refer to the table anymore.

> merge into T_TARGET O
using (
  select PK, VALUE_VISIBLE, VALUE_INVISIBLE from T_SOURCE
) N
on (O.PK = N.PK)
when matched then update set
  VALUE_VISIBLE = N.VALUE_VISIBLE,
  VALUE_INVISIBLE = N.VALUE_INVISIBLE
when not matched then insert
  (PK, VALUE_VISIBLE, VALUE_INVISIBLE)
  values(N.PK, N.VALUE_VISIBLE, N.VALUE_INVISIBLE)

2 rows merged.

> rollback
rollback complete.

Victory! but my work is not done: I don’t like this MERGE statement because I may update rows that don’t need updating. To make sure this doesn’t happen, I’m going to add a where clause.

> merge into T_TARGET O
using (
  select PK, VALUE_VISIBLE, VALUE_INVISIBLE from T_SOURCE
) N
on (O.PK = N.PK)
when matched then update set
  VALUE_VISIBLE = N.VALUE_VISIBLE,
  VALUE_INVISIBLE = N.VALUE_INVISIBLE
  where 1 in (
    DECODE(O.VALUE_VISIBLE,  N.VALUE_VISIBLE,  0,1),
    DECODE(O.VALUE_INVISIBLE,N.VALUE_INVISIBLE,0,1)
  )
when not matched then insert
  (PK, VALUE_VISIBLE, VALUE_INVISIBLE)
  values(N.PK, N.VALUE_VISIBLE, N.VALUE_INVISIBLE)
...
Error at Command Line : 11 Column : 12
Error report -
SQL Error: ORA-00904: "O"."VALUE_INVISIBLE": invalid identifier

This is one of the stranger results I have seen from a SQL statement. I was able to update the VALUE_INVISIBLE column before, but I can’t refer to it here? I finally tried the same trick as for the using clause:

> merge into (
  select PK, VALUE_VISIBLE, VALUE_INVISIBLE from T_TARGET
) O
using (
  select PK, VALUE_VISIBLE, VALUE_INVISIBLE from T_SOURCE
) N
on (O.PK = N.PK)
when matched then update set
  VALUE_VISIBLE = N.VALUE_VISIBLE,
  VALUE_INVISIBLE = N.VALUE_INVISIBLE
  where 1 in (
    DECODE(O.VALUE_VISIBLE,  N.VALUE_VISIBLE,  0,1),
    DECODE(O.VALUE_INVISIBLE,N.VALUE_INVISIBLE,0,1)
  )
when not matched then insert
  (PK, VALUE_VISIBLE, VALUE_INVISIBLE)
  values(N.PK, N.VALUE_VISIBLE, N.VALUE_INVISIBLE)

2 rows merged.

Conclusion

The MERGE starts with the equivalent of a SELECT...FROM <target> RIGHT OUTER JOIN <source>. Any columns we read later on must be included in that SELECT. We read the source columns all over the place, and we may also read the target columns in WHERE clauses. If we need to read invisible columns, they must be explicitly named by using a view or a subquery.

[Update 2015-01-26: After on discussion on OTN, the use of a subquery may be OK.]

Notice I use a subquery in the INTO clause. The documentation on MERGE talks specifically about tables and views, but an “inline view” should be OK. I have opened an SR with Oracle Support to make sure. See my question on OTN for details.

Advertisements

2 thoughts on “MERGE and invisible columns = invisible documentation?

  1. Stew,

    Another interesting article.

    I assumed that you could merge into a “key preserved” view just like an UPDATE statement. But when I tried a key preserved join that on 11.2.0.4 I got:

    ORA-38106: MERGE not supported on join view or view with INSTEAD OF trigger.

    • Tony,

      Thanks for your kind words. MERGE does have some quirks, doesn’t it? I may blog soon about my adventures trying to merge into remote tables with invisible columns: more surprising results…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s