You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@trafodion.apache.org by "Liu, Ming (Ming)" <mi...@esgyn.cn> on 2017/06/01 06:43:19 UTC
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
RE: does trafodion have null value in the index?
Posted by Anoop Sharma <an...@esgyn.com>.
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:rohit.jain@esgyn.com]
Sent: Thursday, June 1, 2017 8:57 AM
To: user@trafodion.incubator.apache.org
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:anoop.sharma@esgyn.com]
Sent: Thursday, June 1, 2017 10:04 AM
To: user@trafodion.incubator.apache.org<ma...@trafodion.incubator.apache.org>
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:ming.liu@esgyn.cn]
Sent: Wednesday, May 31, 2017 11:43 PM
To: user@trafodion.incubator.apache.org<ma...@trafodion.incubator.apache.org>
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
RE: does trafodion have null value in the index?
Posted by Rohit Jain <ro...@esgyn.com>.
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:anoop.sharma@esgyn.com]
Sent: Thursday, June 1, 2017 10:04 AM
To: user@trafodion.incubator.apache.org
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:ming.liu@esgyn.cn]
Sent: Wednesday, May 31, 2017 11:43 PM
To: user@trafodion.incubator.apache.org<ma...@trafodion.incubator.apache.org>
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
RE: does trafodion have null value in the index?
Posted by Anoop Sharma <an...@esgyn.com>.
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:ming.liu@esgyn.cn]
Sent: Wednesday, May 31, 2017 11:43 PM
To: user@trafodion.incubator.apache.org
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