Detailed Explanation of oracle Tablespace Table Partition and Usage of oracle Table Partition Query

In this paper, the concept and operation of partition table are sorted out from the following aspects:
1. The concept of tablespace and partitioned table
2. The specific role of table partitioning
3. Advantages and disadvantages of table partitioning
4. Several types of table partitions and their operation methods
5. Maintaining operations on table partitions.
(1.) The concept of table space and partition table
Tablespace:
Is a collection of one or more data files. All data objects are stored in a specified tablespace, but the main storage is tables, so it is called tablespace.

Partition table:
When the amount of data in the table increases, the speed of querying data will slow down and the performance of the application will decrease, so you should consider partitioning the table. After the table is partitioned, logically, the table is still a complete table, but the data in the table is physically stored in multiple tablespaces (physical files), so that the whole table will not be scanned every time when querying data.

(2). The specific role of table partitioning
The table partitioning feature of Oracle brings great benefits to all kinds of applications by improving manageability, performance, and availability. Generally, partitioning can greatly improve the performance of some queries and maintenance operations. In addition, partitioning can greatly simplify common management tasks. Partitioning is a key tool for building gigabyte data systems or ultra-high availability systems.

Partitioning enables a table, index, or indexed organization table to be further subdivided into segments. These segments of database objects are called partitions. Each partition has its own name and can choose its own storage characteristics. From the database administrator's point of view, a partitioned object has multiple segments, which can be managed collectively or individually, which gives the database administrator considerable flexibility in managing partitioned objects. However, from an application perspective, the partitioned table is exactly the same as the non-partitioned table, and no modification is required when accessing the partitioned table using the SQL DML command.

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

(3). Advantages and disadvantages of table partitioning
Table partitioning has the following advantages:
1. Improve query performance: The query of partition object can only search the partition that you care about, and improve the retrieval speed.
2. Enhance 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 needs to repair the data, only the partition can be repaired;
4. Balancing I/O: Different partitions can be mapped to disk to balance I/O and improve the overall system performance.

Disadvantages:
Partitioned table correlation: There is no way to directly convert existing tables into partitioned tables. However, Oracle provides the ability to redefine tables online.

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

1. Range partitioning:

Range partitioning maps data to every 1 partition based on the range determined by the partition key you specified when creating the partition. This partitioning method is the most commonly used, and the partitioning key often adopts date. For example, you may partition sales data by month.
When using range partitioning, consider the following rules:
1. Every partition must have an VALUES LESS THEN clause that specifies an upper limit value that is not included in the partition. Any record with a partition key value equal to or greater than this upper limit will be added to the next one higher partition.
2. All partitions except the first one will have an implicit lower limit value, which is the upper limit value of the first partition of this partition.
3. In the highest partition, MAXVALUE is defined. MAXVALUE represents an indeterminate value. This value is higher than the value of any partition key in the other partitions, and can also be understood to be higher than the value of VALUE LESS THEN specified in any partition, including null values.

Example 1:
Suppose we have an CUSTOMER table with 200,000 rows of data. We partition this table through CUSTOMER_ID, each partition storing 100,000 rows. We save each partition into a separate tablespace, so that the data file can span multiple physical disks. The following is the code to create the table and partition, 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: Divided 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 column. Based on this characteristic, we can use list partition.

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 kind of partition uses hashing algorithm on column values to determine which partition to put rows in. Hash partitioning is recommended when there are no suitable conditions for the values of columns.
Hash partitions are one type of partition that uniformly distributes data by specifying partition numbers, because by hashing partitions on I/O devices, the size of these partitions is 1.

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 hash algorithm. The most important function in hash algorithm is hash function. If you want to use hash partition in Oracle, you only need to specify the number of partitions. It is suggested that the number of partitions should be n power of 2, which can make the data distribution between each partition more uniform.

4. Combine range hash partitions

This kind of partition is based on range partition and list partition. The table is partitioned by range first, and then by list partition. The partitions in the partition are called sub-partitions.

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. Composite range hash partition:

This partitioning is based on range partitioning and hash partitioning. Tables are first partitioned by range and then hashed by column.

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). Some maintenance actions about table partitioning:

1. Add partitions

The following code adds an P3 partition to the SALES table

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

Note: The partition boundary added above should be higher than the last partition boundary.
The following code adds an P3SUB1 sub-partition to the P3 partition of the SALES table

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

2. Delete partitions
The following code removes the P3 table partition:

ALTER TABLE SALES DROP PARTITION P3;
 

The P4SUB1 subpartition is removed in the following code:

ALTER TABLE SALES DROP SUBPARTITION P4SUB1;
 

Note: If the deleted partition is only 1 partition in the table, then this partition cannot be deleted. To delete this partition, you must delete the table.
3. Truncate partitions

Truncating a partition means deleting the data in a partition, not deleting the partition, nor deleting the data in other partitions. Even if there is only 1 partition in the table, you can truncate the partition. Truncate the partition with the following code:

ALTER TABLE SALES TRUNCATE PARTITION P2;
 

Truncate the sub-partition with the following code:

ALTER TABLE SALES TRUNCATE SUBPARTITION P2SUB2;
 

4. Merge partitions

Merging partitions is to merge adjacent partitions into one partition, and the resulting partition will adopt the boundaries of higher partitions. It is worth noting that partitions cannot be merged into partitions with lower boundaries. The following code implements the merging of P1 P2 partitions:

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

5. Split partitions

Split partition splits one partition into two new partitions, and the original partition no longer exists after splitting. Note that partitions of HASH type cannot be split.

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

6. Joint Partition (coalesca)
Combined partition is to join the data in hash partition into other partitions. When the data in hash partition is relatively large, hash partition can be added and then jointed. It is worth noting that jointed partition can only be used in hash partition. Join partition with the following code:

ALTER TABLE SALES COALESCA PARTITION;

7. Rename table partitions

The following code changes P21 to P2

ALTER TABLE SALES RENAME PARTITION P21 TO P2;
 

8. Related queries

Cross-partition query

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
)

8

How many partitions are there on the lookup table

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
)

9

Query index information

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

0