Oracle分区是怎样优化数据库的

Oracle的分区可以分为：列表分区、范围分区、散列分区、复合分区。

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

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

3、维护轻松：如果需要建表，独自管理每个公区比管理单个大表要轻松得多；

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

5、改善性能：对大表的查询、增加、修改等 *** 作可以分解到表的不同分区来并行执行，可使运行速度更快；

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

这个可以根据时间自行设置的。

首先明确分区表和表分区的区别：表分区是一种思想，分区表示一种技术实现。当表的大小过G的时候可以考虑进行表分区，提高查询效率，均衡IO。oracle分区表是oracle数据库提供的一种表分区的实现形式。表进行分区后，逻辑上仍然是一张表，原来的查询SQL同样生效，同时可以采用使用分区查询来优化SQL查询效率，不至于每次都扫描整个表

1、按时间分区表创建：

create table t_test (

pk_id number(30) not null,

add_date_time DATE,

constraintPK_T_TEST primary key (pk_id)

)

PARTITION BY RANGE (add_date_time)

(

PARTITIONt_test_2013_less VALUES LESS THAN (TO_DATE('2013-01-01 00:00:00','yyyy-mm-ddhh24:mi:ss')) TABLESPACE TS_MISPS,

PARTITIONt_test_2013 VALUES LESS THAN (TO_DATE('2014-01-01 00:00:00','yyyy-mm-ddhh24:mi:ss')) TABLESPACE TS_MISPS,

PARTITION t_test_2014VALUES LESS THAN (TO_DATE('2015-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss'))TABLESPACE TS_MISPS

)

其中add_date_time为分区字段，每一年一个分区。

插入100W数据

declare

i int := 1;

yearVARCHAR2(20);

begin

loop

year := CASEmod(i, 3)

WHEN 0 THEN

'2012-01-14 12:00:00'

WHEN 1 THEN

'2013-01-14 12:00:00'

ELSE

'2014-01-14 12:00:00'

END;

insert into t_test values(i, to_date(year, 'yyyy-mm-dd hh24:mi:ss'));

exit when i= 1000000;

i := i + 1;

end loop;

end;

查看分区表的分区的详细信息

Select table_name,partition_name,high_value fromdba_tab_partitions where table_name='T_TEST';

2、分区表修改

21增加一个分区

分两种情况：1没有maxvalue分区。2有maxvalue分区。我们创建的分区就是没有maxValue的分区

1没有maxvalue分区添加新分区：

alter table t_test add partition t_test_2015 VALUESLESS THAN (TO_DATE('2015-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACETS_MISPS ;

2、有maxvalue分区添加新分区：

有了maxvalue，就不能直接add partition，而是需要max分区split。例如我们将创建的分区的语句修改下：

create table t_test (

pk_id number(30) not null,

add_date_time DATE,

constraintPK_T_TEST primary key (pk_id)

)

PARTITION BY RANGE (add_date_time)

(

PARTITIONt_test_2013_less VALUES LESS THAN (TO_DATE('2013-01-01 00:00:00','yyyy-mm-ddhh24:mi:ss')) TABLESPACE TS_MISPS,

PARTITIONt_test_2013 VALUES LESS THAN (TO_DATE('2014-01-01 00:00:00','yyyy-mm-ddhh24:mi:ss')) TABLESPACE TS_MISPS,

PARTITIONt_test_2014 VALUES LESS THAN (TO_DATE('2015-01-01 00:00:00','yyyy-mm-ddhh24:mi:ss')) TABLESPACE TS_MISPS,

PARTITION t_test_maxVALUES LESS THAN (MAXVALUE)

)

增加一个2016年的分区语句为：

alter table t_test split partition t_test_max at(TO_DATE('2016-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) into (partitiont_test_2015,partition t_test_max);

22删除一个分区

alter table t_test drop partition t_test_2014

注：droppartition时，该分区内存储的数据也将同时删除，你的本意是希望删除掉指定的分区但保留数据，你应该使用merge partition，执行该语句会导致glocal索引的失效需要重建全局索引

23合并分区

相邻的分区可以merge为一个分区，新分区的下边界为原来边界值较低的分区，上边界为原来边界值较高的分区，原先的局部索引相应也会合并，全局索引会失效，需要rebuild。

Alter table t_test merge partitions t_test_2013 ,t_Test_2014 into partition t_Test_2013_to_2

Oracle 分区功能可以提高许多应用程序的可管理性 性能与可用性 通过分区功能 可以将表 索引和索引组织表进一步细分为段 从而能够更精确地管理和访问这些数据库对象 Oracle 提供了种类繁多的分区方案以满足每种业务要求 而且 因为在 SQL 语句中分区是完全透明的 所以该功能几乎可应用于任何应用程序

分区功能的优势

分区功能通过改善可管理性 性能和可用性 从而为各式应用程序带来了极大的好处 通常 分区可以使某些查询以及维护 *** 作的性能大大提高 此外 分区还可以极大简化常见的管理任务 通过分区 数据库设计人员和管理员能够解决前沿应用程序带来的一些难题 分区是构建千兆字节数据系统或超高可用性系统的关键工具

分区功能的基本知识

分区功能能够将表 索引或索引组织表进一步细分为段 这些数据库对象的段叫做分区 每个分区有自己的名称 还可以选择自己的存储特性 从数据库管理员的角度来看 一个分区后的对象具有多个段 这些段既可进行集体管理 也可单独管理 这就使数据库管理员在管理分区后的对象时有相当大的灵活性 但是 从应用程序的角度来看 分区后的表与非分区表完全相同 使用 SQL DML 命令访问分区后的表时 无需任何修改

表的分区是通过 分区键 来实现的 分区键指的是一些列 这些列决定了某一行所在的分区 Oracle 数据库 g 提供了六项技术用于对表进行分区

范围分区

每个分区都由一个分区键值范围指定（对于一个以日期列作为分区键的表 年 月 分区包含分区键值为从 年 月 日 到 年 月 日 的行）

列表分区

每个分区都由一个分区键值列表指定（对于一个地区列作为分区键的表 北美 分区可能包含值 加拿大 美国 和 墨西哥 ）

散列分区

将散列算法用于分区键来确定指定行所在的分区

组合范围散列分区

范围和散列分区技术的组合 通过该组合 首先对表进行范围分区 然后针对每个单独的范围分区再使用散列分区技术进一步细分 索引组织表只能进行范围分区

组合范围列表分区

范围和列表分区技术的组合 通过该组合 首先对表进行范围分区 然后针对每个单独的范围分区再使用列表分区技术进一步细分 索引组织表可以按范围 列表或散列进行分区

Oracle 数据库 g 还提供了三种类型的分区索引

本地索引

本地索引是其分区方式与其所在基础表的分区方式一模一样的索引 本地索引的每个分区仅对应于其所在基础表的一个分区

全局分区索引

全局分区索引是使用不同于其所在表的分区键进行分区的索引 其所在表可以是分区表或非分区表 全局分区的索引可以使用范围或散列分区进行分区 例如 某个表可以按月份进行范围分区 因此具有十二个分区 而该表上的索引则可以使用不同的分区键进行范围分区 从而具有不同的分区数量

全局非分区索引

全局非分区索引基本上和非分区表的索引一样 索引结构是不分区的

Oracle 提供了一系列丰富的技术 可用于对表 索引和索引组织表进行分区 因此可以针对任何业务环境中的任何应用程序进行最佳的分区 Oracle 还提供一套完整的 SQL 命令 用于管理分区表 其中包括添加新分区 删除分区 分拆分区以及合并分区的命令

用分区功能提高可管理性

通过 Oracle 分区功能 可将表和索引分成更多 更小的可管理单元 从而使数据库管理员能以 化整为零 个个击破 的方式管理数据

使用分区功能 维护 *** 作可集中于表的特定部分 例如 数据库管理员可以只对表的一部分做备份 而不必对整个表做备份 对整个数据库对象的维护 *** 作 可以在每个分区的基础上进行 从而将维护工作分解成更容易管理的小块

利用分区功能提高可管理性的一个典型用法是支持数据仓库中的 滚动视窗 加载进程 假设数据库管理员每周向表中加载新数据 可以对该表进行范围分区 使每个分区包含一周的数据 这样加载进程只是简单地添加新的分区 添加一个分区的 *** 作比修改整个表效率高很多 因为 DBA 不需要修改任何其他分区

用分区功能提高性能

由于限制了所检查或 *** 作的数据数量 同时支持并行任务执行 Oracle 分区功能实现了性能上增益 这些特性包括

分区修整

分区修整是用分区功能提高性能的最简单最有价值的手段 分区修整常常能够将查询性能提高几个数量级 例如 假设某个应用程序包含一个存储订单历史记录的 Orders 表 并且此表已按周分区 查询一周的订单只需访问该订单表的一个分区 如果该订单表包含两年的历史记录 这个查询只需要访问一个分区而不是一百零四个 该查询的执行速度因为分区修整而有可能快一百倍 分区修整能与所有其他 Oracle 性能特性协作 Oracle 能将分区修整功能与任何索引技术 联接技术或并行访问方法结合使用

分区智能联接

分区功能可以通过称为分区智能联接的技术提高多表联接的性能 当两个表要联接在一起 而且每个表都用联接键来分区时 就可以使用分区智能联接 分区智能联接将大型联接分解成较小的发生在各个分区间的联接 从而用较少的时间完成全部联接 这就给串行和并行的执行都能带来显著的性能改善

用分区功能提高可用性

分区的数据库对象具有分区独立性 该分区独立性特点可能是高可用性战略的一个重要部分 例如 如果分区表的一个分区不能用 但该表的所有其他分区仍然保持在线并可用 那么这个应用可以继续针对该分区表执行查询和事务处理 只要不是访问不可用的分区 数据库 *** 作仍然能够成功运行

数据库管理员可以指定各分区存放在不同的表空间里 从而让管理员隔离其它表分区针对单个分区进行备份与恢复 *** 作 还有 分区功能可以减少计划停机时间 由于分区功能改善了性能 使数据库管理员能用相对较少的时间完成大型数据库对象的维护工作

未来发展方向

自从引入分区技术以来 Oracle 公司在每次推出重要版本时都会增加新的分区方法 Oracle 引入了范围分区功能 Oracle i 引入了散列和组合范围散列分区功能 Oracle i 引入了列表分区功能 在最新版本 Oracle 数据库 g 中 则增强了用于索引组织表和全局分区索引的分区策略 并且扩展了其用于所有分区维护 *** 作的并发索引维护功能 Oracle 公司致力于不断完善分区技术 确保满足所有的业务需求

结论

lishixinzhi/Article/program/Oracle/201311/16823

oracle分区表的分区有四种类型：范围分区、散列分区、列表分区和复合分区。

特点如下：

1、范围分区

就是根据数据库表中某一字段的值的范围来划分分区。

数据中有空值，Oracle机制会自动将其规划到maxvalue的分区中。

2、散列分区

根据字段的hash值进行均匀分布，尽可能地实现各分区所散列的数据相等。

散列分区即为哈希分区，Oracle采用哈希码技术分区，具体分区如何由Oracle说的算，也可能我下一次搜索就不是这个数据了。

3、列表分区

列表分区明确指定了根据某字段的某个具体值进行分区，而不是像范围分区那样根据字段的值范围来划分的。

4、复合分区

根据范围分区后，每个分区内的数据再散列地分布在几个表空间中，这样我们就要使用复合分区。复合分区是先使用范围分区，然后在每个分区同再使用散列分区的一种分区方法。

比如将part_date的记录按时间分区，然后每个分区中的数据分三个子分区，将数据散列地存储在三个指定的表空间中。

扩展资料：

分区的恢复方法：

如果数据库运行在archive 模式下，那么一旦数据库损坏则可以通过冷备份(热备份)和归档备份将数据库恢复到断点状态。

数据库控制文件恢复（假设所有控制文件均被破坏）：

数据库基于文件系统： 利用 *** 作系统的tar、cp等命令即可。

数据库基于裸设备：dd if=$ORACLE_BASE/conbak of=/dev/rdrd/drd1 seek=12

参考资料来源：百度百科-oracle数据库

将表分区主要是为了减少I/O争用，提高性能，也可以按年或按月卸载数据，使得历史数据库的卸载更加方便。但是每年做一个分区，每个月一个子分区，每个分区需要分配1个表空间，一年就需要13个表空间，过多的表空间和数据文件使得对增加了空间管理的负担，如果数据量不是特别大，尽量不用分区表。

有很多种方法可以做分区表转换，常见的有：

1、CTAS，在建分区表时就把源表数据插进去

2、建分区表，从源表导出，再导入分区表，10g可以用数据泵

3、在线重定义，不影响业务，但速度慢些

2亿数据如果按平均行长70也就是十几g，不会很慢，只要存储不太差，估计个把小时怎么也完事了。在线重定义比较慢，但一晚上也没问题，30g的搞过6，7个小时。

以上就是关于Oracle分区是怎样优化数据库的全部的内容，包括:Oracle分区是怎样优化数据库的、oracle 分区 应该什么时候进行、Oracle分区功能提高应用程序性能等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！