[20190531]Timestamp Oddity.txt

[20190531]Timestamp Oddity.txt

--//連結：

--//重複測試：

1.環境：

SCOTT@test01p> @ ver1

PORT_STRING                    VERSION        BANNER                                                                               CON_ID

------------------------------ -------------- -------------------------------------------------------------------------------- ----------

IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0

2.測試:

SCOTT@test01p> create table t1 ( ts_tz   timestamp(9) with time zone, ts_ltz  timestamp(9) with local time zone);

Table created.

SCOTT@test01p> insert into t1 values(systimestamp, systimestamp);

1 row created.

SCOTT@test01p> commit ;

Commit complete.

SCOTT@test01p> select * from t1;

TS_TZ                         TS_LTZ

----------------------------- -----------------------------

2019-05-30 20:18:47.954000000 2019-05-30 20:18:47.954000000

--//顯示一樣,估計和我的環境配置有關,不探究了.

SCOTT@test01p> alter table t1 add constraint ts_ltz_uk unique (ts_ltz);

Table altered.

SCOTT@test01p> alter table t1 add constraint ts_tz_uk  unique (ts_tz);

alter table t1 add constraint ts_tz_uk  unique (ts_tz)

                                        *

ERROR at line 1:

ORA-02329: column of datatype TIME/TIMESTAMP WITH TIME ZONE cannot be unique or a primary key

--//不能建立約束在local time zone的timestamp型別上.

SCOTT@test01p> create unique index ts_tz_uk on t1(ts_tz);

Index created.

--//這樣建立OK,但實際上建立函式索引.

SCOTT@test01p> select index_name, column_name from user_ind_columns where table_name = 'T1';

INDEX_NAME           COLUMN_NAME

-------------------- --------------------

TS_LTZ_UK            TS_LTZ

TS_TZ_UK             SYS_NC00003$

SCOTT@test01p> column COLUMN_EXPRESSION format a30

SCOTT@test01p> select * from user_ind_expressions where table_name = 'T1';

INDEX_NAME           TABLE_NAME           COLUMN_EXPRESSION              COLUMN_POSITION

-------------------- -------------------- ------------------------------ ---------------

TS_TZ_UK             T1                   SYS_EXTRACT_UTC("TS_TZ")                     1

--//很奇怪的欄位型別,建立唯一索引,也就是儲存的值可能存在重複嗎?

--//連結：

Oracle has silently invoked the sys_extract_utc() function on our (free-floating) timestamp column to normalize it to

UTC. This is really not very friendly but it does make sense, of course – it would be rather expensive to enforce

uniqueness if there were (at least) 24 different ways of storing the same absolute value – and 24 is a conservative

estimate.

--//甲骨文已經默默地呼叫sys_extract_utc()函式在我們(自由浮動)時間戳列規範化UTC。這是真的不是很友好但它確實有意義,當然,這

--//將是相當昂貴的執行獨特性如果有(至少)24種不同的方式儲存相同的絕對值,24是保守的估計。

--//上班在11.2.0.4上重複測試:

SCOTT@book> @ ver1

PORT_STRING                    VERSION        BANNER

------------------------------ -------------- --------------------------------------------------------------------------------

x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

create table t1 ( ts_tz   timestamp(9) with time zone, ts_ltz  timestamp(9) with local time zone);

insert into t1 values(systimestamp, systimestamp);

commit ;

select * from t1;

SCOTT@book> select * from t1;

TS_TZ                                TS_LTZ

------------------------------------ -----------------------------

2019-05-31 08:40:14.003461000 +08:00 2019-05-31 08:40:14.003461000

SCOTT@book> select dump(ts_tz,16) c50 ,dump(TS_LTZ,16) c50 from t1;

C50                                                C50

-------------------------------------------------- --------------------------------------------------

Typ=181 Len=13: 78,77,5,1f,1,29,f,0,34,cf,88,1c,3c Typ=231 Len=11: 78,77,5,1f,1,29,f,0,34,cf,88

SCOTT@book> alter table t1 add constraint ts_ltz_uk unique (ts_ltz);

Table altered.

SCOTT@book> alter table t1 add constraint ts_tz_uk  unique (ts_tz);

alter table t1 add constraint ts_tz_uk  unique (ts_tz)

                                        *

ERROR at line 1:

ORA-02329: column of datatype TIME/TIMESTAMP WITH TIME ZONE cannot be unique or a primary key

--//不能建立約束在local time zone的timestamp型別上.

SCOTT@book> create unique index ts_tz_uk on t1(ts_tz);

Index created.

SCOTT@book> select index_name, column_name from user_ind_columns where table_name = 'T1';

INDEX_NAME                     COLUMN_NAME

------------------------------ --------------------

TS_LTZ_UK                      TS_LTZ

TS_TZ_UK                       SYS_NC00003$

SCOTT@book> column COLUMN_EXPRESSION format a30

SCOTT@book> select * from user_ind_expressions where table_name = 'T1';

INDEX_NAME                     TABLE_NAME COLUMN_EXPRESSION              COLUMN_POSITION

------------------------------ ---------- ------------------------------ ---------------

TS_TZ_UK                       T1         SYS_EXTRACT_UTC("TS_TZ")                     1

--//同樣!!