为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
【DM版本】:dm7
【操作系统】:
【CPU】:
【问题描述】*:DM7的MPP环境中,创建了分布表```CREATE TABLE PRODUCTION.PRODUCT_INVENTORY
(PRODUCTID INT NOT NULL REFERENCES PRODUCTION.PRODUCT(PRODUCTID),
LOCATIONID INT NOT NULL REFERENCES PRODUCTION.LOCATION(LOCATIONID),
QUANTITY INT NOT NULL)
DISTRIBUTED BY RANGE (QUANTITY)
(
VALUES EQU OR LESS THAN (100) ON EP01,
VALUES EQU OR LESS THAN (MAXVALUE) ON EP02
);
在DM8中有系统表ALL_TABLES_DIS_INFO可以查询到相关分布字段信息,但DM7中此信息是在哪个系统表可以查到的?
SELECT
O2.NAME SCHEMA_NAME,
O1.NAME TABLE_NAME,
CASE WHEN O1.INFO3&0X3F=0x00 THEN 'NORMAL'
WHEN O1.INFO3&0X3F IN (0x13, 0x18, 0x19, 0x1A, 0x1B, 0x1C, 0x1D, 0x21, 0x22, 0x23, 0x24, 0x25, 0x26, 0x27) THEN 'HUGE'
WHEN O1.INFO3&0X3F IN (0x04, 0x05) THEN 'VERTICAL'
WHEN O1.INFO3&0X3F IN (0x06, 0x07, 0x08, 0x09, 0x0B, 0x0C) THEN 'PARTITION'
ELSE 'OTHERS'
END TABLE_TYPE,
CASE WHEN O1.INFO3 >>8 & 0xE0000000 = 0x20000000 THEN 'HASH'
WHEN O1.INFO3>>8 & 0xE0000000 = 0x40000000 THEN 'RANDOM'
WHEN O1.INFO3>>8 & 0xE0000000 = 0x60000000 THEN 'RANGE'
WHEN O1.INFO3>>8 & 0xE0000000 = 0x80000000 THEN 'LIST'
WHEN O1.INFO3>>8 & 0xE0000000 = 0xA0000000 THEN 'FULL'
ELSE 'LOCAL'
END DIS_TYPE ,
TABLE_DISTRIBUTED_COL(O2.NAME, O1.NAME) DIS_COLS
FROM
SYS.SYSOBJECTS O1,
SYS.SYSOBJECTS O2
WHERE
O1.SCHID =O2.ID
AND O2.TYPE$ = 'SCH'
AND O1.TYPE$ = 'SCHOBJ'
AND O1.SUBTYPE$ = 'UTAB'
AND O1.ID > 1000
AND O1.NAME NOT LIKE '%$AUX'
AND O1.NAME NOT LIKE '%$RAUX'
AND O1.NAME NOT LIKE '%$DAUX'
AND O1.NAME NOT LIKE '%$UAUX'
AND O1.NAME NOT LIKE '_%$ALOG' ;