[20190531]Timestamp Oddity.txt

[20190531]Timestamp Oddity.txt --//链接：https://jonathanlewis.wordpress.com/2019/05/29/timestamp-oddity/ --//重复测试： 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 --//很奇怪的字段类型,建立唯一索引,也就是保存的值可能存在重复吗? --//链接：https://jonathanlewis.wordpress.com/2019/05/29/timestamp-oddity/ 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 --//同样!!