nullable base table primary key columns are not yet enabled by default in trafodion.
And yes, index uniqueness of an inserted row is across the whole key with single or multiple columns. anoop From: Rohit Jain [mailto:[email protected]] Sent: Thursday, June 1, 2017 8:57 AM To: [email protected] Subject: RE: does trafodion have null value in the index? Anoop, I would assume from that that if it is a unique multi-column secondary index, that NULLs in the same column across rows is acceptable, as long as the combination of all the values that make up the index key are unique. So, NULL is in essence treated like it were a value, though the semantics supported treat it like a NULL. I would also assume that this also holds for the Primary Key column(s) of the base table. That it also can accept NULL values following the same rules as for a UNIQE index. At one point we did not accept any NULL columns in the Primary Key based on ANSI rules from what I understand. Rohit From: Anoop Sharma [mailto:[email protected]] Sent: Thursday, June 1, 2017 10:04 AM To: [email protected]<mailto:[email protected]> Subject: RE: does trafodion have null value in the index? traf indexes can be created on nullable columns and they can contain null values. If it is a unique index, then only one null value can be inserted. If it is a non-unique index, then multiple null values can be inserted. both 'is null' and 'is not null' preds can be applied on index scans. See examples below: >>create table t (a int not null primary key, b int); --- SQL operation complete. >>create index ti on t(b); --- SQL operation complete. >>insert into t values (1,null), (2, 2), (3, null); --- 3 row(s) inserted. >>explain options 'f' select b from t; LC RC OP OPERATOR OPT DESCRIPTION CARD ---- ---- ---- -------------------- -------- -------------------- --------- 1 . 2 root 1.00E+002 . . 1 trafodion_index_scan TI 1.00E+002 --- SQL operation complete. >>select b from t; B ----------- 2 ? ? --- 3 row(s) selected. >>select b from t where b is null; B ----------- ? ? --- 2 row(s) selected. >>explain options 'f' select b from t where b is null; LC RC OP OPERATOR OPT DESCRIPTION CARD ---- ---- ---- -------------------- -------- -------------------- --------- 1 . 2 root 1.00E+000 . . 1 trafodion_index_scan TI 1.00E+000 --- SQL operation complete. >>select b from t where b is not null; B ----------- 2 --- 1 row(s) selected. >> >>create unique index ti2 on t(b); *** ERROR[8110] Duplicate rows detected. *** ERROR[1053] Unique index TRAFODION.SCH.TI2 could not be created because the specified column(s) contain duplicate data. --- SQL operation failed with errors. >>delete from t; --- 3 row(s) deleted. >>insert into t values (1,null); --- 1 row(s) inserted. >> >>create unique index ti2 on t(b); --- SQL operation complete. >>select b from t; B ----------- ? --- 1 row(s) selected. >> From: Liu, Ming (Ming) [mailto:[email protected]] Sent: Wednesday, May 31, 2017 11:43 PM To: [email protected]<mailto:[email protected]> Subject: does trafodion have null value in the index? Hi, all, I heard that database index cannot save null value, so if the predicate contains 'is null' 'is not null', then the index will not be used. Is this true for Trafodion as well? thanks, Ming
