[高手帮忙][江湖救急] Oracle 分区表中LESS THAN 怎么迁移到 postgresql8.4

lmx66768

下面这个是Oracle 上的源表，partition 部分不知道怎么迁移到postgresql ，请教下。谢谢！



  CREATE TABLE AIOSS.BMS_FEE_SUM

   ( SERIALNO VARCHAR(17) NOT NULL,

  OFFICE_ID VARCHAR(10) NOT NULL,

COUNTY_ID VARCHAR(3) NOT NULL,

REGION_ID VARCHAR(1) NOT NULL,

CLOSE_BALANCE_TIME DATE,

SERVICE_KEY VARCHAR(30)

   )

  PARTITION BY RANGE (REGION_ID)

(

PARTITION APART  VALUES LESS THAN ('B')

PARTITION BPART  VALUES LESS THAN ('C')

PARTITION CPART  VALUES LESS THAN ('D')

PARTITION DPART  VALUES LESS THAN ('E')

PARTITION EPART  VALUES LESS THAN ('F')

PARTITION ZPART  VALUES LESS THAN (MAXVALUE)

);

lmx66768

顶下，知道的大侠，帮个忙吧..

bambo

一位高手说了，PG的分区表可能性能不行，这个问题需要好好考虑。

tanfeng3

表大不？如果不大，直接插入数据到PG，就不要用分区了。

osdba

建分区表的语句如下：
CREATE TABLE BMS_FEE_SUM
( SERIALNO VARCHAR(17) NOT NULL,
  OFFICE_ID VARCHAR(10) NOT NULL,
COUNTY_ID VARCHAR(3) NOT NULL,
REGION_ID VARCHAR(1) NOT NULL,
CLOSE_BALANCE_TIME DATE,
SERVICE_KEY VARCHAR(30)
);

--注意建分区表，要加check，这样保证select * from bms_fee_sum where region_id='A'这样的语句只扫描特定的分区，而不会扫描所有的分区
create table bms_fee_sum_b() inherits(bms_fee_sum);
alter table bms_fee_sum_b add check(region_id<'B');

create table bms_fee_sum_c() inherits(bms_fee_sum);
alter table bms_fee_sum_c add check(region_id>='B' and region_id<'C');

create table bms_fee_sum_d() inherits(bms_fee_sum);
alter table bms_fee_sum_d add check(region_id>='C' and region_id<'D');

create table bms_fee_sum_e() inherits(bms_fee_sum);
alter table bms_fee_sum_e add check(region_id>='D' and region_id<'E');

create table bms_fee_sum_f() inherits(bms_fee_sum);
alter table bms_fee_sum_f add check(region_id>='E' and region_id<'F');

create table bms_fee_sum_max() inherits(bms_fee_sum);
alter table bms_fee_sum_max add check(region_id>='F');

CREATE RULE bms_fee_sum_insert_b AS
ON INSERT TO bms_fee_sum  WHERE ( REGION_ID <'B')
  DO INSTEAD  INSERT INTO bms_fee_sum_b VALUES ( NEW.* );

CREATE RULE bms_fee_sum_insert_c AS
ON INSERT TO bms_fee_sum  WHERE ( REGION_ID >='B' and  REGION_ID <'C')
  DO INSTEAD  INSERT INTO bms_fee_sum_c VALUES ( NEW.* );

CREATE RULE bms_fee_sum_insert_d AS
ON INSERT TO bms_fee_sum  WHERE ( REGION_ID >='C' and REGION_ID <'D')
  DO INSTEAD  INSERT INTO bms_fee_sum_d VALUES ( NEW.* );

CREATE RULE bms_fee_sum_insert_e AS
ON INSERT TO bms_fee_sum  WHERE ( REGION_ID >='D' and REGION_ID <'E')
  DO INSTEAD  INSERT INTO bms_fee_sum_e VALUES ( NEW.* );

CREATE RULE bms_fee_sum_insert_f AS
ON INSERT TO bms_fee_sum  WHERE ( REGION_ID >='E' and REGION_ID <'F')
  DO INSTEAD  INSERT INTO bms_fee_sum_f VALUES ( NEW.* );

CREATE RULE bms_fee_sum_insert_max AS
ON INSERT TO bms_fee_sum  WHERE ( REGION_ID >='F')
  DO INSTEAD  INSERT INTO bms_fee_sum_max VALUES ( NEW.* );

renxiao2003

为什么不反过来。我基本没用过分区表啊。

angeler

PostgreSQL没有分区表的概念，它是用表继承来间接实现分区表的。查询的时候可以通过查询父表来实现对特定子表的查询。不过继承表有一个不太方便的地方。就是如果你需要建立索引，你需要对所有子表都建立，只在父表上建立是没有效果的。