Oracle Scratchpad

December 30, 2017

nvarchar2

Filed under: Infrastructure,Oracle — Jonathan Lewis @ 12:08 pm BST Dec 30,2017

Here’s an odd little quirk that appeared when I was playing around with default values just recently. I think it’s one I’ve seen before, I may even have written about it many years ago but I can’t find any reference to it at present. Let’s start with a script that I’ll run on 12.2.0.1 (the effect does appear on earlier versions):


rem
rem     Script:         nvarchar2_anomaly.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Dec 2017
rem

create table t1 (
        column1  varchar2(10),
        column2  nvarchar2(10)
);

create table t2 (
        column1  varchar2(10)
);

alter table t2 add column2 nvarchar2(10);

create table t3 (
        column1  varchar2(10),
        column2  nvarchar2(10) default 'xxxxxxxx'
);

create table t4 (
        column1  varchar2(10)
);

alter table t4 add column2 nvarchar2(10) default 'xxxxxxxx';

insert into t1(column1) values('a');
insert into t2(column1) values('a');
insert into t3(column1) values('a');
insert into t4(column1) values('a');

All I’ve done it create 4 tables which. when described will all look the same:


 Name                    Null?    Type
 ----------------------- -------- ----------------
 COLUMN1                          VARCHAR2(10)
 COLUMN2                          NVARCHAR2(10)

There is a significant different between the first two and the last two, of course, thanks to the specification of a default value which means that the inserts will produce two possible results: the first two tables will have nulls in column2; the last two will have the nvarchar2 equivalent of ‘xxxxxxxx’ which, in my instance, will be a string of 16 bytes: “0,78,0,78,0,78,0,78,0,78,0,78,0,78,0,78”.

Surprisingly, though, there is a dramatic oddity between t3 and t4 which shows up when I query user_tab_cols:

select
        table_name, column_id, column_name,  segment_column_id, data_default
from    user_tab_cols
where   table_name like 'T_'
order by
        table_name, column_id
;

TABLE_NAME            COLUMN_ID COLUMN_NAME          SEGMENT_COLUMN_ID DATA_DEFAULT
-------------------- ---------- -------------------- ----------------- --------------------
T1                            1 COLUMN1                              1
                              2 COLUMN2                              2

T2                            1 COLUMN1                              1
                              2 COLUMN2                              2

T3                            1 COLUMN1                              1
                              2 COLUMN2                              2 'xxxxxxxx'

T4                            1 COLUMN1                              1
                              2 COLUMN2                              3 'xxxxxxxx'
                                SYS_NC00002$                         2

Table t4 has acquired two columns – a hidden column (which physically exists as the second column in the stored data and is declared as raw(126)) and the column which I had specified. You’ll note that the test shows two differences that may be significant: comparing t3/t4 we see that adding, rather than initially defining, the nvarchar2() column introduces the extra column; comparing t2/t4 we see that adding a varchar2() rather than an nvarchar2() doesn’t produce the same effect. Tentative assumption, therefore, is that there is something special about adding nvarchar2() columns. [Update: wrong, see comment 2 and the link it supplies]

Casting my mind back to various customers who have gone through multiple upgrades of 3rd party applications that invariably seem to add columns to tables, I wondered whether this extra column appeared every time you added an nvarchar2(). I’d not noticed anything in that past that suggested this might be the case, but it’s obviously worth checking: and in my simple tests it looked as if Oracle created just one extra column and used it to capture a value that seemed to be determined by the number and location of columns that had been added.

It’s a curiosity, and leaves room for further investigation – so if anyone has links to related articles please feel free to add them in the comments.

 

7 Comments »

  1. I have just executed your script on EE 11.2.0.2.0 Win x64 and I do not observe these additional ‘hidden’ columns. I am somewhat relieved given I have client production scripts to alert developers that DB production code and table definitions are out of sync with that under source control.

    Comment by Michael D O'Shea (@MichaelDOShea) — December 30, 2017 @ 12:43 pm BST Dec 30,2017 | Reply

    • Michael,

      Thanks for the feedback. Possibly it doesn’t appear until 12c – I’ve reproduced the behaviour in 12.1.0.2.

      That’s a good point about reports of differences between development and production systems. It does depend on which views and columns you use for comparison, of course: user_tab_cols shows the hidden columns, of course, while user_tab_columns doesn’t; and in this case the column_id matches even when the segment_column_id tells you something different.

      Comment by Jonathan Lewis — December 30, 2017 @ 1:09 pm BST Dec 30,2017 | Reply

  2. The difference between T2 and T4 is the presence of the default value, not varchar2 vs nvarchar2. This is about adding a new column with a default value, and this is not specific to nvarchar2, the outcome is the same with varchar2, number, date.

    http://www.oracle.com/technetwork/articles/database/ddl-optimizaton-in-odb12c-2331068.html
    Hidden Column “SYS_NC000XX$” Being Added When A New Column Is Added To The Table. (Doc ID 2277937.1)

    This behaviour can be disabled with:

    alter session set “_add_col_optim_enabled”=false ;

    Comment by Balazs Papp — December 30, 2017 @ 5:04 pm BST Dec 30,2017 | Reply

    • Balazs,

      Excellent.
      Thanks for the link.

      A case of stopping too soon before trying to find a flaw with my first hypothesis.

      Comment by Jonathan Lewis — December 30, 2017 @ 5:29 pm BST Dec 30,2017 | Reply

    • Hi Jonathan,

      This DDL optimization stuff caused quite a significant headache to our alter table exchange mexhanism, which we had been using for a long time for loading/unloading tables. The first was in 11.1 with the default not null optimization and the second came with 12.1, where oracle optimized even adding column with default value.
      It is quite tricky to create table for exhange, when the ddl optmization kicks in. For 11.1 feature is is sufficient to force table to be created with proper hakan factor by using event 14529 inside session before CTAS is issues.
      I’m not sure if it is documented for 12.1 feature now, but to create table with proper hidden columns is possible by setting event 14529, level 512. We were forced to file a SR for this on MOS. It took quite a long untill we received this workarroud.

      Pavol Babel
      OCM 10g/11g

      Comment by Pavol Babel — December 31, 2017 @ 12:59 pm BST Dec 31,2017 | Reply

      • Pavel,

        Thanks for the comments – that’s useful information for others to see.
        12.2 has “create table SIMPLE_TABLE for exchange with table PT_TABLE”, so that’s probably going to avoid the issue on the next upgrade.

        Comment by Jonathan Lewis — December 31, 2017 @ 5:46 pm BST Dec 31,2017 | Reply


RSS feed for comments on this post. TrackBack URI

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 )

Google+ photo

You are commenting using your Google+ 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 )

w

Connecting to %s

Powered by WordPress.com.