Trafodion 查看索引表结构

在Trafodion中，我们可以用showddl查看表结构，也可以查看到表上有哪些索引，如下

SQL>showddl test_index;CREATE TABLE TRAFODION.SEABASE.TEST_INDEX  (    A                                INT NO DEFAULT NOT NULL NOT DROPPABLE NOT      SERIALIZED  , B                                VARCHAR(10) CHARACTER SET ISO88591 COLLATE      DEFAULT DEFAULT NULL NOT SERIALIZED  , PRIMARY KEY (A ASC)  ) ATTRIBUTES ALIGNED FORMAT;CREATE INDEX IDX_TEST_INDEX ON TRAFODION.SEABASE.TEST_INDEX  (    B ASC  ) ATTRIBUTES ALIGNED FORMAT;

我们也知道，Trafodion中的索引是以一个独立的HBase表实现的，即如果一个主表上有2个Index，在HBase Shell中我们可以查看到三个表。通过showddl table_name虽然可以看到索引是在建立在哪些字段上面，但仍然无法知悉对应的索引表的组织结构（如索引表的主键是什么，包含哪些字段，是否有分区等），这可以通过以下两种方式实现，

1 invoke table_name
如下例子，使用invoke可以看到索引表有哪些字段，索引表的主键等信息。

//非分区表SQL>invoke test_index;-- Definition of Trafodion table TRAFODION.SEABASE.TEST_INDEX-- Definition current  Tue Oct 10 14:46:40 2017  (    A                                INT NO DEFAULT NOT NULL NOT DROPPABLE NOT      SERIALIZED  , B                                VARCHAR(10) CHARACTER SET ISO88591 COLLATE      DEFAULT DEFAULT NULL NOT SERIALIZED  )  PRIMARY KEY (A ASC)-- Definition of Trafodion index IDX_TEST_INDEX-- Definition current  Tue Oct 10 14:46:40 2017  (    B                                VARCHAR(10) CHARACTER SET ISO88591 COLLATE      DEFAULT DEFAULT NULL NOT SERIALIZED  , A                                INT NO DEFAULT NOT NULL NOT DROPPABLE NOT      SERIALIZED  )  PRIMARY KEY  (    B ASC  , A ASC  ) ATTRIBUTES ALIGNED FORMAT--- SQL operation complete.//分区表SQL>invoke test_idx_salted;-- Definition of Trafodion table TRAFODION.SEABASE.TEST_IDX_SALTED-- Definition current  Tue Oct 10 14:54:48 2017  (    A                                INT NO DEFAULT NOT NULL NOT DROPPABLE NOT      SERIALIZED  , B                                VARCHAR(10) CHARACTER SET ISO88591 COLLATE      DEFAULT DEFAULT NULL NOT SERIALIZED  , "_SALT_"                         INT UNSIGNED NO DEFAULT NOT NULL NOT      DROPPABLE NOT SERIALIZED  )  PRIMARY KEY ("_SALT_" ASC, A ASC)-- Definition of Trafodion index IDX_TEST_IDX-- Definition current  Tue Oct 10 14:54:48 2017  (    "_SALT_"                         INT UNSIGNED NO DEFAULT NOT NULL NOT      DROPPABLE NOT SERIALIZED  , B                                VARCHAR(10) CHARACTER SET ISO88591 COLLATE      DEFAULT DEFAULT NULL NOT SERIALIZED  , A                                INT NO DEFAULT NOT NULL NOT DROPPABLE NOT      SERIALIZED  )  PRIMARY KEY  (    "_SALT_" ASC  , B ASC  , A ASC  ) ATTRIBUTES ALIGNED FORMAT--- SQL operation complete.

2 showddl/invoke table(index_table index_name)
如下例子，使用showddl table(index_table index_name)的方式可以查看索引表的结构，不过默认情况下这个功能是关闭着的，需要先set parserflags 1打开此功能。

SQL>set parserflags 1;--- SQL operation complete.//非分区表SQL>showddl table(index_table idx_test_index);CREATE TABLE TRAFODION.SEABASE.IDX_TEST_INDEX  (    "B@"                             VARCHAR(10) CHARACTER SET ISO88591 COLLATE      DEFAULT NO DEFAULT NOT SERIALIZED  , A                                INT NO DEFAULT NOT NULL NOT DROPPABLE NOT      SERIALIZED  , PRIMARY KEY ("B@" ASC, A ASC)  ) ATTRIBUTES ALIGNED FORMAT;SQL>invoke table(index_table idx_test_index);-- Definition of Trafodion table TRAFODION.SEABASE.IDX_TEST_INDEX-- Definition current  Tue Oct 10 14:57:14 2017  (    "B@"                             VARCHAR(10) CHARACTER SET ISO88591 COLLATE      DEFAULT NO DEFAULT NOT SERIALIZED  , A                                INT NO DEFAULT NOT NULL NOT DROPPABLE NOT      SERIALIZED  )  PRIMARY KEY ("B@" ASC, A ASC)--- SQL operation complete.//分区表SQL>showddl table(index_table idx_test_idx);CREATE TABLE TRAFODION.SEABASE.IDX_TEST_IDX  (    "_SALT_@"                        INT UNSIGNED NO DEFAULT NOT NULL NOT      DROPPABLE NOT SERIALIZED  , "B@"                             VARCHAR(10) CHARACTER SET ISO88591 COLLATE      DEFAULT NO DEFAULT NOT SERIALIZED  , A                                INT NO DEFAULT NOT NULL NOT DROPPABLE NOT      SERIALIZED  , PRIMARY KEY ("_SALT_@" ASC, "B@" ASC, A ASC)  ) ATTRIBUTES ALIGNED FORMAT;SQL>invoke table(index_table idx_test_idx);-- Definition of Trafodion table TRAFODION.SEABASE.IDX_TEST_IDX-- Definition current  Tue Oct 10 14:56:31 2017  (    "_SALT_@"                        INT UNSIGNED NO DEFAULT NOT NULL NOT      DROPPABLE NOT SERIALIZED  , "B@"                             VARCHAR(10) CHARACTER SET ISO88591 COLLATE      DEFAULT NO DEFAULT NOT SERIALIZED  , A                                INT NO DEFAULT NOT NULL NOT DROPPABLE NOT      SERIALIZED  )  PRIMARY KEY ("_SALT_@" ASC, "B@" ASC, A ASC)--- SQL operation complete.