Oracle项目--分区表

最新推荐文章于 2024-10-02 00:27:17 发布

weixin_34242509

最新推荐文章于 2024-10-02 00:27:17 发布

阅读量65

点赞数

文章标签：数据库

先说说需求

系统有一张记录日志文件的表，用了一年客户终于感觉慢（早有料到），这种表的做法基本都是这样处理对表进行重新分区，如果是可以停业务使用交换分区进行，如果不能忍受业务不中断的系统使用在线表分区。

分区时发现又有一个问题，就是最新的数据在分区中如何放，这个问题比较恶心，基本又有如下几个方案。

1.对该表进行预先的分区定义，比如把后一年的分区先定义出来，如果时间快到了，再新曾分区。

这种方案比较土，基本上就靠人操作，但是这种方案在项目中用的最多，因为不涉及数据的删除，除了完成数据的重定义之外基本上再也没有什么风险。

先贴出代码，做个参考：

SQL_PARTIION

set echo on
spool D:\UpdateDealwithStatus.log

--删除该表上的索引
drop table DEALWITHSTATUS_TEMP;
drop index IDX_AUDITOR_VES_DEALWITHSTATUS;
drop index IDX_EPODATAID_VES_DEALWITH;


drop index bocoitms.IDX_AUDITOR_DEALWITHSTATUS_TP;
drop index IDX_EPODATAID_DEALWITH_TP;

--重建该表的索引

--分析该表

exec dbms_stats.gather_table_stats('TEST', 'DEALWITHSTATUS', cascade => true);

--创建临时表  --给临时表分区
create table DEALWITHSTATUS_TEMP
(
  epodataid      NUMBER not null,
  auditor        VARCHAR2(6),
  audittime      DATE default sysdate,
  dealwithstatus NUMBER,
  id             NUMBER not null,
  ipaddress      VARCHAR2(15)
)
partition by range (audittime)
(
partition part_07 less than(to_date('2012-07-01','yyyy-mm-dd'))
  tablespace BOCOITMS_DATA_DEFAULT
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    ),
partition part_08 values less than(to_date('2012-08-01','yyyy-mm-dd'))
 tablespace DATA_DEFAULT
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    ),
partition part_09 values less than(to_date('2012-09-01','yyyy-mm-dd'))
 tablespace DATA_DEFAULT
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    ),
partition part_10 values less than(to_date('2012-10-01','yyyy-mm-dd'))
 tablespace DATA_DEFAULT
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    ),
partition part_11 values less than(to_date('2012-11-01','yyyy-mm-dd'))
 tablespace DATA_DEFAULT
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    ),
partition part_12 values less than(to_date('2012-12-01','yyyy-mm-dd'))
 tablespace DATA_DEFAULT
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    ),
partition part_01 values less than(to_date('2013-01-01','yyyy-mm-dd'))
 tablespace DATA_DEFAULT
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    ),
partition part_02 values less than(to_date('2013-02-01','yyyy-mm-dd'))
 tablespace DATA_DEFAULT
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    ),
partition part_03 values less than(to_date('2013-03-01','yyyy-mm-dd'))
 tablespace DATA_DEFAULT
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    ),
partition part_04 values less than(to_date('2013-04-01','yyyy-mm-dd'))
 tablespace DATA_DEFAULT
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    ),
partition part_05 values less than(to_date('2013-05-01','yyyy-mm-dd'))
 tablespace DATA_DEFAULT
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    ),
partition part_06 values less than(to_date('2013-06-01','yyyy-mm-dd'))
 tablespace DATA_DEFAULT
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    ),
partition part_00 values less than(maxvalue) 
 tablespace DATA_DEFAULT
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    )
);


-- 给临时表创建主键，唯一键，外键
-- Create/Recreate primary, unique and foreign key constraints 
alter table DEALWITHSTATUS_TEMP
  add constraint PK_DEALWITHSTATUS_01_TP primary key (ID)
  using index 
  tablespace DATA_DEFAULT
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 16M
    minextents 1
    maxextents unlimited
  );

-- Create/Recreate indexes 
create index IDX_AUDITOR_VES_DEALWITHSTATUS_TP on DEALWITHSTATUS_TEMP (AUDITTIME, AUDITOR)
  tablespace DATA_DEFAULT
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
create index IDX_DEALWITH_TP on DEALWITHSTATUS_TEMP (EPODATAID)
  tablespace DATA_DEFAULT
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );



--开始检查重定义的合理性
exec dbms_redefinition.can_redef_table('TEST','DEALWITHSTATUS');


--
--开始重定义
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(
uname =>'TEST',
orig_table=>'DEALWITHSTATUS',
int_table=>'DEALWITHSTATUS_TEMP'
);
END;
/



--同步新表
BEGIN
dbms_redefinition.sync_interim_table(
 uname=>'TEST',
 orig_table => 'DEALWITHSTATUS',
 int_table => 'DEALWITHSTATUS_TEMP'
);
END;
/

--创建索引，重新定义临时表数据

create index IDX_AUDITOR_DEALWITHSTATUS on DEALWITHSTATUS (AUDITTIME, AUDITOR) 
LOCAL(
partition part_01 tablespace DATA_DEFAULT,
partition part_02 tablespace DATA_DEFAULT,
partition part_03 tablespace DATA_DEFAULT,
partition part_04 tablespace DATA_DEFAULT,
partition part_05 tablespace DATA_DEFAULT,
partition part_06 tablespace DATA_DEFAULT,
partition part_07 tablespace DATA_DEFAULT,
partition part_08 tablespace DATA_DEFAULT,
partition part_09 tablespace DATA_DEFAULT,
partition part_10 tablespace DATA_DEFAULT,
partition part_11 tablespace DATA_DEFAULT,
partition part_12 tablespace DATA_DEFAULT,
partition part_00 tablespace DATA_DEFAULT
);

create index IDX_DEALWITH on DEALWITHSTATUS (AUDITTIME,EPODATAID)
 LOCAL(
partition part_01 tablespace DATA_DEFAULT,
partition part_02 tablespace DATA_DEFAULT,
partition part_03 tablespace DATA_DEFAULT,
partition part_04 tablespace DATA_DEFAULT,
partition part_05 tablespace DATA_DEFAULT,
partition part_06 tablespace DATA_DEFAULT,
partition part_07 tablespace DATA_DEFAULT,
partition part_08 tablespace DATA_DEFAULT,
partition part_09 tablespace DATA_DEFAULT,
partition part_10 tablespace DATA_DEFAULT,
partition part_11 tablespace DATA_DEFAULT,
partition part_12 tablespace DATA_DEFAULT,
partition part_00 tablespace DATA_DEFAULT
);


--收集新表的统计信息
exec dbms_stats.gather_table_stats('TEST','DEALWITHSTATUS_TEMP',cascade => true);



--结束重定义
BEGIN
dbms_redefinition.finish_redef_table(
uname=>'TEST',
orig_table => DEALWITHSTATUS',
int_table =>DEALWITHSTATUS_TEMP');
END;
/

--确认同步数据没有丢失
select count(*) from DEALWITHSTATUS ;
select count(*) from DEALWITHSTATUS_TEMP;


--删除临时表
DROP DEALWITHSTATUS_TEMP;

--打开行移动
alter table DEALWITHSTATUS enable row movement;

--将主键从命名，外键重命名,索引重命名
alter table DEALWITHSTATUS rename constraint PK_DEALWITHSTATUS_01_TP TO 

PK_DEALWITHSTATUS_01;


alter table DEALWITHSTATUS rename constraint IDX_AUDITOR_DEALWITHSTATUS_TP TO 

IDX_AUDITOR_DEALWITHSTATUS;

alter table DEALWITHSTATUS RENAME constraint IDX_DEALWITH_TP TO IDX_DEALWITH ;





spool off;
spool off;

2.就是使用JOB,存储过程

这个的思路就是

创建一个job在分区时间快要到的时候，新建新分区，删除最旧的那个分区。是自动化的job，但是很多客户都不接受，因为有delete操作，而且是删除分区，特别是后台运行的job,风险很大。

3.直接将数据库升级到11G，使用11G的新特性，这个也是开玩笑，客户更不愿意做。

SQL_11G

create table DEALWITHSTATUS_TEMP
(
  epodataid      NUMBER not null,
  auditor        VARCHAR2(6),
  audittime      DATE default sysdate,
  dealwithstatus NUMBER,
  id             NUMBER not null,
  ipaddress      VARCHAR2(15)
)
partition by range (audittime)
interval (numtoyminterval(1,'MONTH'))
(
partition part01 values less than (to_date('2012-07-01','yyyy-mm-dd'))
);