[jira] [Commented] (TRAFODION-1914) optimize "added columns" in indexes
[ https://issues.apache.org/jira/browse/TRAFODION-1914?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15289114#comment-15289114 ] liu ming commented on TRAFODION-1914: - yes, I see. It is like 'covered index'. A new syntax should be invented. unique index cannot support this. this must be added as a new feature, yes, it makes sense. thanks, Ming > optimize "added columns" in indexes > --- > > Key: TRAFODION-1914 > URL: https://issues.apache.org/jira/browse/TRAFODION-1914 > Project: Apache Trafodion > Issue Type: Improvement > Components: sql-cmp >Reporter: Eric Owhadi > > the current CREATE INDEX feature will always put each column added to the > index in the clustering key. But sometimes, users just want to add columns to > the index to avoid having to probe back the primary table to fetch just one > or 2 column back. Instead copying these columns in the index can avoid making > a probe back to main table and therefore improve performance. Current > implementation allows this, but will always put the extra column as part of > the clustering key. That is not optimal, and very bad for the case of > VARCHAR, since they are exploded to there max size when part of the > clustering key. So this JIRA is abount altering the syntax of create index, > and flag columns that are added but should not be part of the clustering key. -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (TRAFODION-1914) optimize "added columns" in indexes
[ https://issues.apache.org/jira/browse/TRAFODION-1914?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15289108#comment-15289108 ] Eric Owhadi commented on TRAFODION-1914: Hi Ming, No it is different thing. In my JIRA, there is no limitation of it being only for unique index. Hope that makes sense, Eric > optimize "added columns" in indexes > --- > > Key: TRAFODION-1914 > URL: https://issues.apache.org/jira/browse/TRAFODION-1914 > Project: Apache Trafodion > Issue Type: Improvement > Components: sql-cmp >Reporter: Eric Owhadi > > the current CREATE INDEX feature will always put each column added to the > index in the clustering key. But sometimes, users just want to add columns to > the index to avoid having to probe back the primary table to fetch just one > or 2 column back. Instead copying these columns in the index can avoid making > a probe back to main table and therefore improve performance. Current > implementation allows this, but will always put the extra column as part of > the clustering key. That is not optimal, and very bad for the case of > VARCHAR, since they are exploded to there max size when part of the > clustering key. So this JIRA is abount altering the syntax of create index, > and flag columns that are added but should not be part of the clustering key. -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (TRAFODION-1914) optimize "added columns" in indexes
[ https://issues.apache.org/jira/browse/TRAFODION-1914?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15289076#comment-15289076 ] liu ming commented on TRAFODION-1914: - >>create table ut1 ( c1 int not null, c2 int not null, primary key (c1) ); --- SQL operation complete. >>insert into ut1 Values(1,1); --- 1 row(s) inserted. >>create unique index ui1 on ut1(c2); --- SQL operation complete. >>exit then use hbase shell hbase(main):002:0> scan 'TRAFODION.SEABASE.UI1' ROWCOLUMN+CELL \x80\x00\x00\x01 column=#1:\x01, timestamp=1463582407513, value=\x08\x00\x00\x00\x00\x00\x00\x00\x01\x00\x00\x00\x01\x00\x00\x00 1 row(s) in 0.0800 seconds > optimize "added columns" in indexes > --- > > Key: TRAFODION-1914 > URL: https://issues.apache.org/jira/browse/TRAFODION-1914 > Project: Apache Trafodion > Issue Type: Improvement > Components: sql-cmp >Reporter: Eric Owhadi > > the current CREATE INDEX feature will always put each column added to the > index in the clustering key. But sometimes, users just want to add columns to > the index to avoid having to probe back the primary table to fetch just one > or 2 column back. Instead copying these columns in the index can avoid making > a probe back to main table and therefore improve performance. Current > implementation allows this, but will always put the extra column as part of > the clustering key. That is not optimal, and very bad for the case of > VARCHAR, since they are exploded to there max size when part of the > clustering key. So this JIRA is abount altering the syntax of create index, > and flag columns that are added but should not be part of the clustering key. -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (TRAFODION-1914) optimize "added columns" in indexes
[ https://issues.apache.org/jira/browse/TRAFODION-1914?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15289075#comment-15289075 ] liu ming commented on TRAFODION-1914: - Hi, Eric, I filed JIRA 1976 Trafodion support to create 'unique index'. In that case, one should make sure the index column value will be unique. Then that index will not have primary key list into clustering key. This is already supported but not documented. So this seems like a solved issue? > optimize "added columns" in indexes > --- > > Key: TRAFODION-1914 > URL: https://issues.apache.org/jira/browse/TRAFODION-1914 > Project: Apache Trafodion > Issue Type: Improvement > Components: sql-cmp >Reporter: Eric Owhadi > > the current CREATE INDEX feature will always put each column added to the > index in the clustering key. But sometimes, users just want to add columns to > the index to avoid having to probe back the primary table to fetch just one > or 2 column back. Instead copying these columns in the index can avoid making > a probe back to main table and therefore improve performance. Current > implementation allows this, but will always put the extra column as part of > the clustering key. That is not optimal, and very bad for the case of > VARCHAR, since they are exploded to there max size when part of the > clustering key. So this JIRA is abount altering the syntax of create index, > and flag columns that are added but should not be part of the clustering key. -- This message was sent by Atlassian JIRA (v6.3.4#6332)
