`

Oracle表分区和索引分区

阅读更多

表空间及分区表的概念

表空间:
是一个或多个数据文件的集合,所有的数据对象都存放在指定的表空间中,但主要存放的是表, 所以称作表空间。
 
分区表:
当表中的数据量不断增大,查询数据的速度就会变慢,应用程序的性能就会下降,这时就应该考虑对表进行分区。表进行分区后,逻辑上表仍然是一张完整的表,只是将表中的数据在物理上存放到多个表空间(物理文件上),这样查询数据时,不至于每次都扫描整张表。
表分区的具体作用
Oracle的表分区功能通过改善可管理性、性能和可用性,从而为各式应用程序带来了极大的好处。通常,分区可以使某些查询以及维护操作的性能大大提高。此外,分区还可以极大简化常见的管理任务,分区是构建千兆字节数据系统或超高可用性系统的关键工具。
 
分区功能能够将表、索引或索引组织表进一步细分为段,这些数据库对象的段叫做分区。每个分区有自己的名称,还可以选择自己的存储特性。从数据库管理员的角度来看,一个分区后的对象具有多个段,这些段既可进行集体管理,也可单独管理,这就使数据库管理员在管理分区后的对象时有相当大的灵活性。但是,从应用程序的角度来看,分区后的表与非分区表完全相同,使用 SQL DML 命令访问分区后的表时,无需任何修改。

 

分区概述

    为了简化数据库大表的管理,例如在数据仓库中一般都是TB级的数量级.ORACLE8以后推出了分区选项.分区将表分离在若于不同的表空间上,用分而治之的方法来支撑无限膨胀的大表,增强大表在物理一级的可管理性.将大表分割成较小的分区可以改善表的维护、备份、恢复、事务及查询性能。

 

什么时候使用分区表:
1、表的大小超过2GB。
2、表中包含历史数据,新的数据被增加都新的分区中。

 

分区的优点:

1、  增强可用性:如果表的一个分区由于系统故障而不能使用,表的其余好的分区仍可以使用;

2、  减少关闭时间:如果系统故障只影响表的一部份分区,那么只有这部份分区需要修复,矿能比整个大表修复花的时间更少;

3、  维护轻松:如果需要得建表,独产管理每个公区比管理单个大表要轻松得多;

4、  均衡I/O:可以把表的不同分区分配到不同的磁盘来平衡I/O改善性能;

5、  改善性能:对大表的查询、增加、修改等操作可以分解到表的不同分区来并行执行,可使运行速度更快,在数据仓库的TP查询特别有用。

6、  分区对用户透明,最终用户感觉不到分区的存在。  

 

 

create tablespace dw1 datafile 'D:\oracle\oradata\ora9\dw11.ora' size 50M create tablespace dw2 datafile 'D:\oracle\oradata\ora9\dw21.ora' size 50M


   

一、按范围分区:固名思义就是按一定range来分区,看下面的例子:

 

SQL> set linesize 1000

SQL> create table niegc_part

  2  (

  3  part_id integer primary key,

  4  part_date date,

  5  part_dec varchar2(100)

  6  )

  7  partition by range(part_date)

  8  (

  9  partition part_01 values less than(to_date('2006-01-01','yyyy-mm-dd')) tablespace dw1,

 10  partition part_02 values less than(to_date('2007-01-01','yyyy-mm-dd')) tablespace dw2,

 11  partition part_03 values less than(maxvalue) tablespace dw1

 12  );

 

表已创建。

 

SQL>

SQL> insert into niegc_part values(1,to_date('2005-12-30','yyyy-mm-dd'),'less 20

06-01-01');

 

已创建 1 行。

 

SQL> commit;

 

提交完成。

 

SQL> insert into niegc_part values(2,to_date('2006-01-01','yyyy-mm-dd'),'equal 2

007-01-01');

 

已创建 1 行。

 

SQL> commit;

 

提交完成。

 

SQL> insert into niegc_part values(3,sysdate,'sysdate');

 

已创建 1 行。

 

SQL> commit;

 

提交完成。

 

SQL>

SQL>

SQL> select * from niegc_part partition(part_01);

 

   PART_ID PART_DATE  PART_DEC

------------------------------------------

         1 30-12-05 less 2006-01-01

 

相信只要对oracle 有点熟,都能知道上面的range分区的意思了.

 

两个字段以上的range分区大同小异,请看下面的例子:

create table niegc_part ( part_id integer primary key, part_date date, part_dec varchar2(100) ) partition by range(part_id,part_date) ( partition part_01 values less than(1,to_date('2006-01-01','yyyy-mm-dd')) tablespace dw, partition part_02 values less than(10,to_date('2007-01-01','yyyy-mm-dd')) tablespace dw, partition part_03 values less than(maxvalue,maxvalue) tablespace dw );



 

 

 

 

 

二、Hash分区(散列分区) 散列分区通过指定分区编号来均匀分布数据的一种分区类型,因为通过在I/O设备上进行散列分区,使行这些分区大小一致。如将part_id的数据根据自身的情况散列地存放在指定的三个表空间中:

create table niegc_part

(

part_id integer primary key,

part_date date,

part_dec varchar2(100)

)

partition by hash(part_id)

(

partition part_01  tablespace dw1,

partition part_02  tablespace dw2

);

 

系统将按part_id将记录散列地插入三个分区中,这里也就是二个不同的表空间中。 

 

 

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

 

create table niegc_part

(

part_id integer primary key,

part_date date,

part_dec varchar2(100)

)

partition by range(part_date) subpartition by hash(part_id)

subpartitions 2 store in(dw1,dw2)

(

partition part_01 values less than(to_date('2006-01-01','yyyy-mm-dd')) tablespace dw1,

partition part_02 values less than(to_date('2007-01-01','yyyy-mm-dd')) tablespace dw2,

partition part_03 values less than(maxvalue) tablespace dw1

);

  

先根据part_date进行范围分区,然后根据交易的ID将记录散列地存储在二个表空间中。 

 

四、索引分区:

注意: 对某个字段已做了分区了,是不允许再建立索引分区的。这一点要非常注意。全局索引建立时global子句允许指定索引的范围值,这个范围值为索引字段的范围值:

 

create index idx_part_id on niegc_part(part_dec)

global partition by range(part_dec)

(

partition idx_1 values less than('1000') tablespace dw,

partition idx_2 values less than(maxvalue) tablespace dw

) 

 

局部索引分区的建立:(注意:表必须存在分区,此分区的个数必须和分区表的分区个数一样,不然是建立不起来的)

create index idx_part_id on niegc_part(part_dec)

local

(

partition idx_1 tablespace dw1,

partition idx_2 tablespace dw2

)

 

 

五、分区维护:(只对范围分区)

1)、增加一个分区:分区范围只能往上增,不能增加一个少于原有的分区:

alter table niegc_part add partition part_03 values less than(maxvalue)

2)、合并分区:(合并后的分区必须指下最后一个大value的分区)

alter table niegc_part merge partitions part_02,part_03 into  partition part_03

3)、删除一个分区:

alter table niegc_part drop partition part_01

 

 

六、总结

需要说明的是,本文在举例说名分区表事务操作的时候,都指定了分区,因为指定了分区,系统在执行的时候则只操作该分区的记录,提高了数据处理的速度。不要指定分区直接操作数据也是可以的。在分区表上建索引及多索引的使用和非分区表一样。此外,因为在维护分区的时候可能对分区的索引会产生一定的影响,可能需要在维护之后重建索引,相关内容请google分区表索引部分的文档。

 

http://moppet.taobao.com/

 

分享到:
评论

相关推荐

    Oracle 分区表 分区索引 索引分区详解

    虽然存储介质和数据处理技术的发展也很快,但是仍然不能满足用户的需求,为了使用户的大量的数据在读写操作和查询中速度更快,Oracle提供了对表和索引进行分区的技术,以改善大型应用系统的性能。

    oracle分区表分区索引.docx

    Oracle 分区表分区索引是 Oracle 数据库中的一种重要机制,它可以提高数据的存储和查询效率。下面将详细介绍 Oracle 分区表分区索引的类型、分类、创建方法和维护方式。 一、分区表和分区索引的概念 分区表是指将...

    Oracle 分区表 分区索引

    Oracle分区技术提供了强大的工具来管理和优化大型表和索引。通过合理选择分区策略和正确使用分区管理功能,可以显著提高数据库性能并降低管理复杂性。在实际应用中,应根据具体的业务需求和技术环境灵活运用这些分区...

    Oracle分区表和分区索引在VLDB中的研究.pdf

    "Oracle分区表和分区索引在VLDB中的研究" 本文研究了Oracle分区表和分区索引在VLDB(Very Large Databases)中的应用。分区表和分区索引是数据库管理中的关键技术之一,是VLDB中一个重要的性能提升机制。通过分析一...

    Oracle分区表及分区索引

    Oracle 分区表及分区索引 Oracle 分区表是指将一个大表分割成多个小表,每个小表称为一个分区,分区表的优点是可以提高查询性能、改善管理型、增强容错性等。 分区表的分类: 1. 范围分区(Range Partitioning) ...

    ORACLE分区与索引

    针对分区表,Oracle 提供了分区索引,它允许索引与分区策略相结合。例如,可以创建局部索引,每个分区都有自己的索引,或者创建全局索引,覆盖所有分区。局部索引通常在查询性能上有优势,而全局索引则适用于跨分区...

    oracle索引与分区索引介绍

    Oracle 索引与分区索引是 Oracle 数据库中两个重要的概念,它们都是为了提高查询性能和数据存储效率而设计的。在本文中,我们将详细介绍 Oracle 索引与分区索引的概念、特点、分类、创建方法、维护操作等方面的知识...

    分区索引,本地索引,全局索引的区别

    #### 一、Oracle分区索引概念及分类 在Oracle数据库中,分区索引是针对分区表的一种特殊索引类型,它可以显著提高对于大规模数据集的查询性能。根据索引是否与表的分区策略相匹配,分区索引可以分为两大类:本地...

    深入学习分区表及分区索引(详解oracle分区).docx

    - 物理属性可以不同,如PCTFREE(预留空间百分比)、PCTUSED(使用空间百分比)和表空间分配。 - 分区独立性保证即使部分分区不可用,其他分区仍可正常工作。 - 最大分区数量为64000,但LONG或LONG RAW列的表不...

    oracle表分区详解

    以上介绍了Oracle表分区的基本概念、优点、限制以及两种常见的分区类型——范围分区和列表分区,并给出了具体的创建示例。通过合理地使用表分区技术,可以有效地提高数据库系统的性能和可管理性。

    Oracle表分区详解

    除了以上基本类型,Oracle还提供了一些高级分区策略,如子分区、全局分区和本地分区等,以适应不同业务需求。 表分区的优点包括: 1. **性能提升**:查询只涉及相关分区,减少I/O操作,提高查询速度。 2. **维护...

    ORACLE表自动按月分区步骤

    通过上述步骤,我们可以看到分区表会自动为新数据所在月份创建一个新的分区`SYS_P183`,并且会自动生成相应的分区索引。 #### 八、总结 本文详细介绍了如何在Oracle数据库中实现表的自动按月分区及本地索引的创建...

    oracle表分区详细讲解

    ### Oracle表分区详解 #### 一、表空间与分区表概念 **表空间**:在Oracle数据库中,表空间是一个或多个数据文件的集合。所有数据对象(如表、索引等)都存放在特定的表空间中。由于主要存放的是表,因此被称为...

    Oracle分区表详解

    Oracle 的分区技术是一种用于管理和优化超大型表和索引的有效手段。通过将一个大型的表或者索引分割成多个较小且可管理的部分,分区技术能够显著提升数据库的性能、可用性和可维护性。 ##### 分区的作用: 1. **...

    Oracle分区表和索引

    逻辑上,所有分区共享相同的表名、列名、数据类型和约束,但物理属性如pctfree(空闲空间百分比)、pctused(使用空间百分比)和表空间可以各异。分区的独立性意味着即使部分分区不可用,其他分区仍能正常服务,提高...

    深入学习分区表及分区索引(详解oracle分区.docx

    在应用分区时,重要的是保持分区的逻辑一致性,即所有分区都必须有相同的数据结构,如列名、数据类型和约束,但物理属性如PCTFREE、PCTUSED和表空间可以不同。分区的透明性意味着SQL和DML操作无需了解数据是否已分区...

Global site tag (gtag.js) - Google Analytics