postgresql 表分区的测试用例

概述----大数据数据表分区做法 CREATE TABLE bcompany_01(CHECK(cpy_id<=500000) ) INHERITS (bcompany); CREATE TABLE bcompany_02(CHECK(cpy_id<=1000000 and cpy_id>500000) ) INHERITS (bcompany); CREATE TABLE bcompany_03(C

----大数据数据表分区做法
CREATE table bcompany_01(CHECK(cpy_ID<=500000) ) inheritS (bcompany);

CREATE table bcompany_02(CHECK(cpy_ID<=1000000 and cpy_ID>500000) ) inheritS (bcompany);

CREATE table bcompany_03(CHECK(cpy_ID<=1500000 and cpy_ID>1000000) ) inheritS (bcompany);

CREATE table bcompany_04(CHECK(cpy_ID<=2000000 and cpy_ID>1500000) ) inheritS (bcompany);

CREATE OR REPLACE RulE bcompany_01_partition AS
ON INSERT TO bcompany WHERE (cpy_ID<=500000)
DO INSTEAD
INSERT INTO bcompany_01 VALUES(NEW.cpy_ID,NEW.cpy_CODE,NEW.cpy_name,NEW.cpy_INDUSTRY,NEW.cpy_KIND,
NEW.cpy_MEMO,NEW.cpy_ADDRESS,NEW.cpy_ZIP,NEW.cpy_link,NEW.cpy_WEB,NEW.cpy_FAX,NEW.cpy_PHONE,NEW.cpy_EMAIL);

CREATE OR REPLACE RulE bcompany_02_partition AS
ON INSERT TO bcompany WHERE (cpy_ID<=1000000 and cpy_ID>500000)
DO INSTEAD
INSERT INTO bcompany_02 VALUES(NEW.cpy_ID,NEW.cpy_EMAIL);

CREATE OR REPLACE RulE bcompany_03_partition AS
ON INSERT TO bcompany WHERE (cpy_ID<=1500000 and cpy_ID>1000000)
DO INSTEAD
INSERT INTO bcompany_03 VALUES(NEW.cpy_ID,NEW.cpy_EMAIL);

CREATE OR REPLACE RulE bcompany_04_partition AS
ON INSERT TO bcompany WHERE (cpy_ID<=2000000 and cpy_ID>1500000)
DO INSTEAD
INSERT INTO bcompany_04 VALUES(NEW.cpy_ID,NEW.cpy_EMAIL);

SET constraint_exclusion = on;

select * from bcompany_01

select * from bcompany_tmp

--copy (select * from bcompany) to bcompany_bak

select * from bcompany_tmp where cpy_ID<=500000

insert into bcompany (select * from bcompany_tmp where cpy_ID<=1000000 and cpy_ID>500000);
insert into bcompany (select * from bcompany_tmp where cpy_ID<=1500000 and cpy_ID>1000000);
insert into bcompany (select * from bcompany_tmp where cpy_ID<=2000000 and cpy_ID>1500000);
select * from bcompany_01

SELECT * FROM BCOMPANY WHERE

SELECT COUNT(1) FROM BCOMPANY

SELECT COUNT(1) FROM BCOMPANY_TMP WHERE cpy_ID<=2000000

SELECT * FROM BCOMPANY WHERE cpy_name='同维科技公司'

SELECT * FROM BCOMPANY_TMP WHERE cpy_name ='同维科技公司'

explain SELECT * FROM BCOMPANY WHERE cpy_ID<40000

SELECT * FROM BCOMPANY_TMP WHERE cpy_ID=779046

SELECT * FROM BCOMPANY liMIT 10

总结

以上是内存溢出为你收集整理的postgresql 表分区的测试用例全部内容，希望文章能够帮你解决postgresql 表分区的测试用例所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。