分区表
单数据库模式
注意：此解决方法适用于在单数据库模式下运行的 UDB-TDS 集群。
在 UDB-TDS TDS 端口上创建的分区表和在 UDBTX 端口上创建的分区表之间的主要区别在于表所有者。以下示例演示了如何在 UDBTX 端口上创建的表的所有者更改为 dbo，以便您可以访问 UDB-TDS 端口和 UDBTX 端口上的表。
范围分区示例
下面的示例创建并测试具有两个分区的分区表。首先，使用 unvdb（在 UDBTX 端口上）创建表：
DROP TABLE IF EXISTS dbo.PartitionTest;
DROP TABLE IF EXISTS dbo.PartitionTest_y2022m01;
DROP TABLE IF EXISTS dbo.PartitionTest_y2022m02;
DROP INDEX IF EXISTS dbo.partitiontest_logdate_idx;
CREATE TABLE IF NOT EXISTS dbo.PartitionTest
(
city_id integer NOT NULL,
logdate date NOT NULL,
peaktemp integer,
unitsales integer
) PARTITION BY RANGE (logdate);
CREATE INDEX PartitionTest_logdate_idx
ON dbo.PartitionTest(logdate ASC NULLS LAST);
Then, create the partitions:
CREATE TABLE IF NOT EXISTS dbo.PartitionTest_y2022m01 PARTITION OF dbo.PartitionTest
FOR VALUES FROM ('2022-01-01') TO ('2022-02-01');
CREATE TABLE IF NOT EXISTS dbo.PartitionTest_y2022m02 PARTITION OF dbo.PartitionTest
FOR VALUES FROM ('2022-02-01') TO ('2022-03-01');
然后，使用 unvdb 添加数据：
INSERT INTO dbo.partitiontest VALUES (1,'2022-01-01',1,1);
INSERT INTO dbo.partitiontest VALUES (2,'2022-01-10',1,2);
INSERT INTO dbo.partitiontest VALUES (3,'2022-01-15',1,3);
INSERT INTO dbo.partitiontest VALUES (4,'2022-02-01',2,1);
INSERT INTO dbo.partitiontest VALUES (5,'2022-02-03',2,2);
INSERT INTO dbo.partitiontest VALUES (6,'2022-02-11',2,3);
INSERT INTO dbo.partitiontest VALUES (7,'2022-02-15',2,4);
INSERT INTO dbo.partitiontest VALUES (8,'2022-02-16',2,5);
INSERT INTO dbo.partitiontest VALUES (8,'2022-02-17',2,6);
INSERT INTO dbo.partitiontest VALUES (8,'2022-02-20',2,7);
INSERT INTO dbo.partitiontest VALUES (8,'2022-02-21',2,8);
当您从 unvdb 和 UDB-TDS 查询数据时：
SELECT * FROM dbo.partitiontest
unvdb（在 UDBTX 端口上）将显示所有数据。SSMS（在 UDB-TDS 端口上）将显示以下错误消息，并且对象浏览器不会显示表名称：
Msg 33557097, Level 16, State 1, Line 3
relation "master_dbo.partitiontest" does not exist
使用 unvdb（在 UDBTX 端口上）将表所有者更改为 dbo：
ALTER TABLE dbo.partitiontest OWNER to dbo;
ALTER TABLE dbo.partitiontest_y2022m01 OWNER to dbo;
ALTER TABLE dbo.partitiontest_y2022m02 OWNER to dbo;
然后，从unvdb 和 SSMS 查询数据：
SELECT * FROM dbo.partitiontest
unvdb (on the UDBTX port) 将会显示所有数据.
SSMS (on the UDB-TDS port)将会显示数据，并且你应该能在输出框内看到这些表。
分析结果集
首先，通过TDS端口的SSMS查询数据库：
SET UDBTX-TDS_SHOWPLAN_ALL ON
SELECT * FROM dbo.partitiontest WHERE logdate = '2022-02-21'
Query Text: Select * from dbo.partitiontest where logdate = '2022-02-21'
Bitmap Heap Scan on partitiontest_y2022m02 partitiontest (cost=4.22..14.76 rows=9 width=16)
Recheck Cond: (logdate = '2022-02-21'::date)
-> Bitmap Index Scan on partitiontest_y2022m02_logdate_idx (cost=0.00..4.22 rows=9 width=0)
Index Cond: (logdate = '2022-02-21'::date)
然后，在UDBTX端口上使用unvdb：
EXPLAIN ANALYZE
SELECT * FROM dbo.partitiontest WHERE logdate = '2022-02-21'
"Bitmap Heap Scan on partitiontest_y2022m02 partitiontest (cost=4.22..14.76 rows=9 width=16) (actual time=0.015..0.016 rows=1 loops=1)"
" Recheck Cond: (logdate = '2022-02-21'::date)"
" Heap Blocks: exact=1"
" -> Bitmap Index Scan on partitiontest_y2022m02_logdate_idx (cost=0.00..4.22 rows=9 width=0) (actual time=0.011..0.011 rows=1 loops=1)"
" Index Cond: (logdate = '2022-02-21'::date)"
"Planning Time: 0.124 ms"
"Execution Time: 0.043 ms"
继承分区示例
首先，创建将在我们的示例中使用的数据库对象：
DROP TRIGGER IF EXISTS insert_measurement_trigger ON dbo.measurement_inheritance;
DROP FUNCTION IF EXISTS dbo.measurement_inheritance_insert_trigger();
DROP TABLE IF EXISTS dbo.measurement_inheritance_y2006m02;
DROP TABLE IF EXISTS dbo.measurement_inheritance_y2006m03;
DROP TABLE IF EXISTS dbo.measurement_inheritance;
CREATE TABLE dbo.measurement_inheritance (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
);
CREATE TABLE dbo.measurement_inheritance_y2006m02 (
CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )
) INHERITS (dbo.measurement_inheritance);
CREATE TABLE dbo.measurement_inheritance_y2006m03 (
CHECK ( logdate >= DATE '2006-03-01' AND logdate < DATE '2006-04-01' )
) INHERITS (dbo.measurement_inheritance);
DROP INDEX IF EXISTS dbo_log_measurement_inheritance_y2006m02;
DROP INDEX IF EXISTS dbo_log_measurement_inheritance_y2006m03;
CREATE INDEX dbo_log_measurement_inheritance_y2006m02 ON dbo.measurement_inheritance_y2006m02 (logdate);
CREATE INDEX dbo_log_measurement_inheritance_y2006m03 ON dbo.measurement_inheritance_y2006m03 (logdate);
CREATE OR REPLACE FUNCTION dbo.measurement_insert_trigger()
RETURNS TRIGGER AS $$
DECLARE
year1 int;
month1 smallint;
BEGIN
year1 = EXTRACT(YEAR FROM new.logdate );
month1 = EXTRACT(month FROM new.logdate );
raise info 'year1 : % ',year1;
if month1<10 then
EXECUTE 'INSERT INTO dbo.measurement_inheritance_y'|| year1::varchar(4)||'m0'|| month1::varchar||' VALUES ('||
NEW.city_id::varchar||','|| '''' ||NEW.logdate::date||''''
' ,'|| NEW.peaktemp::varchar||','||NEW.unitsales::varchar
|| ')';
ELSE
EXECUTE 'INSERT INTO dbo.measurement_inheritance_y'|| year1::varchar(4)||'m'|| month1::varchar||' VALUES ('||
NEW.city_id::varchar||','|| '''' ||NEW.logdate::date||''''
' ,'|| NEW.peaktemp::varchar||','||NEW.unitsales::varchar
|| ')';
END IF;
-- 2008m01 VALUES (NEW.*);
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS insert_measurement_trigger ON dbo.measurement_inheritance;
CREATE TRIGGER insert_measurement_trigger
BEFORE INSERT ON dbo. measurement_inheritance
FOR EACH ROW EXECUTE FUNCTION dbo.measurement_insert_trigger();
Then, add half of the test data to the table using unvdb (on the UDBTX port):
INSERT INTO dbo.measurement_inheritance VALUES (1,'2006-02-01',1,1);
INSERT INTO dbo.measurement_inheritance VALUES (2,'2006-02-10',1,2);
INSERT INTO dbo.measurement_inheritance VALUES (3,'2006-02-15',1,3);
INSERT INTO dbo.measurement_inheritance VALUES (4,'2006-03-01',2,1);
INSERT INTO dbo.measurement_inheritance VALUES (5,'2006-03-03',2,2);
INSERT INTO dbo.measurement_inheritance VALUES (6,'2006-03-11',2,3);
INSERT INTO dbo.measurement_inheritance VALUES (7,'2006-03-15',2,4);
INSERT INTO dbo.measurement_inheritance VALUES (8,'2006-03-16',2,5);
INSERT INTO dbo.measurement_inheritance VALUES (8,'2006-03-17',2,6);
INSERT INTO dbo.measurement_inheritance VALUES (8,'2006-03-20',2,7);
INSERT INTO dbo.measurement_inheritance VALUES (8,'2006-03-21',2,8);
Use unvdb to change the table owner to dbo:
ALTER FUNCTION dbo.measurement_insert_trigger() OWNER TO dbo;
ALTER TABLE dbo.measurement_inheritance OWNER to dbo;
ALTER TABLE dbo.measurement_inheritance_y2006m02 OWNER TO dbo;
ALTER TABLE dbo.measurement_inheritance_y2006m03 OWNER TO dbo;
Add Data from SSMS:
INSERT INTO dbo.measurement_inheritance values (1,'2006-02-05',1,4);
INSERT INTO dbo.measurement_inheritance values (2,'2006-02-15',1,5);
INSERT INTO dbo.measurement_inheritance values (3,'2006-02-20',1,6);
INSERT INTO dbo.measurement_inheritance values (4,'2006-03-02',2,9);
INSERT INTO dbo.measurement_inheritance values (5,'2006-03-05',2,10);
INSERT INTO dbo.measurement_inheritance values (6,'2006-03-12',2,11);
INSERT INTO dbo.measurement_inheritance values (7,'2006-03-16',2,12);
INSERT INTO dbo.measurement_inheritance values (8,'2006-03-18',2,13);
INSERT INTO dbo.measurement_inheritance values (8,'2006-03-19',2,14);
INSERT INTO dbo.measurement_inheritance values (8,'2006-03-23',2,15);
INSERT INTO dbo.measurement_inheritance values (8,'2006-03-25',2,16);
打开 EXECUTE ANALYZE 功能，并查询数据：
SET UDBTX-TDS_SHOWPLAN_ALL on
SELECT * FROM dbo.measurement_inheritance WHERE logdate ='2006-03-25';
冲突分区示例
首先，创建将在示例中使用的数据库对象：
DROP TABLE IF EXISTS dbo.customers;
CREATE TABLE dbo.customers (
customer_id serial PRIMARY KEY,
name VARCHAR UNIQUE,
email VARCHAR NOT NULL,
active bool NOT NULL DEFAULT TRUE
);
Then, add data:
INSERT INTO
dbo.customers (name, email)
VALUES
(‘ABC’, 'contact@abc.com'),
('MBA', 'contact@mba.com'),
('XYZ', 'contact@xyz.com');
CREATE OR REPLACE FUNCTION dbo.onConflictTestFromSSM()
RETURNS void AS $$
DECLARE
year1 int;
month1 smallint;
BEGIN
INSERT INTO dbo.customers (NAME, email)
VALUES('MBA','hotline@mbacom')
ON CONFLICT (name )
DO
UPDATE SET email = EXCLUDED.email || ';' || customers.email;
RAISE INFO 'function executes';
END;
$$
LANGUAGE plpgsql;
ALTER FUNCTION dbo.onConflictTestFromSSM() OWNER TO dbo;
ALTER TABLE dbo.customers OWNER to dbo;
使用 SSMS 查询 dbo.onConflictTestFromSSM（）：
SELECT * FROM dbo.onConflictTestFromSSM()
EXEC dbo.onConflictTestFromSSM
SELECT * FROM dbo.customers
多数据库模式
在UDBTX 端用PG语法创建分区表：
DROP TABLE IF EXISTS nidc_dbo.PartitionTest;
DROP TABLE IF EXISTS nidc_dbo.PartitionTest_y2022m01;
DROP TABLE IF EXISTS nidc_dbo.PartitionTest_y2022m02;
DROP INDEX IF EXISTS nidc_dbo.partitiontest_logdate_idx;
CREATE TABLE IF NOT EXISTS nidc_dbo.PartitionTest
(
city_id integer NOT NULL,
logdate date NOT NULL,
peaktemp integer,
unitsales integer
) PARTITION BY RANGE (logdate);
CREATE INDEX PartitionTest_logdate_idx
ON nidc_dbo.PartitionTest(logdate ASC NULLS LAST);
CREATE TABLE IF NOT EXISTS nidc_dbo.PartitionTest_y2022m01 PARTITION OF nidc_dbo.PartitionTest
FOR VALUES FROM ('2022-01-01') TO ('2022-02-01');
CREATE TABLE IF NOT EXISTS nidc_dbo.PartitionTest_y2022m02 PARTITION OF nidc_dbo.PartitionTest
FOR VALUES FROM ('2022-02-01') TO ('2022-03-01');
授权给nidc_dbo， nidc是数据库名称。不授权，在SSMS端不可见。
ALTER TABLE nidc_dbo.partitiontest OWNER to nidc_dbo;
ALTER TABLE nidc_dbo.partitiontest_y2022m01 OWNER to nidc_dbo;
ALTER TABLE nidc_dbo.partitiontest_y2022m02 OWNER to nidc_dbo;
在UDBTX端用下面的语句插入数据：
INSERT INTO nidc_dbo.partitiontest VALUES (1,'2022-01-01',1,1);
INSERT INTO nidc_dbo.partitiontest VALUES (2,'2022-01-10',1,2);
INSERT INTO nidc_dbo.partitiontest VALUES (3,'2022-01-15',1,3);
INSERT INTO nidc_dbo.partitiontest VALUES (4,'2022-02-01',2,1);
INSERT INTO nidc_dbo.partitiontest VALUES (5,'2022-02-03',2,2);
INSERT INTO nidc_dbo.partitiontest VALUES (6,'2022-02-11',2,3);
INSERT INTO nidc_dbo.partitiontest VALUES (7,'2022-02-15',2,4);
INSERT INTO nidc_dbo.partitiontest VALUES (8,'2022-02-16',2,5);
INSERT INTO nidc_dbo.partitiontest VALUES (8,'2022-02-17',2,6);
INSERT INTO nidc_dbo.partitiontest VALUES (8,'2022-02-20',2,7);
INSERT INTO nidc_dbo.partitiontest VALUES (8,'2022-02-21',2,8);
在SSMS端用如下语句插入数据：
INSERT INTO partitiontest VALUES (1,'2022-01-01',1,1);
INSERT INTO partitiontest VALUES (2,'2022-01-10',1,2);
INSERT INTO partitiontest VALUES (3,'2022-01-15',1,3);
INSERT INTO partitiontest VALUES (4,'2022-02-01',2,1);
INSERT INTO partitiontest VALUES (5,'2022-02-03',2,2);
INSERT INTO partitiontest VALUES (6,'2022-02-11',2,3);
INSERT INTO partitiontest VALUES (7,'2022-02-15',2,4);
INSERT INTO partitiontest VALUES (8,'2022-02-16',2,5);
INSERT INTO partitiontest VALUES (8,'2022-02-17',2,6);
INSERT INTO partitiontest VALUES (8,'2022-02-20',2,7);
INSERT INTO partitiontest VALUES (8,'2022-02-21',2,8);