----大数据数据表分区做法 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 (编辑:李大同)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!
|