Oracle 分区表

Oracle 分区表

一、分区表：

随着表的不断增大，对于新纪录的增加、查找、删除等(DML)的维护也更加困难。对于数据库中的超大型表，可通过把它的数据分成若干个小表，从而简化数据库的管理活动。对于每一个简化后的小表，我们称为一个单个的分区。

 

对于分区的访问，我们不需要使用特殊的SQL查询语句或特定的DML语句，而且可以单独的操作单个分区，而不是整个表。同时可以将不同分区的数据放置到不同的表空间，比如将不同年份的销售数据，存放在不同的表空间，即年的销售数据存放到TBS_2001,2002年的销售数据存放到TBS_2002,依次类推，从而实现了分散存储，这将大大的简化大容量表的管理，提高查询性能及I/O并发等。

 

对于外部应用程序来说，虽然存在不同的分区，且数据位于不同的表空间，但逻辑上仍然是一张表

 

可以使用SQL*Loader，IMPDP，EXPDP，Import，Export等工具来装载或卸载分区表中的数据

 

关于分区表的功能实际上同SQL server 中的分区表是同样的概念，只不过SQL server中的数据存放到了文件组，相当于Oracle概念中的表空间。

 

 

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

 

Linux-6-64下安装Oracle 12C笔记

 

在CentOS 6.4下安装Oracle 11gR2(x64)

 

Oracle 11gR2 在VMWare虚拟机中安装步骤

 

Debian 下 安装 Oracle 11g XE R2

 

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

 

 

 

 

二、何时分区
 

当表达到GB大小且继续增长

需要将历史数据和当前的数据分开单独处理，比如历史数据仅仅需要只读，而当前数据则实现DML

 

三、分区的条件及特性

共性：不同的分区之间必须有相同的逻辑属性，比如表名，列名，数据类型，约束等，

 

个性：各个分区可以有不同的物理属性，比如pctfree, pctused, and tablespaces.

 

　　分区独立性：即使某些分区不可用，其他分区仍然可用。

 

　　特殊性：含有LONG、LONGRAW数据类型的表不能进行分区

 

四、分区的优点

1、提高查询性能：只需要搜索特定分区，而非整张表，提高查询速度

2、节约维护时间：单个分区的数据装载，索引重建，备份，维护等将远小于整张表的维护时间。

3、节约维护成本：可以单独备份和恢复每个分区

4、均衡I/O：将不同的分区映射到不同的磁盘以平衡I/O，提高并发

 

五、ORACLE分区类型：

范围分区、散列分区、列表分区、组合分区

可以对索���和表分区，全局索引只能按范围分区，可以将其定义在任何类型的分区或非分区表上。

通常全局索引比局部索引需要更多的维护

局部索引与基础表是等同分区的，用于反映其基础表的结构

 

1.Range分区：行映射到基于列值范围的分区

Range 分区，又成为范围分区，基于分区键值的范围将数据映射到所建立的分区上。这是最通用的分区类型。

创建范围分区时，必须指定以下内容

分区方法：range

分区列

标识分区边界的分区描述

 

使用Range分区的时候，要记住几条规则：

每个分区都包含VALUES LESS THAN字名，定义了分区的上层边界。任何等于和大于分区键值的二进制值都被添加到下一个高层分区中。

所有的分区，除了第一个，如果低于VALUES LESS THAN所定义的下层边界，都放在前面的分区中。

MAXVALUE可以用来定义最高层的分区。MAXVALUE表示了虚拟的无限值

 

示例：

create table sal_range

(salesman_id number(5),

salesman_name varchar2(30),

sales_amount number(10),

sales_date date)

partition by range (sales_date)--创建基于日期的范围分区并存储到不同的表空间

(

partition sal_jan2000 values less than(to_date('02/01/2000',

'DD/MM/YYYY')) tablespace sal_range_jan2000,

partition sal_feb2000 values less than(to_date('03/01/2000',

'DD/MM/YYYY')) tablespace sal_range_feb2000,

partition sal_mar2000 values less than(to_date('04/01/2000',

'DD/MM/YYYY')) tablespace sal_range_mar2000,

partition sal_apr2000 values less than(to_date('05/01/2000',

'DD/MM/YYYY')) tablespace sal_range_apr2000

);

 

create table r--创建基于值范围的分区，分区子句未指定表空间时则位于缺省的表空间

(a int)

partition by range (a)

(

partition p1 values less than (10),

partition p2 values less than (20),

partition p3 values less than (30),

partition p4 values less than (maxvalue)

);

 

select * from r partition (p1)--查看分区中的数据

 

一个分区的损坏不会影响其它分区的数据：

alter table r drop partiton p1

select * from r

select * from r partition (p4)

除分区数据不见外，其它都正常

 

partition by 用于指定分区方式

range 表示分区的方式是范围划分

partition pn 用于指定分区的名字

values less than 指定分区的上界（上限）

 

添加分区：

ALTER TABLE r

add partition p5 values less than (xxx ) tablespace xx;

 

查看分区表相关信息：

SELECT table_name,partition_name,subpartition_count,

tablespace_name,user_stats from user_tab_partitions;

 

获取创建分区表的元数据：

set long 10000

select dbms_metadata.get_ddl('TABLE','R','SCOTT') from dual;

表表名用户名区分大小写

 

2.Hash分区：散列分区

Hash分区能够很容易对数据进行分区，因为语法很简单，很容易实现。在下面这种

情况下，使用hash分区比range分区更好：

事先不知道需要将多少数据映射到给定范围的时候

分区的范围大小很难确定，或者很难平衡的时候

Range分区使数据得到不希望的聚集时

性能特性，如并行DML、分区剪枝和分区连接很重要的时候

创建散列分区时，必须指定以下信息

分区方法：hash

分区列

分区数量或单独的分区描述

 

分裂、删除和合并分区不能应用于Hash分区，但是，Hash分区能够合并和添加。

 

创建hash分区有两种方法：一种方法是指定分区数量，另一种方法是指定分区的名字，

但两者不能同时指定。

 

方法一：指定分区数量

create table dept2 (deptno number,deptname varchar2(32))

partition by hash(deptno) partitions 4;

 

方法二：指定分区的名字

create table dept3 (deptno number,deptname varchar2(32))

partition by hash(deptno)

(partition p1 tablespace p1,

partition p2 tablespace p2);

 

create table sales_hash

(salesman_id number(5),

salesman_name varchar2(30),

sales_amount number(10),

week_no number(2))

partition by hash (salesman_id)

partitions 4

store in (data1,data2,data3,data4)

 

data1,data2,data3,data4 为表空间名。

散列分区表的每个分区都被存储在单独的段中。

 

3.List分区：列表分区

List分区可以控制如何将行映射到分区中去。可以在每个分区的键上定义离散的值

不同于Range分区和Hash分区，

Range分区与分区相关联，为分区列假设了一个值的自然范围，故不可能将该值的范围以外的分区组织到一起。

hash分区时不允许对数据的划分进行控制，因为系统使用的是散列函数来划分数据的。

List分区的优点在于按照自然的方式将无序和不相关的数据集合分组。

List分区不支持多列分区，如果将表按列分区，那么分区键就只能有表的一个单独列组成。

Range分区和Hash分区可以对多列进行分区。

List分区时必须指定的以下内容

分区方法：list

分区列

分区描述，每个描述指定一串文字值(值的列表),它们是分区列(它们限定将被包括在分区中的行)的离散值

 

示例：

create table sales_list

(salesman_id number(5),

salesman_name varchar2(30),

sales_state varchar2(20),

sales_amount number(10),

sales_date date)

partition by list (sales_state)

(

partition sales_west values ('California','Hawaii') tablespace x,

partition sales_east values ('New York','Virginia') tablespace y,

partition sales_central values ('Texas','Illinois') tablespace z,

partition sales_other values(DEFAULT) tablespace o

);

 

添加分区：

alter table sales3 add partition hk values ('HK') tablespace xx

更多详情见请继续阅读下一页的精彩内容：

1 2 下一页