存储架构 CNOUG (源链)

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 (the effect does appear on earlier versions):

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

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

; the last two will have the

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


which shows up when I query


        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

-------------------- ---------- -------------------- ----------------- --------------------
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


has acquired two columns – a hidden column (which physically exists as the second column in the stored data and is declared as

) 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()

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.


On using scriptlets in JSP This question already has an answer here: How to avoid Java code in JSP files? 26 answers I am new to JSP. I created a web applic...
laravel config cache cache.php default 注释翻译: /* |-------------------------------------------------------------------------- | Default Cache Store |-...
The Power of a Graph Database Recently, LogicGate’s engineering team undertook the effort to migrate our application from a relational database to a graph datab...
告诉你为何以及如何搭建一个私有的npm仓库... 作者:百度外卖-王丝雨@阿不思 百度外卖-郑傲@慢城小ZA 百度外卖-汪梦@daisy 转载请标明出处 为何需要搭建私有npm仓库? npm——我们大家都知道是NodeJS的包管理工具,用于Node插件的管理包括安装、卸载、管理依赖等。 基于npm命令行我们...
Serverless Databases: The Future of Event-Driven A... In the past few months, we have witnessed interesting things in the ecosystem of serverless technologies . Many organizations are adopting serverless...
CNOUG责编内容来自:CNOUG (源链) | 更多关于

本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。
酷辣虫 » nvarchar2

专业 x 专注 x 聚合 x 分享 CC BY-NC-SA 4.0

使用声明 | 英豪名录