如何修改含partition by的Oracle脚本以适配标准版并实现同等效果

Oracle数据库标准版不支持原生分区表、本地分区索引功能，该类特性属于企业版专属功能，因此原脚本中partition by分区定义、local本地索引相关语句会执行失败。

原分区表核心效果为：按create_year字段将不同年份数据存储到对应表空间、按年过滤时可快速定位数据范围。可以使用「独立分表+联合视图」的方案在标准版实现完全一致的效果，操作步骤如下：

• 第一步：创建各年份独立物理表，分别对应原分区的存储配置

-- 2021年数据表，对应原TEST_2021分区
create table ANDY.TEST_2021
(
  create_year    VARCHAR2(4) not null check (create_year = '2021'),    
  no      VARCHAR2(10) not null,
  id       VARCHAR2(10) not null,
  update_id        VARCHAR2(10) not null,
  update_date      DATE not null,
  constraint TEST_2021_PK primary key (CREATE_YEAR, NO, ID)
)
tablespace TEST_DATA_01
pctfree 10
initrans 1
maxtrans 255
storage
(
  initial 1M
  next 1M
  minextents 1
  maxextents unlimited
);
alter index ANDY.TEST_2021_PK nologging;

-- 2022年数据表，对应原TEST_2022分区
create table ANDY.TEST_2022
(
  create_year    VARCHAR2(4) not null check (create_year = '2022'),    
  no      VARCHAR2(10) not null,
  id       VARCHAR2(10) not null,
  update_id        VARCHAR2(10) not null,
  update_date      DATE not null,
  constraint TEST_2022_PK primary key (CREATE_YEAR, NO, ID)
)
tablespace TEST_DATA_02
pctfree 10
initrans 1
maxtrans 255
storage
(
  initial 1M
  minextents 1
  maxextents unlimited
);
alter index ANDY.TEST_2022_PK nologging;

• 第二步：创建联合视图，替代原分区表的统一查询入口

create or replace view ANDY.TEST as
select * from ANDY.TEST_2021
union all
select * from ANDY.TEST_2022;

• 第三步：如果需要支持对视图执行DML（插入/更新/删除）操作，可以在视图上创建INSTEAD OF触发器，按create_year字段路由到对应子表：

create or replace trigger ANDY.TEST_DML_TRIGGER
instead of insert or update or delete on ANDY.TEST
for each row
begin
  if inserting then
    case :new.create_year
      when '2021' then insert into ANDY.TEST_2021 values (:new.create_year, :new.no, :new.id, :new.update_id, :new.update_date);
      when '2022' then insert into ANDY.TEST_2022 values (:new.create_year, :new.no, :new.id, :new.update_id, :new.update_date);
      else raise_application_error(-20001, '不支持的create_year取值');
    end case;
  elsif updating then
    raise_application_error(-20002, '不允许修改create_year字段，如需调整请删除后重新插入');
  elsif deleting then
    case :old.create_year
      when '2021' then delete from ANDY.TEST_2021 where create_year = :old.create_year and no = :old.no and id = :old.id;
      when '2022' then delete from ANDY.TEST_2022 where create_year = :old.create_year and no = :old.no and id = :old.id;
    end case;
  end if;
end;
/

如果不需要将不同年份数据存储到不同表空间，仅需要保留原表结构和主键约束，可直接删除分区逻辑，改为普通表即可：

create table ANDY.TEST
(
  create_year    VARCHAR2(4) not null,    
  no      VARCHAR2(10) not null,
  id       VARCHAR2(10) not null,
  update_id        VARCHAR2(10) not null,
  update_date      DATE not null
);
alter table ANDY.TEST
  add constraint TEST_PK primary key (CREATE_YEAR, NO, ID);
alter index ANDY.TEST_PK nologging;

该方案和原分区表的差异是所有数据都存到默认表空间，按年查询时无法走分区裁剪，但主键约束、数据结构完全一致。

内容的提问来源于stack exchange，提问作者Howard