This article organizes the concepts and operations of partition tables from the following aspects:
1. Concepts of table space and partition tables
2. Specific functions of table partitions
3. Table partitions Advantages and Disadvantages
4. Several types of table partitions and operation methods
5. Maintenance operations on table partitions.
(1.) Concepts of table spaces and partition tables
Table spaces:
is a collection of one or more data files. All data objects are stored in the specified table space, but they mainly store tables, so they are called table spaces.

Partition table:
When the amount of data in the table continues to increase, the speed of querying data will slow down, and the performance of the application will decrease. At this time, you should consider partitioning the table. After the table is partitioned, the logical table is still a complete table, but the data in the table is physically stored in multiple table spaces (physical files), so that when querying the data, the entire table will not be scanned every time. surface.

(2). The specific role of table partitioning
Oracle's table partitioning function brings great benefits to various 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 and is a key tool for building gigabyte data systems or ultra-high availability systems.

The partitioning function can further subdivide a table, index, or index-organized table into segments. The segments of these database objects are called partitions. Each partition has its own name and can select its own storage characteristics. From the perspective of a database administrator, a partitioned object has multiple segments, and these segments can be managed collectively or individually. This gives the database administrator considerable flexibility when managing partitioned objects. sex. 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.

When to use partition tables:
1. The size of the table exceeds 2GB.
2. The table contains historical data, and new data is added to the new partition.

(3). Advantages and disadvantages of table partitioning
Table partitioning has the following advantages:
1. Improve query performance: When querying partition objects, you can only search for the partitions you care about, improving retrieval speed.
2. Enhanced availability: If a partition of the table fails, the data of the table in other partitions is still available;
3. Easy maintenance: If a partition of the table fails and the data needs to be repaired, only that Just partition;
4. Balanced I/O: Different partitions can be mapped to disks to balance I/O and improve the performance of the entire system.

Disadvantages:
Partition table related: There is no way to directly convert an existing table into a partition table. However, Oracle provides the function of online redefinition of tables.

(4). Several types and operation methods of table partitions

1. Range partitioning:

Range partitioning maps data to each partition based on the range. This range It is determined by the partition key you specified when creating the partition. This partitioning method is the most commonly used, and the partition key often uses a date. For example: you might partition your sales data by month.
When using range partitioning, please consider the following rules:
1. Each partition must have a VALUES LESS THEN clause, which specifies an upper limit value that is not included in the partition. 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. This value 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. Each partition stores 100,000 rows. We save each partition to a separate tablespace so that 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, 
    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 
)

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:

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:

This type of partition uses a hash algorithm on the column value to Determine into which partition the rows are 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 the size of these partitions is made consistent by hash partitioning on the I/O device.

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

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

五.復(fù)合范圍散列分區(qū)：

這種分區(qū)是基于范圍分區(qū)和散列分區(qū)，表首先按某列進(jìn)行范圍分區(qū)，然后再按某列進(jìn)行散列分區(qū)。

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

(5).有關(guān)表分區(qū)的一些維護(hù)性操作：

一、添加分區(qū)

以下代碼給SALES表添加了一個(gè)P3分區(qū)

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

注意：以上添加的分區(qū)界限應(yīng)該高于最后一個(gè)分區(qū)界限。
以下代碼給SALES表的P3分區(qū)添加了一個(gè)P3SUB1子分區(qū)

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

二、刪除分區(qū)
以下代碼刪除了P3表分區(qū)：

ALTER TABLE SALES DROP PARTITION P3;

在以下代碼刪除了P4SUB1子分區(qū)：

ALTER TABLE SALES DROP SUBPARTITION P4SUB1;

注意：如果刪除的分區(qū)是表中唯一的分區(qū)，那么此分區(qū)將不能被刪除，要想刪除此分區(qū)，必須刪除表。
三、截?cái)喾謪^(qū)

截?cái)嗄硞€(gè)分區(qū)是指刪除某個(gè)分區(qū)中的數(shù)據(jù)，并不會(huì)刪除分區(qū)，也不會(huì)刪除其它分區(qū)中的數(shù)據(jù)。當(dāng)表中即使只有一個(gè)分區(qū)時(shí)，也可以截?cái)嘣摲謪^(qū)。通過(guò)以下代碼截?cái)喾謪^(qū)：

ALTER TABLE SALES TRUNCATE PARTITION P2;

通過(guò)以下代碼截?cái)嘧臃謪^(qū)：

ALTER TABLE SALES TRUNCATE SUBPARTITION P2SUB2;

四、合并分區(qū)

合并分區(qū)是將相鄰的分區(qū)合并成一個(gè)分區(qū)，結(jié)果分區(qū)將采用較高分區(qū)的界限，值得注意的是，不能將分區(qū)合并到界限較低的分區(qū)。以下代碼實(shí)現(xiàn)了P1 P2分區(qū)的合并：

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

五、拆分分區(qū)

拆分分區(qū)將一個(gè)分區(qū)拆分兩個(gè)新分區(qū)，拆分后原來(lái)分區(qū)不再存在。注意不能對(duì)HASH類(lèi)型的分區(qū)進(jìn)行拆分。

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

六、接合分區(qū)(coalesca)
結(jié)合分區(qū)是將散列分區(qū)中的數(shù)據(jù)接合到其它分區(qū)中，當(dāng)散列分區(qū)中的數(shù)據(jù)比較大時(shí)，可以增加散列分區(qū)，然后進(jìn)行接合，值得注意的是，接合分區(qū)只能用于散列分區(qū)中。通過(guò)以下代碼進(jìn)行接合分區(qū)：

ALTER TABLE SALES COALESCA PARTITION;

七、重命名表分區(qū)

以下代碼將P21更改為P2

ALTER TABLE SALES RENAME PARTITION P21 TO P2;

八、相關(guān)查詢(xún)

跨分區(qū)查詢(xún)

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

查詢(xún)表上有多少分區(qū)

SELECT * FROM useR_TAB_PARTITIONS WHERE TABLE_NAME='tableName'

查詢(xún)索引信息

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

--顯示數(shù)據(jù)庫(kù)所有分區(qū)表的信息：
select * from DBA_PART_TABLES

--顯示當(dāng)前用戶(hù)可訪問(wèn)的所有分區(qū)表信息:
select * from ALL_PART_TABLES

--顯示當(dāng)前用戶(hù)所有分區(qū)表的信息：
select * from USER_PART_TABLES

--顯示表分區(qū)信息 顯示數(shù)據(jù)庫(kù)所有分區(qū)表的詳細(xì)分區(qū)信息：
select * from DBA_TAB_PARTITIONS

--顯示當(dāng)前用戶(hù)可訪問(wèn)的所有分區(qū)表的詳細(xì)分區(qū)信息：
select * from ALL_TAB_PARTITIONS

--顯示當(dāng)前用戶(hù)所有分區(qū)表的詳細(xì)分區(qū)信息：
select * from USER_TAB_PARTITIONS

--顯示子分區(qū)信息 顯示數(shù)據(jù)庫(kù)所有組合分區(qū)表的子分區(qū)信息：
select * from DBA_TAB_SUBPARTITIONS

--顯示當(dāng)前用戶(hù)可訪問(wèn)的所有組合分區(qū)表的子分區(qū)信息：
select * from ALL_TAB_SUBPARTITIONS

--顯示當(dāng)前用戶(hù)所有組合分區(qū)表的子分區(qū)信息：
select * from USER_TAB_SUBPARTITIONS

--顯示分區(qū)列 顯示數(shù)據(jù)庫(kù)所有分區(qū)表的分區(qū)列信息：
select * from DBA_PART_KEY_COLUMNS

--顯示當(dāng)前用戶(hù)可訪問(wèn)的所有分區(qū)表的分區(qū)列信息：
select * from ALL_PART_KEY_COLUMNS

--顯示當(dāng)前用戶(hù)所有分區(qū)表的分區(qū)列信息：
select * from USER_PART_KEY_COLUMNS

--顯示子分區(qū)列 顯示數(shù)據(jù)庫(kù)所有分區(qū)表的子分區(qū)列信息：
select * from DBA_SUBPART_KEY_COLUMNS

--顯示當(dāng)前用戶(hù)可訪問(wèn)的所有分區(qū)表的子分區(qū)列信息：
select * from ALL_SUBPART_KEY_COLUMNS

--顯示當(dāng)前用戶(hù)所有分區(qū)表的子分區(qū)列信息：
select * from USER_SUBPART_KEY_COLUMNS

--怎樣查詢(xún)出oracle數(shù)據(jù)庫(kù)中所有的的分區(qū)表
select * from user_tables a where a.partitioned='YES'

--刪除一個(gè)表的數(shù)據(jù)是
truncate table table_name;

--刪除分區(qū)表一個(gè)分區(qū)的數(shù)據(jù)是
alter table table_name truncate partition p5;

更多oracle表空間表分區(qū)詳解及oracle表分區(qū)查詢(xún)使用方法相關(guān)文章請(qǐng)關(guān)注PHP中文網(wǎng)！