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