`
mikixiyou
  • 浏览: 1098033 次
  • 性别: Icon_minigender_1
  • 来自: 南京
博客专栏
C3c8d188-c0ab-3396-821d-b68331e21226
Oracle管理和开发
浏览量:352843
社区版块
存档分类
最新评论

Oracle分区表的分区交互技术实现数据快速转移

阅读更多

有一个需求,将某业务表的某个时间点之前的记录转移到它的历史表中。如果当前业务表不是基于这个业务时间点的分区表设置,那只能insert再delete操作。这种转移数据的方法非常非常低基础。经常在初级的数据库管理人员和开发人员的程序中出现。不是说这个方法不好,对于转移的记录数量在几十几百条,而转移频率高,转移时间点随机的情况而言,这个方法还是挺管用的。但如果转移的数据量一次数以百万计的话,这种方法就显得低效了。
因此,在Oracle数据库开发中,对于这种大数据的转移可以使用分区表交换技术实现。即使你一次转移的数据量几亿甚至几十亿也没有关系,转移时间依然是毫秒级的。这个方法大体流程是这样:
首先,你需要将当前表修改为分区表,找到分区字段很关键;其次,这个分区表的索引都建立成本地索引,全局索引就不要了,原因后面介绍;再次,建立一个对应的临时非分区表,表结构和这个一样;最后使用alter table table_name exchange partition  Partition_name with table table_name_exchange;操作,将表分区所拥有数据的实际物理存储空间段相互交换,这是指针级的操作。
这样就完成了这个表分区数据的快速转移。

就这个操作流程,做一个测试。

 

(miki西游 @mikixiyou 原文链接: http://mikixiyou.iteye.com/blog/1773659)

第一步,准备环境

建立一张测试表SALE,它的分区字段是DOTIME,按照季度进行分区。

CREATE TABLE SALE
(
  DOTIME          DATE                          DEFAULT sysdate,
  BILLID          VARCHAR2(20 BYTE)             NOT NULL,
  FROMARREAR      NUMBER(16,4)                  DEFAULT 0
)
PARTITION BY RANGE (DOTIME)
( 
  PARTITION PY11Q3 VALUES LESS THAN (TO_DATE(' 2011-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING, 
  PARTITION PY11Q4 VALUES LESS THAN (TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING, 
  PARTITION PY12Q1 VALUES LESS THAN (TO_DATE(' 2012-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING, 
  PARTITION PY12Q2 VALUES LESS THAN (TO_DATE(' 2012-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING, 
  PARTITION P_MAX VALUES LESS THAN (MAXVALUE)
    LOGGING
)
;

 

再建立一张交换表,表的字段结构和分区表完全一致。

create table SALE_exchange
(
  DOTIME          DATE                          DEFAULT sysdate,
  BILLID          VARCHAR2(20 BYTE)             NOT NULL,
  FROMARREAR      NUMBER(16,4)                  DEFAULT 0
);

 

注意,分区表上的主键所属的全局唯一索引不要了,改成SALE (BILLID,DOTIME)上的本地索引,这样也能保证数据的一致性。原来的主键字段billid必须放在前面,防止原来原来基于billid直接查询操作的性能下降太多。

create unique index PK_SALE on SALE (BILLID,DOTIME) local;

本地分区索引创建完毕。


第二步,
检查一下数据记录情况。假设我们要将PY11Q3分区中的记录转移走。
select count(*) from SALE partition(PY11Q3);
select  count(*)  from SALE_exchange;

第三步,转移数据

alter table SALE exchange partition  PY11Q3 with table SALE_exchange;

一个命令,将分区段和表段的空间指针互相替换,就实现了这个分区表的业务数据的转移工作。

第四步,检查索引状态

分区表这点不好,如果分区发生改变,如exchange,move,split等,那么该分区上的本地索引分区就会失效,同时整个分区表上的全局索引也会失效。所以我最开始说全局索引需要撤销了,就是这个道理。试想,如果这时还有全局索引存在,那它失效了,这就将影响到分区表的其他业务操作。

不管怎样,都需要检查一下索引状态。

select index_name, partition_name, status
  from user_ind_partitions
 where status = 'UNUSABLE'
union all
select index_name, '' as partition_name, status
  from user_indexes
 where status = 'UNUSABLE';

 

这里,本地分区索引的索引分区肯定失效了。原来exchange表上的索引也会失效。它们都需要重建。
分区索引的重建:
alter index PK_SALE rebuild partition PY11Q3;
 
alter index PK_SALE_EXCHANGE rebuild;


经过这样的操作之后,历史数据是从当前业务表移出来了,但它在一个临时的孤立的表中。在现实业务中,这样的数据是需要移动历史表中。当然,如果你有转换操作,你可以继续使用insert 方法转移。如果没有,你还可以使用分区交换技术,将这个数据交换到历史表中。

简而言之,Oracle分区表技术在大数据量表的操作中建议经常使用,它的很多特性有助于我们开发出高效的应用程序。

1
0
分享到:
评论
2 楼 mikixiyou 2013-07-12  
zhangyuslam 写道
如果担心全局索引失效,可以使用如下语句吧:
alter index PK_SALE rebuild partition PY11Q3 update global indexes


rebuild过程接受不了,大量的锁等待。。。
1 楼 zhangyuslam 2013-07-03  
如果担心全局索引失效,可以使用如下语句吧:
alter index PK_SALE rebuild partition PY11Q3 update global indexes

相关推荐

    Oracle 分区Partitioning培训

    通过以上知识点的学习,我们可以了解到 Oracle 分区技术不仅有助于提高数据库的性能,还能增强数据的可用性和管理效率。对于处理大规模数据集的应用场景来说,Oracle 分区技术是一项非常重要的工具。

    基于Oracle_Linux环境数据抽取技术的研究与实践

    - 本文研究了如何在Oracle_Linux环境中有效地实现与其他平台之间的数据抽取,特别是在Oracle数据库与非Oracle数据库之间的数据交互。 3. **数据仓库更新维护优化策略** - 为了提高数据仓库的性能,提出了几种优化...

    Oracle 词汇表

    Oracle 词汇表是数据库管理员和开发人员在使用Oracle数据库系统时必须掌握的重要工具。Oracle数据库是全球广泛应用的关系型数据库管理系统,其英文界面对于非母语使用者可能会带来理解困难。因此,掌握Oracle 词汇表...

    Oracle快速上手

    在Oracle中,你可以创建各种类型的表,如堆表、分区表,并可以利用索引来加速查询。视图则是虚拟表,基于一个或多个表的查询结果。 "子程序.ppt"可能涵盖Oracle的存储过程和函数。这些是PL/SQL代码块,可以在数据库...

    面向大型数据处理系统的Oracle数据库性能优化技术.pdf

    4. **分区技术**:通过分区技术将数据物理分隔,提高查询速度,均衡I/O,例如将大表分区到不同磁盘上。 5. **索引优化**:选择合适的字段建立索引,避免在经常删除的字段上建索引,考虑位映射索引和函数索引。 6. **...

    EF5+Oracle12c实现插入数据

    本篇文章将深入探讨如何使用EF5与Oracle 12c结合,实现高效的数据插入功能。 首先,我们需要确保开发环境中已经安装了Oracle的.NET数据提供程序,即ODP.NET。Oracle Data Provider for .NET (ODP.NET) 是Oracle公司...

    Oracle 10g 快速入门学习

    学习如何分析和优化SQL语句,调整表分区,使用索引,以及管理内存和磁盘空间,对提升数据库性能至关重要。 十、安全管理 Oracle 10g提供了强大的权限控制机制,包括用户管理、角色、权限分配等。理解如何设置合适的...

    oracle数据仓库解决方案

    Oracle 数据仓库解决方案基于Oracle8i数据库,提供了一系列新特性,如并行处理、分区技术,以及对大数据和实时分析的支持。Oracle OLAP产品(如Oracle Analytics Server)则提供了高级的多维分析能力,而Oracle集成...

    json格式数据到入oracle数据库java源码

    - 索引和表分区:根据数据特点创建索引或使用分区策略,加快查询和插入速度。 综上所述,从JSON格式数据到Oracle数据库的Java源码实现涉及多个环节,包括JSON解析、数据库连接、数据处理、事务控制以及性能优化。...

    基于ORACLE的数据仓库&BI;系统实例经典教程

    1. 数据仓库设计:包括星型和雪花型模式,以及如何使用Oracle的表分区和物化视图优化查询性能。 2. ETL(Extract, Transform, Load)过程:学习如何从各种数据源抽取数据,清洗和转换数据,然后加载到数据仓库中。...

    向Oracle用户表中加载.csv 类型的Excel数据

    对于大量数据,可能需要使用并行加载、分区策略等高级技术。此外,确保在导入数据时遵循数据库安全最佳实践,避免敏感信息泄露。在企业环境中,通常会有专门的数据集成工具如ETL(提取、转换、加载)工具来处理这类...

    Oracle数据仓库解决方案.docx

    - **丰富查询处理技术**:Oracle8i支持复杂的查询处理技术,包括多层索引、分区表等,这些技术可以显著提高查询性能。 - **高级SQL优化器**:Oracle8i的SQL优化器能够自动选择最优的查询执行计划,从而提高查询效率...

    ORACLE数据库中插入大字段数据的解决方法

    如果数据量巨大,可以考虑先将数据插入临时表或利用表分区功能,分批处理,然后再合并到目标表。 6. **调整初始化参数**: 优化数据库参数如`DB_FILE_MULTIBLOCK_READ_COUNT`和`LOB_CACHE_SIZE`,可以提升大字段...

    总结java程序中操作Oracle数据库的常用操作1

    11. Oracle特有功能:Oracle数据库提供了许多特有的功能,如游标、存储过程、触发器、分区表、物化视图等。在Java中,可以通过CallableStatement调用存储过程。 `PersonCreditComplainDao.java`文件可能包含了一个...

    大型数据库应用oracle实验报告.pdf

    Oracle 10g 是一个功能强大的大型数据库管理系统,本实验的主要目的是熟悉 Oracle 的环境,学习使用 SQL*Plus 与 Oracle 进行交互,掌握连接数据库以及断开连接的方法,掌握数据文件和控制文件的管理基本命令,掌握...

    oracle 脚本

    Oracle 10g支持分区表,这是一种高级数据管理技术,将大表分成较小、更易管理的部分,每个部分称为一个分区。这提高了查询性能,尤其是在处理大量数据时。分区方式包括范围分区、列表分区、哈希分区和复合分区等。...

    Abp项目模板使用Oracle数据库

    同时,Oracle 11g提供的高级特性,如物化视图、分区表、存储过程等,可以帮助优化查询性能和数据管理。 总的来说,这个项目模板展示了如何将Abp框架与Oracle 11g数据库相结合,以构建具有完整权限管理和多租户支持...

    oracle方向很值得一看的内容

    4. **分区表**:Oracle支持分区表,这是一种将大表逻辑上划分为多个更小、更易管理的部分的技术。根据时间、范围、列表或哈希等方式进行分区,可提升大规模数据的查询速度和管理效率。 5. **备份与恢复**:Oracle...

    Oracle中文官方文档

    - **分区表及分区索引**:讲解了如何通过将大表分成更小的部分来优化查询性能。 - **内容管理**:介绍了Oracle如何管理和检索非结构化数据,如文档和多媒体文件。 - **数据库安全**:探讨了保护数据免受未经授权访问...

    《基于Oracle10g的数据仓库实践》

    Oracle10g提供了多种内置的数据仓库功能,包括但不限于分区、索引组织表、压缩以及数据挖掘工具等,这些特性对于提高数据仓库系统的性能至关重要。 ##### 1.3 Oracle10g的数据仓库设计 设计一个高效的数据仓库,...

Global site tag (gtag.js) - Google Analytics