DM系统视图获取多层级分区表信息问题
为提高效率，提问时请提供以下信息，问题描述清晰可优先响应。
【DM版本】:
【操作系统】：
【CPU】:
【问题描述】*：
表DDL语句如下：
CREATE TABLE "STUDENTS"."PHONEDETAIL4"
(
"NAME" VARCHAR(50),
"SEX" VARCHAR(50),
"XIAOFEI" INT,
"PROVINCE" VARCHAR(50),
"CITY" VARCHAR(50),
"DISTRICT" VARCHAR(50),
"XIAOFEI_DATE" DATE)
PARTITION BY RANGE("XIAOFEI_DATE")
SUBPARTITION BY LIST("PROVINCE") SUBPARTITION TEMPLATE
(
SUBPARTITION "LIST_P1" VALUES('北京') STORAGE(ON "STUDENTS"),
SUBPARTITION "LIST_P2" VALUES('新疆') STORAGE(ON "STUDENTS"),
SUBPARTITION "LIST_P3" VALUES(DEFAULT) STORAGE(ON "STUDENTS")
),
SUBPARTITION BY HASH("DISTRICT") SUBPARTITION TEMPLATE
(
SUBPARTITION "HASH_P1" STORAGE(ON "STUDENTS"),
SUBPARTITION "HASH_P2" STORAGE(ON "STUDENTS"),
SUBPARTITION "HASH_P3" STORAGE(ON "STUDENTS"),
SUBPARTITION "HASH_P4" STORAGE(ON "STUDENTS")
)
(
PARTITION "RANGE_P1" VALUES LESS THAN(DATE'2000-01-01') STORAGE(ON "STUDENTS", CLUSTERBTR) ,
PARTITION "RANGE_P2" VALUES LESS THAN(DATE'2001-01-01') STORAGE(ON "STUDENTS", CLUSTERBTR) ,
PARTITION "RANGE_P3" VALUES LESS THAN(DATE'2002-01-01') STORAGE(ON "STUDENTS", CLUSTERBTR) ,
PARTITION "RANGE_P4" VALUES LESS THAN(DATE'2003-01-01') STORAGE(ON "STUDENTS", CLUSTERBTR) ,
PARTITION "RANGE_P5" VALUES LESS THAN(DATE'2004-01-01') STORAGE(ON "STUDENTS", CLUSTERBTR) ,
PARTITION "RANGET_P6" VALUES LESS THAN(DATE'2005-01-01') STORAGE(ON "STUDENTS", CLUSTERBTR) ,
PARTITION "RANGE_P_MAX" VALUES LESS THAN(MAXVALUE) STORAGE(ON "STUDENTS", CLUSTERBTR)
) STORAGE(HASHPARTMAP(1), ON "STUDENTS", CLUSTERBTR);
第一个sql：
SELECT A.TABLE_NAME,
A.PARTITIONING_TYPE,
B.COLUMN_NAME AS PART_COLUMN_NAME,
A.SUBPARTITIONING_TYPE,
A."INTERVAL"
FROM SYS.ALL_PART_TABLES A
LEFT JOIN (
SELECT OWNER, NAME, COLUMN_NAME
FROM SYS.ALL_PART_KEY_COLUMNS
ORDER BY COLUMN_POSITION
) B
ON A.OWNER = B.OWNER
AND A.TABLE_NAME = B.NAME
WHERE A.TABLE_NAME IN ('PHONEDETAIL4') AND A.OWNER = 'STUDENTS';
第二个sql：
SELECT *
FROM SYS.ALL_SUBPART_KEY_COLUMNS
WHERE NAME IN ('PHONEDETAIL4') AND OWNER = 'STUDENTS';
第三个sql：
SELECT TABLE_NAME,
PARTITION_NAME,
HIGH_VALUE,
PARTITION_POSITION,
TABLESPACE_NAME,
COMPRESSION
FROM SYS.ALL_TAB_PARTITIONS
WHERE TABLE_NAME IN ('PHONEDETAIL4') AND TABLE_OWNER = 'STUDENTS'
ORDER BY PARTITION_POSITION;
第四个sql：
SELECT DISTINCT *
FROM SYS.ALL_TAB_SUBPARTITIONS
WHERE TABLE_NAME IN ('PHONEDETAIL4') AND TABLE_OWNER = 'STUDENTS';
有两个问题：
1、第一个sql查询出来的是range，其子分区类型是list，这个时候我该怎么去查找list的子分期类型：hash类型呢？
2、第二个sql查询出来的键，我该怎么查询能知道其对应的是什么分区类型？（比如PROVINCE对应LIST;DISTRICT对应HASH）
试一下下面这个SQL，看看是否能够满足需要。这个SQL有点慢，你可以继续研究优化下
WITH T_PART AS
(
SELECT ROW_NUMBER() OVER (ORDER BY TAB.NAME) AS RN
,CONNECT_BY_ROOT(TAB.ID) AS BASE_TABLE_ID
,LEVEL - 1 AS PART_LEVEL
,PAR.PARTITION_NAME
,TAB.ID AS PART_TABLE_ID
,TAB.NAME AS PART_TABLE_NAME
,CASE WHEN PRIOR TAB.INFO3&0X3F IN (0X06, 0x18, 0x22) THEN 'RANGE'
WHEN PRIOR TAB.INFO3&0X3F IN (0X08, 0x1A, 0x24) THEN 'HASH'
WHEN PRIOR TAB.INFO3&0X3F IN (0X0B, 0x1C, 0x26) THEN 'LIST'
WHEN PRIOR TAB.INFO3&0X3F = 0X04 THEN 'VERTICAL'
END AS PART_TYPE
,CASE WHEN TAB.INFO3&0X3F IN (0X06, 0x18, 0x22) THEN 'RANGE'
WHEN TAB.INFO3&0X3F IN (0X08, 0x1A, 0x24) THEN 'HASH'
WHEN TAB.INFO3&0X3F IN (0X0B, 0x1C, 0x26) THEN 'LIST'
WHEN TAB.INFO3&0X3F = 0X04 THEN 'VERTICAL'
END AS SUBPART_TYPE
FROM SYSOBJECTS TAB
JOIN SYSOBJECTS SCH
ON SCH.TYPE$ = 'SCH'
AND SCH.ID = TAB.SCHID
LEFT JOIN SYSHPARTTABLEINFO AS PAR
ON PAR.BASE_TABLE_ID = TAB.PID
AND PAR.PART_TABLE_ID = TAB.ID
WHERE TAB.SUBTYPE$ IN ('UTAB')
START WITH SCH.NAME = 'STUDENTS' AND TAB.NAME = 'PHONEDETAIL4'
CONNECT BY PRIOR TAB.ID = TAB.PID
ORDER SIBLINGS BY TAB.NAME
)
,T_PARTCOL AS
(
SELECT ID
,SF_BIN_GET_SMALLINT(BIN_VALUE,LEVEL * 2) AS COLID
FROM SYSOBJINFOS
WHERE TYPE$ = 'TABPART'
CONNECT BY PRIOR ID = ID
AND PRIOR SYS_GUID IS NOT NULL
AND LEVEL <= SF_BIN_GET_SMALLINT(BIN_VALUE,0)
)
SELECT PTAB.BASE_TABLE_ID
,PTAB.PART_LEVEL
,PTAB.PART_TABLE_NAME
,REPEAT(' ',PTAB.PART_LEVEL * 2) || PTAB.PARTITION_NAME AS PARTITION_NAME
,PTAB.PART_TYPE
,PTAB.SUBPART_TYPE
,(SELECT LISTAGG(COL.NAME,',')
FROM SYSCOLUMNS COL
,T_PARTCOL PCOL
WHERE COL.ID = PTAB.BASE_TABLE_ID
AND PCOL.ID = PTAB.PART_TABLE_ID
AND COL.COLID = PCOL.COLID
) AS SUBPART_COL
FROM T_PART PTAB
ORDER BY PTAB.RN
第一个问题找子分区类型，可以试下这个SQL是否满足要求：
SELECT OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_TYPE,BASE_TABLE_ID,PART_TABLE_ID,PARTITION_TYPE FROM SYSHPARTTABLEINFO ,DBA_OBJECTS WHERE PART_TABLE_ID = OBJECT_ID AND OBJECT_NAME = 'PHONEDETAIL4';
联系客服
达梦在线服务平台社区用户管理规范
鄂ICP备18017926号-1
DM系统视图or表，是不支持获取超过两层级的分区表信息吗？