`
8366
  • 浏览: 809108 次
  • 性别: Icon_minigender_1
  • 来自: 西安
社区版块
存档分类
最新评论

Oracle数据库中分区表的操作方法

    博客分类:
  • DB
阅读更多
在大型的企业应用或企业级的数据库应用中,要处理的数据量通常可以达到几十到几百GB,有的甚至可以到TB级。虽然存储介质和数据处理技术的发展也很快,但是仍然不能满足用户的需求,为了使用户的大量的数据在读写操作和查询中速度更快,Oracle提供了对表和索引进行分区的技术,以改善大型应用系统的性能。

  使用分区的优点:

  ·增强可用性:如果表的某个分区出现故障,表在其他分区的数据仍然可用;

  ·维护方便:如果表的某个分区出现故障,需要修复数据,只修复该分区即可;

  ·均衡I/O:可以把不同的分区映射到磁盘以平衡I/O,改善整个系统性能;

  ·改善查询性能:对分区对象的查询可以仅搜索自己关心的分区,提高检索速度。

  Oracle数据库提供对表或索引的分区方法有三种:

  ·范围分区

  ·Hash分区(散列分区)

  ·复合分区

  下面将以实例的方式分别对这三种分区方法来说明分区表的使用。为了测试方便,我们先建三个表空间。

create tablespace dinya_space01
datafile ’/test/demo/oracle/demodata/dinya01.dnf’ size 50M
create tablespace dinya_space01
datafile ’/test/demo/oracle/demodata/dinya02.dnf’ size 50M
create tablespace dinya_space01
datafile ’/test/demo/oracle/demodata/dinya03.dnf’ size 50M

  1.1. 分区表的创建

  1.1.1. 范围分区

  范围分区就是对数据表中的某个值的范围进行分区,根据某个值的范围,决定将该数据存储在哪个分区上。如根据序号分区,根据业务记录的创建日期进行分区等。

  需求描述:有一个物料交易表,表名:material_transactions。该表将来可能有千万级的数据记录数。要求在建该表的时候使用分区表。这时候我们可以使用序号分区三个区,每个区中预计存储三千万的数据,也可以使用日期分区,如每五年的数据存储在一个分区上。

  根据交易记录的序号分区建表:

SQL> create table dinya_test
2 (
3 transaction_id number primary key,
4 item_id number(8) not null,
5 item_description varchar2(300),
6 transaction_date date not null
7 )
8 partition by range (transaction_id)
9 (
10 partition part_01 values less than(30000000) tablespace dinya_space01,
11 partition part_02 values less than(60000000) tablespace dinya_space02,
12 partition part_03 values less than(maxvalue) tablespace dinya_space03
13 );
Table created.

   建表成功,根据交易的序号,交易ID在三千万以下的记录将存储在第一个表空间dinya_space01中,分区名为:par_01,在三千万到六千万之间的记录存储在第二个表空间:

  dinya_space02中,分区名为:par_02,而交易ID在六千万以上的记录存储在第三个表空间dinya_space03中,分区名为par_03.

  根据交易日期分区建表:

SQL> create table dinya_test
2 (
3 transaction_id number primary key,
4 item_id number(8) not null,

5 item_description varchar2(300),
6 transaction_date date not null
7 )
8 partition by range (transaction_date)
9 (
10 partition part_01 values less than(to_date(’2006-01-01’,’yyyy-mm-dd’))
tablespace dinya_space01,
11 partition part_02 values less than(to_date(’2010-01-01’,’yyyy-mm-dd’))
tablespace dinya_space02,
12 partition part_03 values less than(maxvalue) tablespace dinya_space03
13 );
Table created.

  这样我们就分别建了以交易序号和交易日期来分区的分区表。每次插入数据的时候,系统将根据指定的字段的值来自动将记录存储到制定的分区(表空间)中。

  当然,我们还可以根据需求,使用两个字段的范围分布来分区,如partition by range ( transaction_id ,transaction_date), 分区条件中的值也做相应的改变,请读者自行测试。

  1.1.2. Hash分区(散列分区)

  散列分区为通过指定分区编号来均匀分布数据的一种分区类型,因为通过在I/O设备上进行散列分区,使得这些分区大小一致。如将物料交易表的数据根据交易ID散列地存放在指定的三个表空间中:

SQL> create table dinya_test
2 (
3 transaction_id number primary key,
4 item_id number(8) not null,
5 item_description varchar2(300),
6 transaction_date date
7 )
8 partition by hash(transaction_id)
9 (
10 partition part_01 tablespace dinya_space01,
11 partition part_02 tablespace dinya_space02,
12 partition part_03 tablespace dinya_space03
13 );
Table created.

  建表成功,此时插入数据,系统将按transaction_id将记录散列地插入三个分区中,这里也就是三个不同的表空间中。

  1.1.3. 复合分区

  有时候我们需要根据范围分区后,每个分区内的数据再散列地分布在几个表空间中,这样我们就要使用复合分区。复合分区是先使用范围分区,然后在每个分区内再使用散列分区的一种分区方法,如将物料交易的记录按时间分区,然后每个分区中的数据分三个子分区,将数据散列地存储在三个指定的表空间中:

SQL> create table dinya_test
2 (
3 transaction_id number primary key,
4 item_id number(8) not null,
5 item_description varchar2(300),
6 transaction_date date
7 )
8 partition by range(transaction_date)subpartition by hash(transaction_id)
9 subpartitions 3 store in (dinya_space01,dinya_space02,dinya_space03)
10 (
11 partition part_01 values less than(to_date(’2006-01-01’,’yyyy-mm-dd’)),
12 partition part_02 values less than(to_date(’2010-01-01’,’yyyy-mm-dd’)),
13 partition part_03 values less than(maxvalue)
14 );
Table created.

  该例中,先是根据交易日期进行范围分区,然后根据交易的ID将记录散列地存储在三个表空间中。
1.2. 分区表操作

  以上了解了三种分区表的建表方法,下面将使用实际的数据并针对按日期的范围分区来测试分区表的数据记录的操作。

  1.2.1. 插入记录:

SQL> insert into dinya_test values(1,12,’BOOKS’,sysdate);
1 row created.
SQL> insert into dinya_test values(2,12, ’BOOKS’,sysdate+30);
1 row created.

SQL> insert into dinya_test values(3,12, ’BOOKS’,to_date(’2006-05-30’,’yyyy-mm-dd’));
1 row created.
SQL> insert into dinya_test values(4,12, ’BOOKS’,to_date(’2007-06-23’,’yyyy-mm-dd’));
1 row created.
SQL> insert into dinya_test values(5,12, ’BOOKS’,to_date(’2011-02-26’,’yyyy-mm-dd’));
1 row created.
SQL> insert into dinya_test values(6,12, ’BOOKS’,to_date(’2011-04-30’,’yyyy-mm-dd’));
1 row created.
SQL> commit;
Commit complete.
SQL>

  按上面的建表结果,2006年前的数据将存储在第一个分区part_01上,而2006年到2010年的交易数据将存储在第二个分区part_02上,2010年以后的记录存储在第三个分区part_03上。

  1.2.2. 查询分区表记录:

SQL> select * from dinya_test partition(part_01);
TRANSACTION_ID ITEM_ID ITEM_DESCRIPTION TRANSACTION_DATE
--------------------------------------------------------------------------------
1 12 BOOKS 2005-1-14 14:19:
2 12 BOOKS 2005-2-13 14:19:
SQL>
SQL> select * from dinya_test partition(part_02);
TRANSACTION_ID ITEM_ID ITEM_DESCRIPTION TRANSACTION_DATE
--------------------------------------------------------------------------------
3 12 BOOKS 2006-5-30
4 12 BOOKS 2007-6-23
SQL>
SQL> select * from dinya_test partition(part_03);
TRANSACTION_ID ITEM_ID ITEM_DESCRIPTION TRANSACTION_DATE
--------------------------------------------------------------------------------
5 12 BOOKS 2011-2-26
6 12 BOOKS 2011-4-30
SQL>

  从查询的结果可以看出,插入的数据已经根据交易时间范围存储在不同的分区中。这里是指定了分区的查询,当然也可以不指定分区,直接执行select * from dinya_test查询全部记录。

  在也检索的数据量很大的时候,指定分区会大大提高检索速度。

  1.2.3. 更新分区表的记录:

SQL> update dinya_test partition(part_01) t set t.item_description=’DESK’ where
t.transaction_id=1;
1 row updated.
SQL> commit;
Commit complete.
SQL>

  这里将第一个分区中的交易ID=1的记录中的item_description字段更新为“DESK”,可以看到已经成功更新了一条记录。但是当更新的时候指定了分区,而根据查询的记录不在该分区中时,将不会更新数据,请看下面的例子:

SQL> update dinya_test partition(part_01) t set t.item_description=’DESK’ where
t.transaction_id=6;
0 rows updated.
SQL> commit;
Commit complete.
SQL>

  指定了在第一个分区中更新记录,但是条件中限制交易ID为6,而查询全表,交易ID为6的记录在第三个分区中,这样该条语句将不会更新记录。

  1.2.4. 删除分区表记录:

SQL> delete from dinya_test partition(part_02) t where t.transaction_id=4;
1 row deleted.
SQL> commit;
Commit complete.
SQL>

  上面例子删除了第二个分区part_02中的交易记录ID为4的一条记录,和更新数据相同,如果指定了分区,而条件中的数据又不在该分区中时,将不会删除任何数据。

  1.3. 分区表索引的使用:

  分区表和一般表一样可以建立索引,分区表可以创建局部索引和全局索引。当分区中出现许多事务并且要保证所有分区中的数据记录的唯一性时采用全局索引。

  1.3.1. 局部索引分区的建立:

SQL> create index dinya_idx_t on dinya_test(item_id)
2 local
3 (
4 partition idx_1 tablespace dinya_space01,
5 partition idx_2 tablespace dinya_space02,
6 partition idx_3 tablespace dinya_space03
7 );
Index created.
SQL>

  看查询的执行计划,从下面的执行计划可以看出,系统已经使用了索引:

SQL> select * from dinya_test partition(part_01) t where t.item_id=12;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=187)
1 0 TABLE ACCESS (BY LOCAL INDEX ROWID) OF ’DINYA_TEST’ (Cost=
2 Card=1 Bytes=187)
2 1 INDEX (RANGE SCAN) OF ’DINYA_IDX_T’ (NON-UNIQUE) (Cost=1
Card=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
334 bytes sent via SQL*Net to client
309 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client

1 sorts (memory)
0 sorts (disk)
2 rows processed
SQL>

  1.3.2. 全局索引分区的建立

  全局索引建立时global 子句允许指定索引的范围值,这个范围值为索引字段的范围值:

SQL> create index dinya_idx_t on dinya_test(item_id)
2 global partition by range(item_id)
3 (
4 partition idx_1 values less than (1000) tablespace dinya_space01,
5 partition idx_2 values less than (10000) tablespace dinya_space02,
6 partition idx_3 values less than (maxvalue) tablespace dinya_space03
7 );
Index created.
SQL>

  本例中对表的item_id字段建立索引分区,当然也可以不指定索引分区名直接对整个表建立索引,如:

SQL> create index dinya_idx_t on dinya_test(item_id);
Index created.
SQL>

  同样的,对全局索引根据执行计划可以看出索引已经可以使用:

SQL> select * from dinya_test t where t.item_id=12;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=3 Bytes=561)
1 0 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF ’DINYA_TEST’ (Cost
=2 Card=3 Bytes=561)
2 1 INDEX (RANGE SCAN) OF ’DINYA_IDX_T’ (NON-UNIQUE) (Cost=1
Card=3)
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
10 consistent gets
0 physical reads

0 redo size
420 bytes sent via SQL*Net to client
309 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
5 rows processed
SQL>

  1.4. 分区表的维护:

  了解了分区表的建立、索引的建立、表和索引的使用后,在应用的还要经常对分区进行维护和管理。日常维护和管理的内容包括:增加一个分区,合并一个分区及删除分区等等。下面以范围分区为例说明增加、合并、删除分区的一般操作:

  1.4.1. 增加一个分区:

SQL> alter table dinya_test
2 add partition part_04 values less than(to_date(’2012-01-01’,’yyyy-mm-dd’))
tablespace dinya_spa
ce03;
Table altered.
SQL>

  增加一个分区的时候,增加的分区的条件必须大于现有分区的最大值,否则系统将提示ORA-14074 partition bound must collate higher than that of the last partition 错误。

  1.4.2. 合并一个分区:

SQL> alter table dinya_test merge partitions part_01,part_02 into partition part_02;
Table altered.
SQL>

  在本例中将原有的表的part_01分区和part_02分区进行了合并,合并后的分区为part_02,如果在合并的时候把合并后的分区定为part_01的时候,系统将提示ORA-14275 cannot reuse lower-bound partition as resulting partition 错误。

  1.4.3. 删除分区:

SQL> alter table dinya_test drop partition part_01;
Table altered.
SQL>

  删除分区表的一个分区后,查询该表的数据时显示,该分区中的数据已全部丢失,所以执行删除分区动作时要慎重,确保先备份数据后再执行,或将分区合并。
分享到:
评论

相关推荐

    Oracle数据库分区表操作方法

    Oracle数据库分区表操作方法 Oracle数据库中的分区表是将大型数据表分割成多个小表,以提高应用系统的性能和方便数据管理。在本文中,我们将详细介绍分区表的使用方法和优点。 分区表的优点 使用分区表可以带来...

    Oracle数据库中分区表的操作方法详解

    Oracle数据库的分区表是针对大数据量管理的一种高效策略,它通过将大表分解为多个较小、更易管理和处理的部分,显著提升了数据查询和维护的效率。在大型企业应用中,处理的数据量可能达到GB乃至TB级别,传统的数据...

    Oracle数据库中分区表的操作方法.doc

    ### Oracle数据库中分区表的操作方法 #### 一、概述 在大数据处理的场景下,为了提升数据处理效率和系统性能,Oracle数据库引入了分区表技术。分区表是将一张大表按照一定的规则拆分成多个小表(即分区),每个...

    (Oracle管理)多做知识的积累 详解ORACLE数据库的分区表.docx

    Oracle数据库的分区表是一种高效管理和优化大数据量表的技术,它将一张大表在逻辑上保持完整,但在物理上分散到多个部分,每个部分称为分区。这有助于提高查询性能,增强可用性和简化维护工作。 首先,了解表空间和...

    oracle普通表转化为分区表的方法

    Oracle数据库中的分区表是一种高级的表组织形式,它将大表分成多个较小的部分,每个部分称为一个分区,以提高查询性能和管理效率。当表的大小超过2GB时,官方推荐使用分区表,因为它们在处理大量数据时具有显著优势...

    Oracle表分区详解(优缺点)

    表空间是Oracle数据库中存储数据的基本单位,它由一个或多个数据文件组成,容纳了数据库的所有对象,包括表。当表的数据量过大时,查询效率会降低,这时可以通过表分区来改善。分区表将一个大表在物理上分割成多个...

    Oracle分区表详解

    ### Oracle 分区表详解 #### 一、Oracle 分区简介 ...以上是 Oracle 分区表的基础概念及其操作的详细介绍。通过合理应用分区技术,不仅可以提高数据的可管理性,还能有效提升查询性能和系统可用性。

    oracle数据库操作手册

    Oracle数据库是全球广泛使用的大型关系型数据库管理系统,其强大的功能和稳定性使其在企业级应用中占据了重要地位。本操作手册将深入探讨Oracle数据库的核心概念、安装配置、管理维护以及SQL查询等多个方面,帮助...

    oralce数据库表分区介绍

    Oracle数据库中的表分区是一种优化大型数据表查询性能的技术,它将一个大表划分为多个较小、更易管理的部分,每个部分称为一个分区。通过分区,可以显著提高数据的存储、检索和维护效率。以下是关于Oracle数据库表...

    oracle数据库优化-分区共.pdf

    分区是Oracle数据库提供的一种高效管理超大型表和索引的方法。它将大表和索引分割成多个可管理的小块,以提高数据访问性能,减少管理时间,并通过并行处理增强系统可用性。 分区技术在Oracle 10g R2版本中支持四种...

    oracle_分区表数据定期迁移到其他数据库测试方案

    根据提供的标题、描述、标签及部分内容,我们可以了解到这项工作主要关注的是如何将Oracle数据库中的分区表数据定期迁移到另一个数据库,并且对整个过程进行了详细的技术规划。以下是对这些信息进行整理后得到的关键...

    oracle 数据库表生成器

    Oracle数据库表生成器是一款工具,专门用于将XML文件的内容转换为Oracle数据库中的表格结构和实际数据。这个过程涉及到了几个关键的IT知识点,包括Oracle数据库系统、XML数据格式、数据库设计与管理以及数据导入导出...

    Oracle分区表用法

    - 表空间(Tablespace):在Oracle数据库中,表空间是存储数据的主要容器,由一个或多个数据文件组成。所有的数据库对象,尤其是表,都存储在特定的表空间内。表空间允许数据库管理员管理存储资源,确保数据的合理...

    Oracle数据库查询优化的方法

    Oracle数据库查询优化是数据库管理中的关键环节,它旨在提高数据检索速度,降低系统资源消耗,提升整体系统性能。本文将深入探讨Oracle数据库查询优化的各种方法和技术。 首先,我们从SQL语句的编写入手。良好的SQL...

    oracle数据库基础知识

    Oracle数据库是全球广泛使用的大型关系型数据库管理系统,它在企业级数据存储和管理中扮演着核心角色。Oracle数据库基础知识是每个IT从业者或自学者必备的知识领域。以下将深入讲解Oracle数据库的一些关键概念和操作...

    Oracle数据库学习指南

    Oracle2: 1. 《Oracle8 优化技术》摘录 (第一章 安装) 2. 《Oracle8 优化技术》摘录 (第二章 内存-CPU) 3. 《Oracle8 优化技术》摘录 (第三章 输入-输出) 4. EXP、IMP 命令详解 ...52. 自动备份Oracle数据库

Global site tag (gtag.js) - Google Analytics