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.