Trafodion 查看表上索引及索引字段信息

昨天有人问到怎么通过SQL的方式查看表上有哪些索引、每个索引上有哪些字段，通过SQL的方式，意味着我们得需要直接从元数据表中查询。
Trafodion将所有的对象（表、索引、字段等）元数据信息保存到”MD“这个Schema下，每个对象都需要唯一的对象ID（OBJECT_ID），通过OBJECT_ID，可以在不同的元数据表中查询需要的信息。

下面这条SQL语句可以帮助我们查看所有表及表上的索引及索引字段信息，

select a.schema_name,a.object_name as table_name, c.object_name as index_name, replace(d.column_name,'@','') as  column_name
  from trafodion."_MD_".objects a, trafodion."_MD_".indexes b, trafodion."_MD_".objects c, trafodion."_MD_".columns d
 where a.object_uid = b.base_table_uid
   and b.index_uid = c.object_uid
   and c.object_uid = d.object_uid
   and a.schema_name not like '|_HV|_%%|_' escape '|'
   and a.schema_name not like '|_HB|_%%|_' escape '|'
   and a.schema_name not in ('_MD_','_PRIVMGR_MD_','_REPOS_')
   and a.schema_name not like 'VOLATILE_SCHEMA_%%'
   and d.column_name not in ('SYSKEY','_SALT_','_DIVISION_1_')
order by 1,2,3;

样例输出结果如下，

SCHEMA_NAME                                                                                                                      TABLE_NAME                                                                                                                       INDEX_NAME                                                                                                                       COLUMN_NAME                                                                                                                     
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
SEABASE                                                                                                                          AAA                                                                                                                              IDX_AAA                                                                                                                          A                                                                                                                               
SEABASE                                                                                                                          AAA                                                                                                                              IDX_AAA_2                                                                                                                        A                                                                                                                               
SEABASE                                                                                                                          BBB                                                                                                                              IDX_BBB                                                                                                                          B                                                                                                                               
SEABASE                                                                                                                          BBB                                                                                                                              IDX_BBB_1                                                                                                                        A                                                                                                                               
SEABASE                                                                                                                          BBB                                                                                                                              IDX_BBB_1                                                                                                                        B                                                                                                                               

--- 5 row(s) selected.