Oracle 테이블스페이스 테이블 파티션에 대한 자세한 설명 및 Oracle 테이블 파티션 쿼리 사용 방법-PHP开发-php.cn

이 글에서는 파티션 테이블의 개념과 동작을 다음과 같은 측면에서 정리했습니다.
1. 테이블스페이스와 파티션 테이블의 개념
2. 테이블 파티션의 기능
3. 테이블 파티션의 장점과 단점
4. 여러 유형의 테이블 파티션 및 조작 방법
5. 테이블 파티션의 유지 관리 작업
(1.) 테이블스페이스 및 파티션 테이블의 개념
테이블스페이스:
하나 이상의 데이터 파일의 집합으로 모든 데이터 객체는 지정된 테이블스페이스에 저장되지만 주로 테이블을 저장하므로 테이블스페이스라고 부른다.

파티션 테이블:
테이블의 데이터 양이 계속 증가하면 데이터 쿼리 속도가 느려지고 애플리케이션 성능이 저하됩니다. 이때 파티션 분할을 고려해야 합니다. 테이블. 테이블이 분할된 후에도 논리적 테이블은 여전히 완전한 테이블이지만 테이블의 데이터는 물리적으로 여러 테이블스페이스(물리적 파일)에 저장되므로 데이터를 쿼리할 때 매번 전체 테이블을 스캔하지 않습니다. 표면.

(2) 테이블 파티셔닝의 구체적인 역할
오라클의 테이블 파티셔닝 기능은 관리 효율성, 성능 및 가용성을 향상시켜 다양한 애플리케이션에 큰 이점을 제공합니다. 일반적으로 분할은 특정 쿼리 및 유지 관리 작업의 성능을 크게 향상시킬 수 있습니다. 또한 파티셔닝은 일반적인 관리 작업을 크게 단순화할 수 있으며 기가바이트 데이터 시스템 또는 초고가용성 시스템을 구축하기 위한 핵심 도구입니다.

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

파티션 테이블을 사용해야 하는 경우:
1. 테이블 크기가 2GB를 초과합니다.
2. 테이블에는 기록 데이터가 포함되어 있으며 새 데이터가 새 파티션에 추가됩니다.

(3). 테이블 파티셔닝의 장점과 단점
테이블 파티셔닝에는 다음과 같은 장점이 있습니다.
1. 쿼리 성능 향상: 파티션 개체를 쿼리할 때 관심 있는 파티션만 검색할 수 있습니다. , 검색 속도가 향상됩니다.
2. 가용성 향상: 테이블의 특정 파티션에 장애가 발생해도 다른 파티션에 있는 테이블의 데이터를 계속 사용할 수 있습니다.
3. 테이블의 특정 파티션에 장애가 발생하여 데이터를 계속 유지해야 하는 경우
4. 균형 잡힌 I/O: 다양한 파티션을 디스크에 매핑하여 I/O 균형을 맞추고 전체 시스템 성능을 향상시킬 수 있습니다.

단점:
파티션 테이블 관련: 기존 테이블을 파티션 테이블로 직접 변환할 수 있는 방법이 없습니다. 그러나 Oracle은 테이블의 온라인 재정의 기능을 제공합니다.

(4). 테이블 파티션의 여러 유형 및 작동 방법

1. 범위 파티셔닝:

범위 파티셔닝은 범위를 기준으로 데이터를 각 파티션에 매핑합니다. 파티션을 생성할 때 지정한 파티션 키에 따라 결정됩니다. 이 분할 방법은 가장 일반적으로 사용되며 파티션 키는 날짜를 사용하는 경우가 많습니다. 예를 들어 판매 데이터를 월별로 분할할 수 있습니다.
범위 파티셔닝을 사용할 때는 다음 규칙을 고려하세요.
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, 
    PHONEVARCHAR2(15) NOT NULL, 
    EMAILVARCHAR2(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(&#39;01- MAY -2003&#39;,&#39;DD-MON-YYYY&#39;)) TABLESPACEORD_TS01,
  PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE(&#39;01-JUN-2003&#39;,&#39;DD-MON-YYYY&#39;)) TABLESPACE ORD_TS02,
  PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE(&#39;01-JUL-2003&#39;,&#39;DD-MON-YYYY&#39;)) 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 
);

로그인 후 복사

둘. 리스트 파티셔닝:

이 파티션의 특징은 특정 열에 소수의 값만 있다는 것입니다. 이 기능을 기반으로 리스트 파티셔닝을 사용할 수 있습니다.

예 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 (&#39;ACTIVE&#39;) TABLESPACE PROB_TS01, 
      PARTITION PROB_INACTIVE VALUES (&#39;INACTIVE&#39;) TABLESPACE PROB_TS02
)

로그인 후 복사

예 2

CREATE  TABLE  ListTable
( 
    id    INT  PRIMARY  KEY , 
    name  VARCHAR (20), 
    area  VARCHAR (10) 
) 
PARTITION  BY  LIST (area) 
( 
    PARTITION  part1 VALUES (&#39;guangdong&#39;,&#39;beijing&#39;) TABLESPACE  Part1_tb, 
    PARTITION  part2 VALUES (&#39;shanghai&#39;,&#39;nanjing&#39;)  TABLESPACE  Part2_tb 
);
)

로그인 후 복사

3. 해시 파티셔닝:

이 유형의 파티션은 열 값에 해싱을 사용합니다. 행을 어느 파티션에 배치해야 하는지 결정하기 위한 연산입니다. 열 값이 적합한 조건을 갖지 않는 경우에는 해시 파티셔닝을 권장합니다.
해시 파티셔닝은 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);

로그인 후 복사

hash分区最主要的机制是根据hash算法来计算具体某条纪录应该插入到哪个分区中,hash算法中最重要的是hash函数，Oracle中如果你要使用hash分区，只需指定分区的数量即可。建议分区的数量采用2的n次方，这样可以使得各个分区间数据分布更加均匀。

四.组合范围散列分区

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

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(&#39;2003-01-01&#39;,&#39;YYYY-MM-DD&#39;))TABLESPACE rptfact2009 
  ( 
      SUBPARTITION P1SUB1 VALUES (&#39;ACTIVE&#39;) TABLESPACE rptfact2009, 
      SUBPARTITION P1SUB2 VALUES (&#39;INACTIVE&#39;) TABLESPACE rptfact2009 
  ), 
   PARTITION P2 VALUES LESS THAN (TO_DATE(&#39;2003-03-01&#39;,&#39;YYYY-MM-DD&#39;)) TABLESPACE rptfact2009 
  ( 
      SUBPARTITION P2SUB1 VALUES (&#39;ACTIVE&#39;) TABLESPACE rptfact2009, 
      SUBPARTITION P2SUB2 VALUES (&#39;INACTIVE&#39;) TABLESPACE rptfact2009 
  ) 
)

로그인 후 복사

五.复合范围散列分区：

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

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&#39;,&#39;yyyy-mm-dd&#39;)), 
     partition part_02 values less than(to_date(‘2010-01-01&#39;,&#39;yyyy-mm-dd&#39;)), 
     partition part_03 values less than(maxvalue) 
 );

로그인 후 복사

(5).有关表分区的一些维护性操作：

一、添加分区

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

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

로그인 후 복사

注意：以上添加的分区界限应该高于最后一个分区界限。
以下代码给SALES表的P3分区添加了一个P3SUB1子分区

ALTER TABLE SALES MODIFY PARTITION P3 ADD SUBPARTITION P3SUB1 VALUES(&#39;COMPLETE&#39;);

로그인 후 복사

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

ALTER TABLE SALES DROP PARTITION P3;

로그인 후 복사

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

ALTER TABLE SALES DROP SUBPARTITION P4SUB1;

로그인 후 복사

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

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

ALTER TABLE SALES TRUNCATE PARTITION P2;

로그인 후 복사

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

ALTER TABLE SALES TRUNCATE SUBPARTITION P2SUB2;

로그인 후 복사

四、合并分区

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

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

로그인 후 복사

五、拆分分区

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

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

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

ALTER TABLE SALES COALESCA PARTITION;

로그인 후 복사

七、重命名表分区

以下代码将P21更改为P2

ALTER TABLE SALES RENAME PARTITION P21 TO P2;

로그인 후 복사

八、相关查询

跨分区查询

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=&#39;tableName&#39;

로그인 후 복사

查询索引信息

select object_name,object_type,tablespace_name,sum(value)
from v$segment_statistics
where statistic_name IN (&#39;physical reads&#39;,&#39;physical write&#39;,&#39;logical reads&#39;)and object_type=&#39;INDEX&#39;
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=&#39;YES&#39;

--删除一个表的数据是
truncate table table_name;

--删除分区表一个分区的数据是
alter table table_name truncate partition p5;

로그인 후 복사

更多oracle表空间表分区详解及oracle表分区查询使用方法相关文章请关注PHP中文网！