Oracle数据库表分区

最新推荐文章于 2024-08-14 23:11:00 发布

weixin_33998125

最新推荐文章于 2024-08-14 23:11:00 发布

阅读量112

点赞数

文章标签：数据库运维

原文链接：https://my.oschina.net/VILLE/blog/1631906

版权

为什么80%的码农都做不了架构师？>>>

一、几个基本概念

表空间以及分区表概念

表空间:是一个或多个数据文件的集合，所有的数据对象都存放在指定的表空间中，主要作用是存储表;

分区表:当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。

表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表。

注意：

1.分区表中，每个分区的逻辑结构必须相同。如：列名、数据类型;

2.分区表中，每个分区的物理存储参数可以不同。如：各个分区所在的表空间;

3.各个分区可以存放在不同的表空间中，但这些表空间所使用的块大小（block_size）必须一致;

4.除了包含LONG以及LONG RAW字段的表无法使用分区外，其他表均可以使用分区，包括含有LOB字段的表;

5.对于应用而言完全透明，分区前后没有变化，不需要进行修改;

二、表分区意义与作用

意义与作用

表分区功能通过改善可管理性、性能和可用性，从而为应用程序带来了极大的好处。通常，分区可以使某些查询以及维护操作的性能大大提高。此外,分区还可以极大简化常见的管理任务，分区是构建千兆字节数据系统或超高可用性系统的关键工具。

分区功能能够将表、索引或索引组织表进一步细分为片段，这些数据库对象的片段统称为分区;每个分区都有各自名称，可以定义各自的存储特性。从DBA角度来讲，一个分区后的对象有多个段，这些段可以集中式管理，亦可单独管理,这就使DBA在管理分区后的对象时有相当大的灵活性。从应用角度来看分区后的表与非分区表完全相同，使用 SQL DML 命令访问分区后的表时，无需任何修改。

表分区场景

1、表的大小超过2GB；

2、表中包含历史数据，新的数据被增加都新的分区中；

三、表分区的优点与局限性

表分区有以下优点

1.改善查询性能:对于用户的请求，只检索需要的分区;

2.增强可用性:某个分区出现故障不会影响其它分区表使用;

3.维护方便:可以在分区级别，针对单独的分区，进行索引的维护、数据的加载以及备份恢复等操作,大大降低了维护时长;

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

表分区局限性

已经存在的表没有方法可以直接转化为分区表，在oracle中提供了在线重定义表的功能。

四、分区键

分区键就是决定表中的数据行，属于哪一个分区的一组数据列。在执行DML操作时，数据库会根据分区键选择分区;

五、常用分区方法及使用方法

A.范围分区(range partition)

范围分区将数据基于范围映射到每一个分区，这个范围是你在创建分区时指定的分区键决定的。这种分区方式是最为常用的，并且分区键经常采用日期;

范围分区特点:

1.范围分区主要依据分区键定义时给出的键值范围，根据实际的取值，进行分区的选择，进而在相应分区中存储数据;

2.范围分区比较合适存在以数字为导向，方便进行数字范围划分的数据列。如：员工表的雇佣日期列、工资列等;

3.范围分区的数据分布可能不均匀;

范围分区定义规则

1、在定义范围分区时，每个分区定义必须使用 values less than（value）子句。其中（value）表示该分区的上限值;

2、在定义范围分区时，最后一个分区可以是values less than（maxvalue）。其中（maxvalue）表示该分区存储高于其他分区上限值的数据行;

e.g

CREATE TABLE CUSTOMER 
( 
    CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY, 
    FIRST_NAME  VARCHAR2(30) NOT NULL, 
    LAST_NAME   VARCHAR2(30) NOT NULL, 
    PHONE        VARCHAR2(15) NOT NULL, 
    EMAIL        VARCHAR2(80), 
    STATUS       CHAR(1) 
) 
PARTITION BY RANGE (CUSTOMER_ID) 
( 
    PARTITION CUS_PART1 VALUES LESS THAN (100000) TABLESPACE CUS_TS01, 
    PARTITION CUS_PART2 VALUES LESS THAN (200000) TABLESPACE CUS_TS02 
)
例二：按时间划分
CREATE TABLE ORDER_ACTIVITIES 
( 
    ORDER_ID      NUMBER(7) NOT NULL, 
    ORDER_DATE    DATE, 
    TOTAL_AMOUNT NUMBER, 
    CUSTOTMER_ID NUMBER(7), 
    PAID           CHAR(1) 
) 
PARTITION BY RANGE (ORDER_DATE) 
(
  PARTITION ORD_ACT_PART01 VALUES LESS THAN (TO_DATE('01- MAY -2003','DD-MON-YYYY')) TABLESPACEORD_TS01,
  PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE('01-JUN-2003','DD-MON-YYYY')) TABLESPACE ORD_TS02,
  PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE('01-JUL-2003','DD-MON-YYYY')) TABLESPACE ORD_TS03
)
例三：MAXVALUE
CREATE TABLE RangeTable
( 
  idd   INT PRIMARY KEY , 
  iNAME VARCHAR(10), 
  grade INT  
) 
PARTITION  BY  RANGE (grade) 
( 
      PARTITION  part1 VALUES  LESS  THEN (1000) TABLESPACE  Part1_tb, 
      PARTITION  part2 VALUES  LESS  THEN (MAXVALUE) TABLESPACE  Part2_tb 
);

B、列表分区（list partition）

列表分区特点：

1.列表分区主要依据分区键定义时给出的取值列表，根据实际的取值，进行分区的选择，进而在相应分区中存储数据；

2.列表分区比较合适列唯一取值有限，且较为固定的数据列。如：员工表的部门列；

3.列表分区的数据分布可能不均匀。

列表分区定义规则：

1、在定义范围分区时，每个分区定义必须使用 values（'value01','value02'....）子句。表示该分区存储包含相关value值的数据行;

2、在定义范围分区时，最后一个分区可以是values（DEFAULT）。表示该分区存储未在其他分区定义的数据行;

e.g

CREATE TABLE PROBLEM_TICKETS 
( 
    PROBLEM_ID   NUMBER(7) NOT NULL PRIMARY KEY, 
    DESCRIPTION  VARCHAR2(2000), 
    CUSTOMER_ID  NUMBER(7) NOT NULL, 
    DATE_ENTERED DATE NOT NULL, 
    STATUS       VARCHAR2(20) 
) 
PARTITION BY LIST (STATUS) 
( 
      PARTITION PROB_ACTIVE   VALUES ('ACTIVE') TABLESPACE PROB_TS01, 
      PARTITION PROB_INACTIVE VALUES ('INACTIVE') TABLESPACE PROB_TS02
例二
CREATE  TABLE  ListTable
( 
    id    INT  PRIMARY  KEY , 
    name  VARCHAR (20), 
    area  VARCHAR (10) 
) 
PARTITION  BY  LIST (area) 
( 
    PARTITION  part1 VALUES ('guangdong','beijing') TABLESPACE  Part1_tb, 
    PARTITION  part2 VALUES ('shanghai','nanjing')  TABLESPACE  Part2_tb 
);
)

C.HASH分区（hash partition）

HASH分区特点：

1.HASH分区主要通过hash算法确定相应数据行应该被存放到哪个分区中;

2.HASH分区比较适合列差异值很多的数据列;

HASH分区的注意事项：

1.对于HASH分区，无法控制一条数据在分区间的具体分布。具体分布由hash算法决定;

2.对于HASH分区，如果更改分区的数量，将导致所有数据在分区间的重新分布;

HASH分区定义规则：

在定义HASH分区时，其分区数量应为2的N次方，如：2，4，8，16等;

e.g

CREATE TABLE HASH_TABLE 
( 
  COL NUMBER(8), 
  INF VARCHAR2(100) 
) 
PARTITION BY HASH (COL) 
( 
  PARTITION PART01 TABLESPACE HASH_TS01, 
  PARTITION PART02 TABLESPACE HASH_TS02, 
  PARTITION PART03 TABLESPACE HASH_TS03 
)
简写：
CREATE TABLE emp
(
    empno NUMBER (4),
    ename VARCHAR2 (30),
    sal   NUMBER 
)
PARTITION BY  HASH (empno) PARTITIONS 8
STORE IN (emp1,emp2,emp3,emp4,emp5,emp6,emp7,emp8);

D.组合分区（composite partition）

组合分区的特点：

1.组合分区中，主要通过在不同列上，使用“范围分区”、“列表分区”以及“HASH分区”不同组合方式，进而实现组合分区;

2.组合分区中，分区本身没有相应的segment，可以认为是一个逻辑容器，只有子分区拥有实际segment，用于存放数据；

组合分区的注意事项：

1.在oracle 11g以前，组合分区主要有两种组合方式：“RANGE-HASH”以及“RANGE-LIST”;

在oracle 11g以后，组合分区新增了四种组合方式：“RANGE-RANGE”、“LIST-RANGE”、“LIST-HASH”以及“LIST-LIST”;

e.g1

CREATE TABLE SALES 
(
PRODUCT_ID VARCHAR2(5),
SALES_DATE DATE,
SALES_COST NUMBER(10),
STATUS VARCHAR2(20)
)
PARTITION BY RANGE(SALES_DATE) SUBPARTITION BY LIST (STATUS)
(
   PARTITION P1 VALUES LESS THAN(TO_DATE('2003-01-01','YYYY-MM-DD'))TABLESPACE rptfact2009 
          ( 
              SUBPARTITION P1SUB1 VALUES ('ACTIVE') TABLESPACE rptfact2009, 
              SUBPARTITION P1SUB2 VALUES ('INACTIVE') TABLESPACE rptfact2009 
          ), 
   PARTITION P2 VALUES LESS THAN (TO_DATE('2003-03-01','YYYY-MM-DD')) TABLESPACE rptfact2009 ( 
              SUBPARTITION P2SUB1 VALUES ('ACTIVE') TABLESPACE rptfact2009, 
              SUBPARTITION P2SUB2 VALUES ('INACTIVE') TABLESPACE rptfact2009 
   ) 
)

e.g 2

create table dinya_test 
 ( 
 transaction_id number primary key, 
 item_id number(8) not null, 
 item_description varchar2(300), 
 transaction_date date 
 ) 
 partition by range(transaction_date)subpartition by hash(transaction_id)  subpartitions 3 store in (dinya_space01,dinya_space02,dinya_space03) 
 ( 
     partition part_01 values less than(to_date(‘2006-01-01’,’yyyy-mm-dd’)), 
     partition part_02 values less than(to_date(‘2010-01-01’,’yyyy-mm-dd’)), 
     partition part_03 values less than(maxvalue) 
 );

注意:对于同一张组合分区表而言，子分区的名字不能相同，即使不在相同的分区里;

五、有关表分区的一些维护性操作

1、添加分区

以下代码给SALES表添加了一个P3分区

   ALTER TABLE SALES ADD PARTITION P3 VALUES LESS THAN(TO_DATE('2003-06-01','YYYY-MM-DD'));

注意：以上添加的分区界限应该高于最后一个分区界限。

以下代码给SALES表的P3分区添加了一个P3SUB1子分区

   ALTER TABLE SALES MODIFY PARTITION P3 ADD SUBPARTITION P3SUB1 VALUES('COMPLETE');

2、删除分区

以下代码删除了P3表分区：

   ALTER TABLE SALES DROP PARTITION P3;

在以下代码删除了P4SUB1子分区：

   ALTER TABLE SALES DROP SUBPARTITION P4SUB1;

注意：如果删除的分区是表中唯一的分区，那么此分区将不能被删除，要想删除此分区，必须删除表。

3、截断分区

截断某个分区是指删除某个分区中的数据，并不会删除分区，也不会删除其它分区中的数据。当表中即使只有一个分区时，也可以截断该分区。通过以下代码截断分区：

   ALTER TABLE SALES TRUNCATE PARTITION P2;

通过以下代码截断子分区：

   ALTER TABLE SALES TRUNCATE SUBPARTITION P2SUB2;

4、合并分区

合并分区是将相邻的分区合并成一个分区，结果分区将采用较高分区的界限，值得注意的是，不能将分区合并到界限较低的分区。以下代码实现了P1 P2分区的合并：

   ALTER TABLE SALES MERGE PARTITIONS P1,P2 INTO PARTITION P2;

5、拆分分区

拆分分区将一个分区拆分两个新分区，拆分后原来分区不再存在。注意不能对HASH类型的分区进行拆分。

   ALTER TABLE SALES SBLIT PARTITION P2 AT(TO_DATE('2003-02-01','YYYY-MM-DD')) INTO (PARTITION P21,PARTITION P22);

6、接合分区(coalesca)

结合分区是将散列分区中的数据接合到其它分区中，当散列分区中的数据比较大时，可以增加散列分区，然后进行接合，值得注意的是，接合分区只能用于散列分区中。通过以下代码进行接合分区：

   ALTER TABLE SALES COALESCA PARTITION;

7、重命名表分区

以下代码将P21更改为P2

   ALTER TABLE SALES RENAME PARTITION P21 TO P2;

8、相关查询

跨分区查询

select sum( *) from
(select count(*) cn from t_table_SS PARTITION (P200709_1)
   union all
select count(*) cn from t_table_SS PARTITION (P200709_2)
);

查询表上有多少分区

SELECT * FROM useR_TAB_PARTITIONS WHERE TABLE_NAME='tableName'

查询索引信息

select object_name,object_type,tablespace_name,sum(value)
from v$segment_statistics
where statistic_name IN ('physical reads','physical write','logical reads')and object_type='INDEX'
group by object_name,object_type,tablespace_name
order by 4 desc

--显示数据库所有分区表的信息：

select * from DBA_PART_TABLES

--显示当前用户可访问的所有分区表信息:

select * from ALL_PART_TABLES

--显示当前用户所有分区表的信息：

select * from USER_PART_TABLES

--显示表分区信息显示数据库所有分区表的详细分区信息：

select * from DBA_TAB_PARTITIONS

--显示当前用户可访问的所有分区表的详细分区信息：

select * from ALL_TAB_PARTITIONS

--显示当前用户所有分区表的详细分区信息：

select * from USER_TAB_PARTITIONS

--显示子分区信息显示数据库所有组合分区表的子分区信息：

select * from DBA_TAB_SUBPARTITIONS

--显示当前用户可访问的所有组合分区表的子分区信息：

select * from ALL_TAB_SUBPARTITIONS

--显示当前用户所有组合分区表的子分区信息：

select * from USER_TAB_SUBPARTITIONS

--显示分区列显示数据库所有分区表的分区列信息：

select * from DBA_PART_KEY_COLUMNS

--显示当前用户可访问的所有分区表的分区列信息：

select * from ALL_PART_KEY_COLUMNS

--显示当前用户所有分区表的分区列信息：

select * from USER_PART_KEY_COLUMNS

--显示子分区列显示数据库所有分区表的子分区列信息：

select * from DBA_SUBPART_KEY_COLUMNS

--显示当前用户可访问的所有分区表的子分区列信息：

select * from ALL_SUBPART_KEY_COLUMNS

--显示当前用户所有分区表的子分区列信息：

select * from USER_SUBPART_KEY_COLUMNS

--查询oracle数据库中所有的的分区表

select * from user_tables a where a.partitioned='YES'

--删除一个表的数据是

truncate table table_name;

--删除分区表一个分区的数据是

alter table table_name truncate partition p5;

转载于:https://my.oschina.net/VILLE/blog/1631906

weixin_33998125

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Oracle数据库表分区

为什么80%的码农都做不了架构师？>>> ...
复制链接

扫一扫