Complete mastery of Oracle partitioning

This article brings you relevant knowledge about Oracle, which mainly introduces issues related to partitioning. Through the partitioning function, tables, indexes and index-organized tables can be further subdivided into segments. , so that you can manage and access these database objects more accurately. I hope it will be helpful to everyone.

Recommended tutorial: "Oracle Tutorial"

1. Introduction:

Oracle partitioning function can be improved a lot Application manageability, performance and availability. Partitioning allows tables, indexes, and index-organized tables to be further subdivided into segments, allowing more precise management and access of these database objects. Oracle offers a wide variety of partitioning schemes to suit every business requirement. And, because partitioning is completely transparent within SQL statements, this feature can be applied to almost any application.

Benefits of Partitioning: Partitioning brings great benefits to a variety of applications by improving manageability, performance, and availability. In general, partitioning can greatly improve the performance of certain queries and maintenance operations. In addition, partitioning can greatly simplify common management tasks. Through partitioning, database designers and administrators can solve some of the challenges posed by cutting-edge applications. Partitioning is a key tool in building gigabyte data systems or ultra-high availability systems.

2. Basic knowledge of partition function:

The partition function can further subdivide tables, indexes or index-organized tables into segments. Segments of these database objects are called partitions. Each partition has its own name and can select its own storage characteristics. From a database administrator's perspective, a partitioned object has multiple segments, which can be managed collectively or individually. This gives the database administrator considerable flexibility in managing partitioned objects. However, from an application perspective, a partitioned table is identical to a non-partitioned table, and no modification is required when accessing a partitioned table using SQL DML commands. Table partitioning is achieved through "partition keys", which refer to columns that determine the partition in which a row is located. Oracle Database 10g provides several technologies for partitioning tables:

1. Range partitioning (range): Each partition is specified by a partition key value range (for a date column as the partition key Table, "January 2005" partition contains rows with partition key values ​​from "January 1, 2005" to "January 31, 2005").

When using range partitioning, please consider the following rules:

1) Each partition must have a VALUES LESS THEN clause, which specifies a value that is not included in the partition upper limit value. Any records with a partition key value equal to or greater than this upper limit will be added to the next higher partition.

2) All partitions, except the first one, will have an implicit lower limit value, which is the upper limit value of the previous partition of this partition.

3) In the highest partition, MAXVALUE is defined. MAXVALUE represents an uncertain value. This value is higher than the value of any partition key in other partitions, and can also be understood as higher than the value of VALUE LESS THEN specified in any partition, including null values.

Example 1:

Suppose there is a CUSTOMER table with 200,000 rows of data. We partition this table by CUSTOMER_ID, and each partition stores 100,000 rows. We save each partition to a separate tablespace so that the data files can span multiple physical disks. The following is the code to create tables and partitions, as follows:

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
)

Example 2: Divide by time

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
)

Example 3: 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
);

2. List partition (list): Each partition is specified by a list of partition key values ​​(for a table with a region column as the partition key, the "North America" ​​partition might contain the values ​​"Canada", "United States", and "Mexico"). The characteristic of this partition is that there are only a few values ​​in a certain column. Based on this characteristic, we can use list partitioning.

Example 1

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

Example 2

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
);
)

3. Hash partition (hash):

Use the hash algorithm Use the partition key to determine the partition where the specified row resides. This type of partitioning uses a hashing algorithm on column values ​​to determine into which partition a row should be placed. Hash partitioning is recommended when the column values ​​do not have suitable conditions. Hash partitioning is a type of partitioning that evenly distributes data by specifying partition numbers because hash partitioning is performed on the I/O device to make the partitions the same size.

Example 1:

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
)

Abbreviation:

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);

The main mechanism of hash partition is to calculate which partition a specific record should be inserted into according to the hash algorithm, hash algorithm The most important thing is the hash function. If you want to use hash partitioning in Oracle, you only need to specify the number of partitions. It is recommended that the number of partitions be 2 to the nth power, which can make the data distribution among each partition more even.

4. Combined range hash partition (range-hash):

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

这种分区是基于范围分区和列表分区，表首先按某列进行范围分区，然后再按某列进行列表分区，分区之中的分区被称为子分区。

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
)
)

5、组合范围列表分区(range-list)：

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

这种分区是基于范围分区和散列分区，表首先按某列进行范围分区，然后再按某列进行散列分区。

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)
);

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

1、本地索引：

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

2、全局分区索引：

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

3、全局非分区索引：

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

四、Oracle 提供了一系列丰富的技术，

可用于对表、索引和索引组织表进行分区，

因此可以针对任何业务环境中的任何应用程序进行最佳的分区 Oracle 还提供一套完整的 SQL 命令，用于管理分区表。其中包括添加新分区、删除分区、分拆分区以及合并分区的命令。

分区的优点：

1、用分区功能提高可管理性

通过 Oracle 分区功能，可将表和索引分成更多、更小的可管理单元，从而使数据库管理员能以“化整为零，个个击破”的方式管理数据。使用分区功能，维护操作可集中于表的特定部分。例如，数据库管理员可以只对表的一部分做备份，而不必对整个表做备份。对整个数据库对象的维护操作，可以在每个分区的基础上进行，从而将维护工作分解成更容易管理的小块。利用分区功能提高可管理性的一个典型用法是支持数据仓库中的‘滚动视窗’加载进程。假设数据库管理员每周向表中加载新数据。可以对该表进行范 围分区，使每个分区包含一周的数据。这样加载进程只是简单地添加新的分区。添加一个分区的操作比修改整个表效率高很多，因为 DBA 不需要修改任何其他分区。

2、用分区功能提高性能

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

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

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

3、用分区功能提高可用性

分区的数据库对象具有分区独立性。该分区独立性特点可能是高可用性战略的一个重要部分，例如，如果分区表的一个分区不能用，但该表的所有其他 分区仍然保持在线并可用。那么这个应用可以继续针对该分区表执行查询和事务处理，只要不是访问不可用的分区，数据库操作仍然能够成功运行。数据库管理员可以指定各分区存放在不同的表空间里，从而让管理员隔离其它表分区针对单个分区进行备份与恢复操作。还有，分区功能可以减少计划停机时间。由于分区功能改善了性能，使数据库管理员能用相对较少的时间完成大型数据库对象的维护工作。

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

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;

在Oracle 11g中，组合分区功能这块有所增强，又增加了range-range,list-range,list-list,list-hash，并且 11g里面还支持Interval分区和虚拟列分

推荐教程：《Oracle学习教程》

The above is the detailed content of Complete mastery of Oracle partitioning. For more information, please follow other related articles on the PHP Chinese website!