SELECT SCH.NAME AS SCHEMA_NAME
,TAB.NAME AS TABLE_NAME
,IND.NAME AS INDEX_NAME
,PK.NAME AS CONS_NAME
,DECODE(CON.TYPE$,'P','PRIMARY KEY','U','UNIQUE INDEX',CON.TYPE$) AS CONS_TYPE
,CASEWHEN BITAND(IDXINFO.XTYPE,0x01) =0x00THEN'Y'ENDAS ISCLUSTER
,IDXINFO.ISUNIQUE
FROM SYSOBJECTS TAB
JOIN SYSOBJECTS SCH ON TAB.SCHID = SCH.ID
JOIN SYSOBJECTS IND ON IND.PID = TAB.ID AND IND.SUBTYPE$ ='INDEX'JOIN SYSINDEXES IDXINFO ON IDXINFO.ID = IND.ID
LEFTJOIN SYSCONS CON ON CON.INDEXID = IND.ID
LEFTJOIN SYSOBJECTS PK ON PK.ID = CON.ID
WHERE TAB.SUBTYPE$ ='UTAB'AND SCH.NAME ='TA60ACCO1'AND TAB.NAME ='TBCLIENT1'
这张表的逐句 PK_CLIENT1 是个非簇集主键,在达梦库里,PK_CLIENT1 这个名字是作为主键约束名记录的,而主键对应的索引名是个系统生成的名称。
在 DBA_INDEXES 里查到的那个UNIQUE索引应该就是对应主键约束的索引名。
在DBA_INDEXES 里查到的CLISTER索引,是为了存储这个表,系统自动维护的一个簇集索引,也就是表本身的数据是以一个索引形式存储的。
可以用下面这个SQL查一下看看
SELECT SCH.NAME AS SCHEMA_NAME ,TAB.NAME AS TABLE_NAME ,IND.NAME AS INDEX_NAME ,PK.NAME AS CONS_NAME ,DECODE(CON.TYPE$,'P','PRIMARY KEY','U','UNIQUE INDEX',CON.TYPE$) AS CONS_TYPE ,CASE WHEN BITAND(IDXINFO.XTYPE,0x01) = 0x00 THEN 'Y' END AS ISCLUSTER ,IDXINFO.ISUNIQUE FROM SYSOBJECTS TAB JOIN SYSOBJECTS SCH ON TAB.SCHID = SCH.ID JOIN SYSOBJECTS IND ON IND.PID = TAB.ID AND IND.SUBTYPE$ = 'INDEX' JOIN SYSINDEXES IDXINFO ON IDXINFO.ID = IND.ID LEFT JOIN SYSCONS CON ON CON.INDEXID = IND.ID LEFT JOIN SYSOBJECTS PK ON PK.ID = CON.ID WHERE TAB.SUBTYPE$ = 'UTAB' AND SCH.NAME = 'TA60ACCO1' AND TAB.NAME = 'TBCLIENT1'