oracle数据库是怎样实现按月分区的

查询的时候按table来查，一个表的所有分区都属于table。

但每个分区都是单独的segment，如果查询限制了分区键，那么查询只落在特定的segment，而segment在底层对应的数据块是不同的，这样可以减少数据的访问。

举个例子来说吧：

create table tableA （ STATIS_DAY VARCHAR2(8)）

partition by list (STATIS_DAY)

(

partition PART_20110522 values ('20110522')；

partition PART_20110522 values ('20110523')；

partition PART_20110522 values ('20110524')

）;

那么 你在写sql的时候

直接 select from tableA where STATIS_DAY between '20110524' and '20110523' 就是查询多个分区了啊！

首先你要理解分区的意义；

select from tableA partition(PART_20110522 ); 就是单独查询这个分区。不过其实跟

select from tableA where STATIS_DAY ='20110522' 是一个样子的~~~

分区表的相关资料：>

Hash Global分区索引介绍

HASH-Partitioned Global索引是Oracle 10g开始提供的新特性。而在以前的版本中

，Oracle只支持Range-Partitioned Global索引。HASH-Partitioned Global索引的好处如下：

比Range-Partitioned Global索引易于实施。HASH-Partitioned Global索引是根据

索引字段值，通过Oracle内部的HASH算法自动均匀散列到定义的分区中。而

Range-Partitioned Global索引需要根据索引字段值的范围进行分区，因此实施和

维护的难度都大。

HASH-Partitioned Global索引适合于在并发量、吞吐量很大的交易系统（OLTP）

中，对某些字段的访问冲突。尤其是sequence字段值。

HASH-Partitioned Global索引适合于大批量的数据查询。HASH-Partitioned Global索引不仅可以提供分区之间的并行查询，

而且在分区内也可进行并行查询的处理。

建立分区索引必须指定表空间，并且指定的表空间要与数据表空间分开，

这样便于管理，同时尽可能分开索引和数据的IO访问，提高效率

from askmaclean

其实不需要拆分表，分区就可以，还是原来的表名，只是将原来的表分成了若干的分区，这样能起到分表的效果，还不用分成很多的表。

比如你原来的表的名字是A，那么将该表改为A1，然后从新建立一个分区表A，分区的依据是班级，也就是list分区，也就是一般意义上的列表分区表。

然后再将A1的数据插入新A表就可以了。

至于分区表的建立方式，往上很多，可以自行查找。

这样 *** 作查询的语句不需要变，只是在不跨分区查询的情况下，相当于分成了若干张表去查询。比如查询1班的成绩，那么就是在1班的分区内，不会有2班的问题，就相当于你用一个指头就能解决问题，不会动用这个手一样。

如果分表的话，那么假设有12个班，那么就要建立12张表，这样的话，语句就要写12次，冗余太大了。

代码简单就是你复制的那样

具体的实现方式就是

在你ORD_ACT_PART01 分区中，要存放日期小于2003年5月1号的数据，也就是存放2003年4月的，以及以前的所有

ORD_ACT_PART02 分区中存放2003年5月的数据，但是因为之前有了2003年4月的存放在分区1里，所以这里只存放5月的了，后边也类似

但是你这个吧，只建了3个分区，到存放7月的数据，就会提示插入错误了，因为你没有存放7月的分区，所以只能再建，语法就是跟楼上那些人写的差不多了

ALTER TABLE

ORDER_ACTIVITIES

ADD PARTITION ORD_ACT_PART04 VALUES LESS THAN

(TO_DATE('01-AUG-2003','DD-MON-YYYY')) TABLESPACE ORD_TS04

不过建议起名时最好p200301,p200302这样的一目了然

Oracle提供了分区技术以支持VLDB(Very Large DataBase)。分区表通过对分区列的判断，把分区列不同的记录，放到不同的分区中。分区完全对应用透明。

Oracle的分区表可以包括多个分区，每个分区都是一个独立的段（SEGMENT），可以存放到不同的表空间中。查询时可以通过查询表来访问各个分区中的数据，也可以通过在查询时直接指定分区的方法来进行查询。

分区提供以下优点：

由于将数据分散到各个分区中，减少了数据损坏的可能性；

可以对单独的分区进行备份和恢复；

可以将分区映射到不同的物理磁盘上，来分散IO；

提高可管理性、可用性和性能。

Oracle提供了以下几种分区类型：

范围分区（range）；

哈希分区（hash）；

列表分区（list）；

范围－哈希复合分区（range-hash）；

范围－列表复合分区（range-list）。

Oracle的普通表没有办法通过修改属性的方式直接转化为分区表，必须通过重建的方式进行转变，下面介绍三种效率比较高的方法，并说明它们各自的特点。

方法一：利用原表重建分区表。

步骤：

SQL> CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);

表已创建。

SQL> INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTS;

已创建6264行。

SQL> COMMIT;

提交完成。

SQL> CREATE TABLE T_NEW (ID, TIME) PARTITION BY RANGE (TIME)

2 (PARTITION P1 VALUES LESS THAN (TO_DATE('2004-7-1', 'YYYY-MM-DD')),

3 PARTITION P2 VALUES LESS THAN (TO_DATE('2005-1-1', 'YYYY-MM-DD')),

4 PARTITION P3 VALUES LESS THAN (TO_DATE('2005-7-1', 'YYYY-MM-DD')),

5 PARTITION P4 VALUES LESS THAN (MAXVALUE))

6 AS SELECT ID, TIME FROM T;

表已创建。

SQL> RENAME T TO T_OLD;

表已重命名。

SQL> RENAME T_NEW TO T;

表已重命名。

SQL> SELECT COUNT() FROM T;

COUNT()

----------

6264

SQL> SELECT COUNT() FROM T PARTITION (P1);

COUNT()

----------

0

SQL> SELECT COUNT() FROM T PARTITION (P2);

COUNT()

----------

6246

SQL> SELECT COUNT() FROM T PARTITION (P3);

COUNT()

----------

18

优点：方法简单易用，由于采用DDL语句，不会产生UNDO，且只产生少量REDO，效率相对较高，而且建表完成后数据已经在分布到各个分区中了。

不足：对于数据的一致性方面还需要额外的考虑。由于几乎没有办法通过手工锁定T表的方式保证一致性，在执行CREATE TABLE语句和RENAME T_NEW TO T语句直接的修改可能会丢失，如果要保证一致性，需要在执行完语句后对数据进行检查，而这个代价是比较大的。另外在执行两个RENAME语句之间执行的对T的访问会失败。

适用于修改不频繁的表，在闲时进行 *** 作，表的数据量不宜太大。

方法二：使用交换分区的方法。

步骤：

SQL> CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);

表已创建。

SQL> INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTS;

已创建6264行。

SQL> COMMIT;

提交完成。

SQL> CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME)

2 (PARTITION P1 VALUES LESS THAN (TO_DATE('2005-7-1', 'YYYY-MM-DD')),

3 PARTITION P2 VALUES LESS THAN (MAXVALUE));

表已创建。

SQL> ALTER TABLE T_NEW EXCHANGE PARTITION P1 WITH TABLE T;

表已更改。

SQL> RENAME T TO T_OLD;

表已重命名。

SQL> RENAME T_NEW TO T;

表已重命名。

SQL> SELECT COUNT() FROM T;

COUNT()

----------

6264

优点：只是对数据字典中分区和表的定义进行了修改，没有数据的修改或复制，效率最高。如果对数据在分区中的分布没有进一步要求的话，实现比较简单。在执行完RENAME *** 作后，可以检查T_OLD中是否存在数据，如果存在的话，直接将这些数据插入到T中，可以保证对T插入的 *** 作不会丢失。

不足：仍然存在一致性问题，交换分区之后RENAME T_NEW TO T之前，查询、更新和删除会出现错误或访问不到数据。如果要求数据分布到多个分区中，则需要进行分区的SPLIT *** 作，会增加 *** 作的复杂度，效率也会降低。

适用于包含大数据量的表转到分区表中的一个分区的 *** 作。应尽量在闲时进行 *** 作。

方法三：Oracle9i以上版本，利用在线重定义功能

步骤：

SQL> CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);

表已创建。

SQL> INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTS;

已创建6264行。

SQL> COMMIT;

提交完成。

SQL> EXEC DBMS_REDEFINITIONCAN_REDEF_TABLE(USER, 'T', DBMS_REDEFINITIONCONS_USE_PK);

PL/SQL 过程已成功完成。

SQL> CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME)

2 (PARTITION P1 VALUES LESS THAN (TO_DATE('2004-7-1', 'YYYY-MM-DD')),

3 PARTITION P2 VALUES LESS THAN (TO_DATE('2005-1-1', 'YYYY-MM-DD')),

4 PARTITION P3 VALUES LESS THAN (TO_DATE('2005-7-1', 'YYYY-MM-DD')),

5 PARTITION P4 VALUES LESS THAN (MAXVALUE));

表已创建。

SQL> EXEC DBMS_REDEFINITIONSTART_REDEF_TABLE(USER, 'T', 'T_NEW', -

> 'ID ID, TIME TIME', DBMS_REDEFINITIONCONS_USE_PK);

PL/SQL 过程已成功完成。

SQL> EXEC DBMS_REDEFINITIONFINISH_REDEF_TABLE('YANGTK', 'T', 'T_NEW');

PL/SQL 过程已成功完成。

SQL> SELECT COUNT() FROM T;

COUNT()

----------

6264

SQL> SELECT COUNT() FROM T PARTITION (P2);

COUNT()

----------

6246

SQL> SELECT COUNT() FROM T PARTITION (P3);

COUNT()

----------

18

优点：保证数据的一致性，在大部分时间内，表T都可以正常进行DML *** 作。只在切换的瞬间锁表，具有很高的可用性。这种方法具有很强的灵活性，对各种不同的需要都能满足。而且，可以在切换前进行相应的授权并建立各种约束，可以做到切换完成后不再需要任何额外的管理 *** 作。

不足：实现上比上面两种略显复杂。

适用于各种情况。

这里只给出了在线重定义表的一个最简单的例子，详细的描述和例子可以参考下面两篇文章。

Oracle的在线重定义表功能：

Oracle的在线重定义表功能（二）：

索引也可以进行分区，分区索引有两种类型：global和local。对于local索引，每一个表分区对应一个索引分区，当表的分区发生变化时，索引的维护由Oracle自动进行。对于global索引，可以选择是否分区，而且索引的分区可以不与表分区相对应。当对分区进行维护 *** 作时，通常会导致全局索引的INVALDED，必须在执行完 *** 作后REBUILD。Oracle9i提供了UPDATE GLOBAL INDEXES语句，可以使在进行分区维护的同时重建全局索引。

全局索引可以包含多个分区的值 局部索引比全局索引容易管理,而全局索引比较快

注意:不能为散列分区 或者 子分区创建全局索引

Oracle的分区功能十分强大。不过用起来发现有两点不大方便：

第一是已经存在的表没有方法可以直接转化为分区表。不过Oracle提供了在线重定义表的功能，可以通过这种方式来完成普通表到分区表的转化。可以参考这个例子：

第二点是如果采用了local分区索引，那么在增加表分区的时候，索引分区的表空间是不可控制的。如果希望将表和索引的分区分开到不同的表空间且不同索引分区也分散到不同的表空间中，那么只能在增加分区后，对新增的分区索引单独rebuild。

Oracle最大允许存在多少个分区呢

我们可以从Oracle的Concepts手册上找到这个信息，对于Oracle9iR2:

Tables can be partitioned into up to 64,000 separate partitions

对于Oracle10gR2，Oracle增强了分区特性：

Tables can be partitioned into up to 1024K-1 separate partitions

关于何时应该进行分区,Oracle有如下建议:

■ Tables greater than 2GB should always be considered for partitioning

■ Tables containing historical data, in which new data is added into the newest partition A typical example is a historical table where only the current month's data is updatable and the other 11 months are read only

这些信息是在网上查到的，测试了下确实可以用。

现在某家企业的Oracle数据库中有一张产品信息表 这张表中的记录已经超过了 万条 其中成品信息大概 万条 零件记录有 万条左右 剩余的都是包装信息 数据库工程师通过相关的分析与监测 用户访问这张表的时候 会有严重的等待现象 这主要是因为这张表中的数据存放在同一块硬盘上 当不同的用户并发访问这张表时 会因为磁盘I/O性能的瓶颈 而导致等待 如下图所示 当各位读者遇到这种情况 该如何采取措施来优化性能呢笔者这里的建议是采用Oracle分区表减少磁盘的I/O冲突 改善数据库的性能

一 分区表的原理与优势

分区表对于提高大表的访问性能会有很大的帮助 如上图所示 可以将一张产品信息表分按产品类别分为三个部分 分别为成品信息 零件信息和原材料信息 然后将这三部分对应一个单独的分区 并将它们存放在不同的硬盘上 此时当不同的用户访问不同的信息时 就可以有效的降低磁盘的I/O冲突 即使是同一个用户 需要同时访问这三部分信息 如产品的物料清单时 由于其分别从不同的硬盘中读取数据 为此也可以明显的降低磁盘I/O冲突

所以分区的基本原理就是通过访问一个表或者索引的较小片断 而不是访问整个表和索引 以提高数据库的性能 当然这有一个前提条件 需要将一个表的不同分区放置在不同的磁盘上 此时磁盘整体的吞吐量就会成倍上升

采取分区 不仅可以提高用户访问时的性能 而且还可以提高备份时的灵活性 如上面这个例子 将产品信息根据其类别分为不同的区 并将它们保存在不同的硬盘上 此时就可以对各自的分区进行单独的备份 如最近因为国家环保的要求 原材料信息进行了大规模的调整 此时就可以对原材料信息所在的分区数据进行单独的备份 这不仅可以减少备份的时间 而且可以降低备份作业过程中出现的I/O冲突问题

一般来说 分区表的优势有一个前提 就是需要将分区数据放置在不同的磁盘上 如果不这么做的话 那么分区往往很难起到降低磁盘I/O的效果 所以为了最大程度的降低一个大表的磁盘I/O(特别是经常会有并发行的访问) 此时应该将表分割到多个分区上 然后再将这些分区存放在不同的磁盘上

二 分区表模式的选择

那么该如何对表进行分区呢这又是一个比较关键的问题 根据经验 笔者认为要让分区取得更好的效果 分区表模式的选择至关重要 也就是说 按什么内容对表进行分区管理 有时候 先同的数据 采取不同的分区表模式 往往会有不同的效果 在下面的内容中 笔者会结合企业实际应用的情景 对分区表模式的选择进行举例 希望这些内容能够帮助各位读者 更好的维护分区表

第一种模式 按行来进行分区

上面这个例子中 笔者谈到有一张产品信息表 其包括成品信息 零件信息和原材料信息 当这张表的记录比较多 并且当用户访问这张表时已经出现了严重的I/O冲突的时候 则就可以根据行记录来进行分区 如在产品信息表中有一个产品类别的字段 数据库管理员就可以根据这个字段对标进行分区 具体的分区方法也比较简单 在建立表的时候 在产品类别字段后加上Partion关键字 然后指定按字段的内容进行分区 这个 *** 作比较简单 笔者就不过多展开了 笔者这里需要强调的是 分区完之后一定要将数据存放在不同的磁盘上 即不同的表空间 否则的话 不能够起到改善磁盘I/O的效果

第二种模式 按列来进行分区

在实际工作中 还有这么一种情况 如现在有一张员工信息表 这这张表中除了包含员工的基本信息 如身份z号码 姓名 籍贯等内容 还包括员工的身份z复印件或者照片等信息 大家都知道 信息的数据流量是很大的 有可能一张身份z复印件的数据流量相当于几千条的员工基本信息 而且 当用户访问员工信息表的时候 并不是每个时候都需要查看身份z复印件 大部分时候他们可能只是查询员工的****或者住址等等 另外 一般身份z复印件等照片不会随意更改 而员工的****或这住址等等 则更改比较频繁 此时如果需要更改员工信息 却将不需要更改的员工身份z复印件也查询出来 显然那这会加重磁盘的I/O冲突

当企业存在这种情况时 也可以对这个表进行分区 此时分区并不是对行进行分区 而是对列进行分区 如可以将身份z复印件信息或者照片信息分为一个独立的分区 并将其保存在另外一个硬盘上 这么做 能够带来如下的好处

一是在数据访问时 可以指定是否需要查询身份z信息所在的分区 在查询员工信息的时候 在语句中可以指定从哪个分区中查询信息 当用户平时只是查询员工的****或者住址时 就不需要访问身份z复印件所在的分区 如可以使用如下语句查询

Select from ad_user partition(uinfo) –假设员工的基本信息存放在分区Unifo中

在查询语句中 使用Partition关键字可以指定其查询的是哪个分区 如果不特指的话 则系统会查询这个表所对应的全部分区 而指定的话 就只访问某个特定分区的内容 上面这条语句 就只读取Uinfo分区中的信息 而不会读取身份z复印件等相关信息 如此的话 就可以降低磁盘的I/O冲突 减少不必要的数据流量 提高查询的性能

二是方便对数据的备份 根据使用习惯 一般身份z复印件或者员工照片等信息不怎么会更改 为此对这些数据的备份频率可以比较低一点 况且这些信息的容量往往会很大 如果经常对其进行备份 显然会增加磁盘的I/O负担 而对于员工的联系信息或者住址等等 其变化的频率就会高许多 对这些信息就需要进行经常性的备份 对大表进行分区管理 还有一个很大的优势就在于可以对各个分区中的数据采取独立的备份 为此就可以对身份z复印件所在的分区进行单独的备份 如一个月或者有大的变动时进行备份 而对于其他的信息则可以每天进行备份 这就可以实现在性能与安全方面的均衡 一般来说 在访问某个表时 如果经常需要访问的信息只是特定的几列 而不需要访问的信息容量比较大 此时就可以采用按列分区的模式 在这种情况下 用户就可以在查看某个分区内容的时候避免访问其他分区 同时还可以在不妨碍其它分区的情况下对某个分区的数据进行独立的备份

第三种分区模式 散列分区

有时候某个表可能没有明显的分区特征 即不符合上面提到的这些情况 但是表中的记录又非常的多 在这种情况下 也有必要进行分区 不过此时我们可以让系统进行随机的分区 这种分区模式就叫做散列分区 通常情况下 为了提高散列分区的效果 即得到一个比较均匀的分布 往往可以将 的N次方指定为散列分区数 一般来说 N越大 其分布的越均匀

lishixinzhi/Article/program/Oracle/201311/16752

1

sql>

desc

dba_segments

name

null

type

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

--------

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

owner

varchar2(30)

segment_name

varchar2(81)

partition_name

varchar2(30)

segment_type

varchar2(18)

segment_subtype

varchar2(10)

tablespace_name

varchar2(30)

header_file

number

header_block

number

bytes

number

blocks

number

extents

number

initial_extent

number

next_extent

number

min_extents

number

max_extents

number

max_size

number

retention

varchar2(7)

minretention

number

pct_increase

number

freelists

number

freelist_groups

number

relative_fno

number

buffer_pool

varchar2(7)

select

bytes

from

dba_segments

where

partition_name='xxxx'

或者

2

sql>

desc

dba_tab_partitions

name

null

type

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

--------

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

table_owner

varchar2(30)

table_name

varchar2(30)

composite

varchar2(3)

partition_name

varchar2(30)

subpartition_count

number

high_value

long

high_value_length

number

partition_position

number

tablespace_name

varchar2(30)

pct_free

number

pct_used

number

ini_trans

number

max_trans

number

initial_extent

number

next_extent

number

min_extent

number

max_extent

number

max_size

number

pct_increase

number

freelists

number

freelist_groups

number

logging

varchar2(7)

compression

varchar2(8)

compress_for

varchar2(18)

num_rows

number

blocks

number

empty_blocks

number

avg_space

number

chain_cnt

number

avg_row_len

number

sample_size

number

last_analyzed

date

buffer_pool

varchar2(7)

global_stats

varchar2(3)

user_stats

varchar2(3)

select

avg_row_lennum_rows

size

from

dba_tab_partitions

where

partition_name='xxxx'

note:2

为平均值，和1

计算出来的值可能有出入。

以上就是关于oracle数据库是怎样实现按月分区的全部的内容，包括:oracle数据库是怎样实现按月分区的、oracle中分区查询时，怎样一次查询多个分区、请问oracle数据库中该如何设计分区索引partition index等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！