将普通表在线重定义为分区表

最新推荐文章于 2024-01-30 08:02:09 发布

久违的太阳

最新推荐文章于 2024-01-30 08:02:09 发布

阅读量689

点赞数

分类专栏： oracle 管理基础

本文链接：https://blog.csdn.net/su377486/article/details/37996493

版权

oracle 管理基础专栏收录该内容

15 篇文章 0 订阅

订阅专栏

最近遇到一个项目，客户反应数据查询比较慢，主要有5张大表，运行半年数据都在千万级别，其实数据并不多，但是客户硬件比较差，导致查询慢，现在需要讲这些普通表按时间分区。

原表结构如下，主键为feeitemid：

create table PHIS_WEBHIS.OPB_FEEDETAIL
(
  feeitemid     VARCHAR2(36) not null,
  orgcode       VARCHAR2(30) not null,
  regid         VARCHAR2(36) not null,
  recipeid      VARCHAR2(36) not null,
  seqno         NUMBER(3) not null,
  transtype     VARCHAR2(1) not null,
  regdpcd       VARCHAR2(4),
  doctcode      VARCHAR2(6),
  groupid       VARCHAR2(36),
  groupname     VARCHAR2(120),
  itemid        VARCHAR2(36) not null,
  itemname      VARCHAR2(120) not null,
  drugflag      VARCHAR2(1) not null,
  specs         VARCHAR2(50),
  dosemodelcode VARCHAR2(4),
  feecode       VARCHAR2(5),
  unitprice     NUMBER(9,4),
  qty           NUMBER(8,2),
  days          NUMBER(4),
  packqty       NUMBER(4),
  priceunit     VARCHAR2(16),
  scalration    NUMBER(4),
  totcost       NUMBER(8,2),
  owncost       NUMBER(8,2),
  paycost       NUMBER(8,2),
  pubcost       NUMBER(8,2),
  execdpcd      VARCHAR2(4),
  execdpnm      VARCHAR2(20),
  sicode        VARCHAR2(20),
  itemgrade     VARCHAR2(1),
  itemstatus    VARCHAR2(1),
  invoiceid     VARCHAR2(36) not null,
  invocode      VARCHAR2(3),
  feeopcd       VARCHAR2(6),
  feedate       DATE,
  sendflag      VARCHAR2(1),
  returnqty     NUMBER(8,2),
  drugtype      VARCHAR2(2),
  orderid       VARCHAR2(36),
  akc228        NUMBER(8,2),
  ake051        NUMBER(8,2),
  akc268        NUMBER(8,2),
  isselfflag    VARCHAR2(3),
  ext1          VARCHAR2(10),
  ext2          VARCHAR2(10),
  ext3          VARCHAR2(10),
  fee_discount  NUMBER(12,2),
  invoiceseq    VARCHAR2(20),
  sjly          VARCHAR2(1)
)

改变后的分区表如下：

 
 create table PHIS_WEBHIS.OPB_FEEDETAIL_TEST
(
  feeitemid     VARCHAR2(36) not null,
  orgcode       VARCHAR2(30) not null,
  regid         VARCHAR2(36) not null,
  recipeid      VARCHAR2(36) not null,
  seqno         NUMBER(3) not null,
  transtype     VARCHAR2(1) not null,
  regdpcd       VARCHAR2(4),
  doctcode      VARCHAR2(6),
  groupid       VARCHAR2(36),
  groupname     VARCHAR2(120),
  itemid        VARCHAR2(36) not null,
  itemname      VARCHAR2(120) not null,
  drugflag      VARCHAR2(1) not null,
  specs         VARCHAR2(50),
  dosemodelcode VARCHAR2(4),
  feecode       VARCHAR2(5),
  unitprice     NUMBER(9,4),
  qty           NUMBER(8,2),
  days          NUMBER(4),
  packqty       NUMBER(4),
  priceunit     VARCHAR2(16),
  scalration    NUMBER(4),
  totcost       NUMBER(8,2),
  owncost       NUMBER(8,2),
  paycost       NUMBER(8,2),
  pubcost       NUMBER(8,2),
  execdpcd      VARCHAR2(4),
  execdpnm      VARCHAR2(20),
  sicode        VARCHAR2(20),
  itemgrade     VARCHAR2(1),
  itemstatus    VARCHAR2(1),
  invoiceid     VARCHAR2(36) not null,
  invocode      VARCHAR2(3),
  feeopcd       VARCHAR2(6),
  feedate       DATE,
  sendflag      VARCHAR2(1),
  returnqty     NUMBER(8,2),
  drugtype      VARCHAR2(2),
  orderid       VARCHAR2(36),
  akc228        NUMBER(8,2),
  ake051        NUMBER(8,2),
  akc268        NUMBER(8,2),
  isselfflag    VARCHAR2(3),
  ext1          VARCHAR2(10),
  ext2          VARCHAR2(10),
  ext3          VARCHAR2(10),
  fee_discount  NUMBER(12,2),
  invoiceseq    VARCHAR2(20),
  sjly          VARCHAR2(1)
)
partition by range(FEEDATE)
(
partition p20140301 values less than (to_date('2014-04-01', 'yyyy-mm-dd')),
partition p20140401 values less than (to_date('2014-05-01', 'yyyy-mm-dd')),
partition p20140501 values less than (to_date('2014-06-01', 'yyyy-mm-dd')),
partition p20140601 values less than (to_date('2014-07-01', 'yyyy-mm-dd')),
partition p20140701 values less than (to_date('2014-08-01', 'yyyy-mm-dd')),
partition p20140801 values less than (to_date('2014-09-01', 'yyyy-mm-dd')),
partition p20140901 values less than (to_date('2014-10-01', 'yyyy-mm-dd')),
partition p20141001 values less than (to_date('2014-11-01', 'yyyy-mm-dd')),
partition pmax values less than (maxvalue)
)
tablespace PHISTABLESPACE;

查看表是否可以在线重定义：

begin
 dbms_redefinition.can_redef_table('PHIS_WEBHIS','OPB_FEEDETAIL');
 end;

没有输出即可以在线重定义，如果不行则会报错，默认在线重定义的方式是按主键，如果表上没有主键，则需要按照rowid。

其实在线重定义的原理是使用物化视图，物化视图也分主键或者rowid方式，使用物化视图实时刷新将数据同步到临时表上。

开始重定义表：

 execute dbms_redefinition.start_redef_table('PHIS_WEBHIS','OPB_FEEDETAIL','OPB_FEEDETAIL_TEST');

手工进行刷新操作：

 execute dbms_redefinition.sync_interim_table ('PHIS_WEBHIS','OPB_FEEDETAIL','OPB_FEEDETAIL_TEST');

结束重定义：

 execute dbms_redefinition.finish_redef_table('PHIS_WEBHIS','OPB_FEEDETAIL','OPB_FEEDETAIL_TEST');

结束完成之后查询原表发现表结构已经更改。

总结注意：

1.默认使用主键刷新，如果无主键需要使用rowid

execute dbms_redefinition.start_redef_table('PHIS_WEBHIS','OPB_FEEDETAIL','OPB_FEEDETAIL_TEST',null,2);

2.需要在中间转换表上建立相关对象可以使用DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS来创建

 DECLARE
num_errors PLS_INTEGER;
 begin
 DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('PHIS_WEBHIS','OPB_FEEDETAIL','OPB_FEEDETAIL_TEST',DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
 end;
 /

3.完成之后将中间表删除。