`
z_kanhai
  • 浏览: 51922 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

Oracle分区表详解 .

 
阅读更多
一、Oracle分区简介
ORACLE的分区是一种处理超大型表、索引等的技术。分区是一种“分而治之”的技术,通过将大表和索引分成可以管理的小块,从而避免了对每个表作为一个大的、单独的对象进行管理,为大量数据提供了可伸缩的性能。分区通过将操作分配给更小的存储单元,减少了需要进行管理操作的时间,并通过增强的并行处理提高了性能,通过屏蔽故障数据的分区,还增加了可用性。

二、Oracle分区优缺点
优点:

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

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

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

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

缺点:

分区表相关:已经存在的表没有方法可以直接转化为分区表。不过 Oracle 提供了在线重定义表的功能。

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

Hash分区(散列分区):
散列分区为通过指定分区编号来均匀分布数据的一种分区类型,因为通过在I/O设备上进行散列分区,使得这些分区大小一致。

List分区(列表分区):
当你需要明确地控制如何将行映射到分区时,就使用列表分区方法。与范围分区和散列分区所不同,列表分区不支持多列分区。如果要将表按列分区,那么分区键就只能由表的一个单独的列组成,然而可以用范围分区或散列分区方法进行分区的所有的列,都可以用列表分区方法进行分区。

范围-散列分区(复合分区):
有时候我们需要根据范围分区后,每个分区内的数据再散列地分布在几个表空间中,这样我们就要使用复合分区。复合分区是先使用范围分区,然后在每个分区内再使用散列分区的一种分区方法(注意:先一定要进行范围分区)

范围-列表分区(复合分区):
范围和列表技术的组合,首先对表进行范围分区,然后用列表技术对每个范围分区再次分区。与组合范围-散列分区不同的是,每个子分区的所有内容表示数据的逻辑子集,由适当的范围和列表分区设置来描述。(注意:先一定要进行范围分区)

四、Oracle表分区表操作
--Partitioning 是否为true
select * from v$option s order by s.PARAMETER desc

--创建表空间
CREATE TABLESPACE "PARTION_03"

LOGGING

DATAFILE 'D:ORACLEORADATAJZHUAPARTION_03.dbf' SIZE 50M

EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO

--删除表空间
drop tablespace partion_01



--范围分区技术语句
create table Partition_Test

(

PID number not null,

PITEM varchar2(200),

PDATA date not null

)

partition by range(PID)

(

partition part_01 values less than(50000) tablespace dinya_space01,

partition part_02 values less than(100000) tablespace dinya_space02,

partition part_03 values less than(maxvalue) tablespace dinya_space03

)

create table Partition_TTest

(

PID number not null,

PITEM varchar2(200),

PDATA date not null

)

partition by range(PDATA)

(

partition part_t01 values less than(to_date('2004-01-01','yyyy-mm-dd')) tablespace dinya_space01,

partition part_t02 values less than(to_date('2008-01-01','yyyy-mm-dd')) tablespace dinya_space02,

partition part_t03 values less than(maxvalue) tablespace dinya_space03

)

insert into Partition_Test(PID,PITEM,PDATA) select h.id,h.userid,h.rectime from st_handle h

select * from Partition_Test partition(part_01) t where t.pid = '1961'

--hash 分区技术
create table Partition_HashTest

(

PID number not null,

PITEM varchar2(200),

PDATA date not null

)

partition by hash(PID)

(

partition part_h01 tablespace dinya_space01,

partition part_h02 tablespace dinya_space02,

partition part_h03 tablespace dinya_space03

)

insert into Partition_HashTest(PID,PITEM,PDATA) select h.id,h.userid,h.rectime from st_handle h

select * from Partition_HashTest partition(part_h03) t where t.pid = '1961'



--复合分区技术
create table Partition_FHTest

(

PID number not null,

PITEM varchar2(200),

PDATA date not null

)

partition by range(PDATA) subpartition by hash(PID) subpartitions 3 store in (dinya_space01,dinya_space02,dinya_space03)

(

partition part_fh01 values less than(to_date('2004-01-01','yyyy-mm-dd')) tablespace dinya_space01,

partition part_fh02 values less than(to_date('2008-01-01','yyyy-mm-dd')) tablespace dinya_space02,

partition part_fh03 values less than(maxvalue) tablespace dinya_space03

)

insert into Partition_FHTest(PID,PITEM,PDATA) select h.id,h.userid,h.rectime from st_handle h

select * from Partition_FHTest partition(part_fh02) t where t.pid = '1961'

select * from Partition_FHTest partition(part_fh03) t

--速度比较
select * from st_handle h where h.rectime > to_date('2008-01-01','yyyy-mm-dd');

select * from Partition_FHTest partition(part_fh03) t where t.pdata > to_date('2008-01-01','yyyy-mm-dd');

--分区表操作
--增加一个分区
alter table Partition_Test add partition part_05 values less than (10020) tablespace dinya_space03

--查询分区数据
select * from Partition_FHTest partition(part_fh02) t

--修改分区里的数据
update Partition_FHTest partition(part_fh02) t set t.PITEM = 'JZHUA' where t.pid = '1961'

--删除分区里的数据
delete from Partition_FHTest partition(part_fh02) t where t.pid = '1961'

--合并分区
create table Partition_HB

(

PID number not null,

PITEM varchar2(200),

PDATA date not null

)

partition by range(PID)

(

partition part_01 values less than(50000) tablespace dinya_space01,

partition part_02 values less than(100000) tablespace dinya_space02,

partition part_03 values less than(maxvalue) tablespace dinya_space03

)

insert into Partition_HB(PID,PITEM,PDATA) select h.id,h.userid,h.rectime from st_handle h

select * from Partition_HB partition(part_03) t where t.pid = '100001'

alter table Partition_HB merge partitions part_01,part_02 into partition part_02;

--拆分分区
-- spilt partition 分区名 at(这里是一个临界区,比如:50000就是说小于50000的放在part_01,而大于50000的放在part_02中)

alter table Partition_HB split Partition part_02 at (50000) into (Partition part_01 tablespace dinya_space01, Partition part_02 tablespace dinya_space02);

--更改分区名
alter table Partition_HB rename Partition part_01_test to part_02;

五、Oracle索引分区表操作
分区表和一般表一样可以建立索引,分区表可以创建局部索引和全局索引。当分区中出现许多事务并且要保证所有分区中的数据记录的唯一性时采用全局索引。全局索引建立时 global 子句允许指定索引的范围值,这个范围值为索引字段的范围值。其实理论上有3中分区索引。

索引分区是在您建立了表分区后,要建索引就必须是建立索引分区。分2大类:一类是把索引信息建立在各个分区上,这叫局部索引分区(或叫本地索引分区)。另一类是把索引集中起来,叫全局索引。

Global索引(全局索引):
对于 global 索引,可以选择是否分区,而且索引的分区可以不与表分区相对应。当对分区进行维护操作时,通常会导致全局索引的 Invalid,必须在执行完操作后 Rebuild。Oracle9i 提供了 Update Global Indexes 语句,可以在进行分区维护的同时重建全局索引。



1:索引信息的存放位置与父表的Partition(分区)信息完全不相干。甚至父表是不是分区表都无所谓的。

create index dinya_idx_t on dinya_test(item_id) global partition by range(item_id) (

partition idx_1 values less than (1000) tablespace dinya_space01,

partition idx_2 values less than (10000) tablespace dinya_space02,

partition idx_3 values less than (maxvalue) tablespace dinya_space03

);

2:但是在这种情况下,如果父表是分区表,要删除父表的一个分区都必须要更新Global Index ,否则索引信息不正确

ALTER TABLE TableName DROP PARTITION PartitionName Update Global Indexes



建立方法:

create index ind_2 on sales (amount_sold)

global partition by range (amount_sold)

(partition d1 ,

partition d2);

因为全局索引的首个字段必须是range字段,所以就无所谓前缀和非前缀了,都是前缀。

经过试验,我觉得建立全局索引的速度要略逊于局部前缀索引。

也可能是我的能力问题,现在觉得建立分区还不然不建立索引。如果大家能给我解惑

Local索引(局部索引):
对于 local 索引,每一个表分区对应一个索引分区(就是说一个分区表一个字段只可以建一个局部索引),当表的分区发生变化时,索引的维护由 Oracle 自动进行;

1:索引信息的存放位置依赖于父表的Partition(分区)信息,换句话说创建这样的索引必须保证父表是Partition(分区),索引信息存放在父表的分区所在的表空间。

2:但是仅可以创建在父表为HashTable或者composite分区表的。

3:仅可以创建在父表为HashTable或者composite分区表的。并且指定的分区数目要与父表的分区数目要一致。

create index dinya_idx_t on dinya_test(item_id) local (

partition idx_1 tablespace dinya_space01,

partition idx_2 tablespace dinya_space02,

partition idx_3 tablespace dinya_space03

);

不指定索引分区名直接对整个表建立索引

create index dinya_idx_t on dinya_test(item_id);



建立方法:

create index ind_1 on dept (deptno)

local

(partition d1 ,

partition d2);

(1)局部前缀索引分区和局部非前缀分区。如果您拟建立的索引的首个字段,和进行分区时的range列一样,那就是局部前缀索引分区。

优点是:理论上(我认为的),比方说您以年代为range分区,2007年一个分区、2008年一个分区,然后您又在这个时间列上建立了局部前缀索引分区,那么ORACLE就会直接利用这个区上的索引仅进行这个分区上的搜索,所以效率会很高。

在我建立的2000万的表中进行查询,实践是,这个局部前缀复合索引的花销cost是5,而没有分区前是4。当然这也无所谓了。又进行了其他几个查询,其cost都相差无几。

(2)局部非前缀索引。如果您建立索引的列的首个字段不是range列,那么就叫局部非前缀索引。

优点是:如果您查一个电话号码,它在每年都会出现,当您要count汇总时,这种索引就会同时把这几个分区进行并行处理查询,速度理论上要快。

但我的试验比较令我失望:我建了一个2000万的无分区的表,然后把这个表又复制了一遍,进行了6个分区。但结果在对某列进行查询统计时,如果在一个分区,两者速度相差不大,分区的查询速度是:0.25m,无分区的查询速度是:0.065m。但在我期望的跨区统计时,分区的第一次统计时间是:61.875m,第二次是:10m;而无分区的表仅为:3.703m。

分享到:
评论

相关推荐

    oracle表分区详解.pdf

    总之,Oracle表分区是一项强大的数据库管理技术,它在处理大规模数据时提供了许多优势,但也需要数据库管理员掌握相关的知识和技能来有效管理分区表。随着数据量的不断增长,合理利用分区策略可以帮助企业节省维护...

    Oracle分区表详解

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

    oracle分区表详解

    Oracle分区表是Oracle数据库中的一种高级特性,它允许大型表和索引被划分为更小、更易于管理的部分,称为分区。这些分区可以在物理上存放在不同的表空间中,甚至可以分布在不同的磁盘上。Oracle数据库的分区技术,...

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

    本文将深入探讨Oracle分区表和分区索引的概念、应用场景和实现方式。 1. 使用分区的场景: - 数据量超过2GB的大表,因为大文件在32位操作系统上可能存在限制,并且备份时间较长。 - 包含历史数据的表,如将新数据...

    oracle分区表分区索引.docx

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

    Oracle表分区详解

    在实际应用中,还需要关注分区表的索引、物化视图、分区维护操作(如添加、合并和拆分分区)等方面,以确保系统性能和可维护性。 总结来说,Oracle表分区是大型数据库系统中的重要优化手段,通过合理规划和应用,...

    oracle表分区详解

    ### Oracle表分区详解 #### 一、概述 Oracle数据库中的表分区是一种高级组织技术,它通过将表的大数据集划分为较小的、更易于管理的部分(即分区),从而提高查询性能和可管理性。表分区可以按照不同的策略进行...

    Oracle表分区详解(优缺点)

    已存在的非分区表无法直接转化为分区表,需要通过特定的在线重定义操作来完成。此外,分区会增加数据库的复杂性,可能需要更多的存储空间,并且对数据库设计和维护提出了更高要求。 Oracle表分区主要有以下几种类型...

    Oracle分区表

    关于Oracle分区表详解与.......................................................................................................................................................................

    Linux下安装Oracle服务器步骤详解.doc

    对于小规模应用(10个并发用户以下),一个硬盘就足够了,创建一个专门的Oracle分区,并确保至少1500MB的剩余空间。然而,如果计划运行Oracle8i以支持更大规模的应用,推荐配置包括至少3块SCSI硬盘。这些硬盘可以...

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

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

    ORACLE分区表的创建

    ### ORACLE 分区表的创建详解 #### 一、概述 在Oracle数据库中,分区是一种高效的数据管理方式,尤其适用于大型表和索引组织表。通过将数据逻辑地分割成多个独立的部分(即分区),可以显著提高查询性能,并简化表...

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

    总结来说,Oracle分区是一种强大的数据库管理工具,特别适合处理大数据量和历史数据存储的情况。通过合理选择分区策略,可以极大地提升数据处理的速度和系统的整体性能。然而,设计和实施分区策略需要深思熟虑,以...

    oracle表分区详解【转】

    Oracle数据库中的表分区是数据库设计中的一个重要概念,它允许我们将大型数据表划分为较小、更易管理和查询的部分。本文将深入探讨Oracle表分区的详细知识,包括其原理、类型、优势以及如何在实际操作中应用。 一、...

    Oracle Partition分区详细总结.pdf

    总之,Oracle分区功能是管理大规模数据集的有效手段,它不仅提升了数据处理的性能,也简化了数据库的维护工作。通过对分区表的合理设计和管理,可以充分发挥Oracle数据库的强大能力,满足各种高性能和高可用性的需求...

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

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

    Oracle 分区表 分区索引

    ### Oracle 分区表与分区索引详解 #### 一、Oracle分区概述 在Oracle数据库中,分区技术是一种非常有效的管理大型表和索引的方法。通过将一个大的表或索引分成多个较小的部分(分区),可以显著提高查询性能,并...

Global site tag (gtag.js) - Google Analytics