Oracle 파티셔닝에 대한 완벽한 숙달

이 기사에서는 파티셔닝과 관련된 문제를 주로 소개하는 Oracle에 대한 관련 지식을 제공합니다. 파티셔닝 기능을 통해 테이블, 인덱스 및 인덱스 구성 테이블을 세그먼트로 더 세분화하여 보다 정확하게 관리하고 액세스할 수 있습니다. 이 데이터베이스 개체가 모든 사람에게 도움이 되기를 바랍니다.

추천 튜토리얼: "Oracle Tutorial"

1. 소개:

Oracle 파티셔닝 기능은 많은 애플리케이션의 관리 효율성, 성능 및 가용성을 향상시킬 수 있습니다. 파티셔닝을 사용하면 테이블, 인덱스 및 인덱스 구성 테이블을 세그먼트로 더 세분화할 수 있으므로 이러한 데이터베이스 개체를 보다 정확하게 관리하고 액세스할 수 있습니다. 오라클은 모든 비즈니스 요구 사항에 맞는 다양한 파티셔닝 체계를 제공합니다. 그리고 분할은 SQL 문 내에서 완전히 투명하므로 이 기능은 거의 모든 애플리케이션에 적용될 수 있습니다.

파티셔닝 기능의 장점: 파티셔닝 기능은 관리 효율성, 성능 및 가용성을 향상시켜 다양한 애플리케이션에 큰 이점을 제공합니다. 일반적으로 분할은 특정 쿼리 및 유지 관리 작업의 성능을 크게 향상시킬 수 있습니다. 또한 파티셔닝을 통해 일반적인 관리 작업을 크게 단순화할 수 있습니다. 파티셔닝을 통해 데이터베이스 설계자와 관리자는 최첨단 애플리케이션이 제기하는 몇 가지 문제를 해결할 수 있습니다. 파티셔닝은 기가바이트 데이터 시스템이나 초고가용성 시스템을 구축하는 핵심 도구입니다.

2. 파티션 기능에 대한 기본 지식:

파티션 기능은 테이블, 인덱스 또는 인덱스 구성 테이블을 세그먼트로 더 세분화할 수 있습니다. 이러한 데이터베이스 개체의 세그먼트를 파티션이라고 합니다. 각 파티션에는 고유한 이름이 있으며 고유한 스토리지 특성을 선택할 수 있습니다. 데이터베이스 관리자의 관점에서 분할된 개체에는 집합적으로 또는 개별적으로 관리할 수 있는 여러 세그먼트가 있습니다. 이는 데이터베이스 관리자에게 분할된 개체를 관리하는 데 있어 상당한 유연성을 제공합니다. 그러나 애플리케이션 관점에서 볼 때 분할된 테이블은 분할되지 않은 테이블과 동일하므로 SQL DML 명령을 사용하여 분할된 테이블에 액세스할 때 수정이 필요하지 않습니다. 테이블 파티셔닝은 행이 위치한 파티션을 결정하는 열을 참조하는 "파티션 키"를 통해 수행됩니다. Oracle Database 10g는 테이블 파티셔닝을 위한 여러 가지 기술을 제공합니다.

1. 범위 파티셔닝(범위): 각 파티션은 파티션 키 값 범위로 지정됩니다(파티션 키로 날짜 열이 있는 테이블의 경우 "2005년 1월"). 파티션에는 "2005년 1월 1일"부터 "2005년 1월 31일"까지의 파티션 키 값이 있는 행이 포함되어 있습니다.

범위 파티셔닝을 사용할 때는 다음 규칙을 고려하세요.

1) 각 파티션에는 파티션에 포함되지 않는 상한값을 지정하는 VALUES LESS THEN 절이 있어야 합니다. 파티션 키 값이 이 상한보다 크거나 같은 레코드는 다음으로 높은 파티션에 추가됩니다.

2) 첫 번째 파티션을 제외한 모든 파티션에는 이 파티션의 이전 파티션의 상한값인 암시적 하한값이 있습니다.

3) 최상위 파티션에는 MAXVALUE가 정의됩니다. MAXVALUE는 불확실한 값을 나타냅니다. 이 값은 다른 파티션의 파티션 키 값보다 높으며, null 값을 포함하여 모든 파티션에 지정된 VALUE LESS THEN 값보다 높은 것으로 이해될 수도 있습니다.

예 1:

200,000개의 데이터 행이 있는 CUSTOMER 테이블이 있다고 가정합니다. 각 파티션은 100,000개의 행을 저장하므로 데이터 파일이 저장됩니다. 여러 물리적 디스크에 걸쳐 있을 수 있습니다. 다음은 테이블과 파티션을 생성하는 코드입니다.

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
)

예제 2: 시간으로 나누기

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
)

예제 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(파티션 키로 지역 열이 있는 테이블의 경우 "북미" 파티션에 "캐나다", "미국" 및 "멕시코" 값이 포함될 수 있습니다). 이 분할의 특징은 특정 열에 소수의 값만 있다는 것입니다. 이러한 특징을 바탕으로 목록 분할을 사용할 수 있습니다.

예제 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

예제 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):

파티션 키에 해시 알고리즘을 사용하여 지정된 행이 위치한 파티션을 결정합니다. 이러한 유형의 분할은 열 값에 대한 해싱 알고리즘을 사용하여 행을 어느 분할에 배치해야 하는지 결정합니다. 열 값이 적합한 조건을 갖지 않는 경우에는 해시 파티셔닝을 권장합니다. 해시 파티셔닝(Hash Partitioning)은 파티션의 크기를 동일하게 만들기 위해 I/O 장치에서 해시 파티셔닝을 수행하므로 파티션 번호를 지정하여 데이터를 균등하게 분배하는 파티셔닝 유형입니다.

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

약어:

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

해시 파티션의 주요 메커니즘은 해시 알고리즘에 따라 특정 레코드를 어느 파티션에 삽입해야 하는지 계산하는 것입니다. 해시 알고리즘에서 가장 중요한 것은 해시 함수입니다. Oracle에서는 해시 파티셔닝을 사용하려면 파티션 수만 지정하면 됩니다. 파티션 수는 2의 n승인 것이 좋습니다. 이렇게 하면 각 파티션 간의 데이터 분포가 더 균일해집니다.

4. 결합된 범위 해시 파티션(범위-해시):

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

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

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学习教程》

위 내용은 Oracle 파티셔닝에 대한 완벽한 숙달의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!