注意: 达梦系统表中的数据是大写的, 包括Schema/索引/表名, 所以在查询表和索引时需要转换为大写来进行查询

• 统计索引 (SYS.DBA_INDEXES,DBA_SEGMENTS )

-- 统计索引大小(包含表名)
SELECT  se.bytes/1024/1024 as index_mb, table_name,NUM_ROWS,index_name FROM DBA_SEGMENTS se,
SYS.DBA_INDEXES ind where se.segment_name=ind.index_name
and se.OWNER='XX' AND se.SEGMENT_TYPE='INDEX' 
and  ind.OWNER='XX' and ind.NUM_ROWS>0 order by se.bytes desc limit
 30;

• 统计表大小(DBA_SEGMENTS)

SELECT segment_name,bytes/1024/1024 FROM DBA_SEGMENTS where OWNER='XX' AND SEGMENT_TYPE='TABLE'   order by BYTES desc limit 30;

• 查看某个表下有那些索引

select table_name,index_name,column_name,column_position from dba_ind_columns  where table_owner='XX'   and TABLE_NAME='AA';

• BEIGIN/DECLARE语法, 其实达梦支持create index IF NOT EXISTS和drop index IF EXISTS的语法, 去达梦的连接工具能成功执行sql, 但是集成到SpringBoot中Flyway可能会执行失败(应该和版本有关), 下面这种用法更为通用

DECLARE
trigger_cnt1 INT;
trigger_cnt2 INT;
BEGIN
    SELECT COUNT(*) INTO trigger_cnt1 FROM SYS.DBA_INDEXES where INDEX_NAME='TRIGGER_TEMPLATE_CMDB_INDEX';
    SELECT COUNT(*) INTO trigger_cnt2 FROM SYS.DBA_INDEXES where INDEX_NAME='TRIGGER_TEMPLATE_INDEX';
IF trigger_cnt1 = 0 THEN
        EXECUTE IMMEDIATE 'create index TRIGGER_TEMPLATE_CMDB_INDEX on alarm_trigger(template,alarm_template_id,template_id,has_change,cmdb_id);';
END IF;
IF trigger_cnt2 > 0 THEN
    EXECUTE IMMEDIATE  'drop index TRIGGER_TEMPLATE_INDEX;';
END IF;
END;

Flaway报错-Unable to parse statement in db xxx