Concept and operation of Oracle table partition

Time:2022-4-26

Detailed explanation of Oracle table partition

1. Concept of tablespace and partition table

Tablespaces:

It is a collection of one or more data files. All data objects are stored in the specified table space, but the main storage is tables, so it is called table space.

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 decline. At this time, we 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 table spaces (physical files), so that the whole table will not be scanned every time when querying data.

2. Specific functions of table partition

Oracle’s table partitioning function 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 Gigabit data systems or ultra-high availability systems.

The partition function can further subdivide the table, index or index organization table into segments. The segments of these database objects are called partitions. Each partition has its own name and can also choose its own storage characteristics. From the perspective of database administrator, the object after a partition has multiple segments, which can be managed collectively or separately, which makes the database administrator have considerable flexibility in managing the object after partition. However, from the perspective of application, the partitioned table is exactly the same as the non partitioned table. When using SQL DML command to access the partitioned table, there is no need to modify it.

When to use table partition:

1) The size of the table exceeds 2GB.

2) The table contains historical data, and the new data is added to the new partition.

3. Advantages and disadvantages of table partition

advantage:

1) Improve query performance: the query of partition objects can only search the partition they care about and improve the retrieval speed.

2) Enhance availability: if one 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 data needs to be repaired, only the partition can be repaired;

4) Balanced I / O: different partitions can be mapped to disks to balance I / O and improve the performance of the whole system.

Disadvantages:

Partition tables are related. There is no way to directly convert existing tables into partition tables. However, Oracle provides the ability to redefine tables online.

4. Several types and operation methods of table partition

1. Scope zoning

Range partitioning maps data to each partition based on the range, which is determined by the partition key you specify when creating the partition. This partitioning method is the most commonly used, and the partitioning key often adopts date. For example: you might partition sales data by month.

When using range partitioning, consider the following rules:

1) Each partition must have a values less then clause that specifies an upper limit value that is not included in the partition. Any record whose value of the partition key is equal to or greater than the upper limit will be added to the next higher partition.

2) All partitions except the first one will have an implicit lower limit, which is the upper limit 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 less then specified in any partition, including null value.

Example 1:

Suppose there is a customer table with 200000 rows of data in it. We pass this table through customer_ ID is partitioned. Each partition stores 100000 rows. We save each partition to a separate table space, so that the data file can span multiple physical disks. The following is the code for creating 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: 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 feature of this partition is that there are only a few values in a column. Based on this feature, we can adopt 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. Partition hash:

This kind of partition uses the hash algorithm on the column value to determine which partition to put the row into. Hash partitioning is recommended when there are no appropriate conditions for the value of a column.

Hash partition is a partition type that evenly distributes data by specifying partition number, because these partitions are consistent in size by hash partition on I / O devices.

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

4. Combination range hash partition

This partition is based on range partition and list partition. The table is first partitioned by a column, and then partitioned by a column. The partition in the partition is called sub partition.


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 partition is based on range partition and hash partition. The table is first partitioned by a column, and then partitioned by a 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 operations related to table partition

1) Add partition

The following code adds a 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 limit added above should be higher than the last partition limit.

The following code adds a p3sub1 sub partition to the P3 partition of the sales table


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

2) Delete partition

The following code deletes the P3 table partition:


ALTER TABLE SALES DROP PARTITION P3;

The p4sub1 sub partition is deleted in the following code:


ALTER TABLE SALES DROP SUBPARTITION P4SUB1;

Note: if the deleted partition is the only partition in the table, this partition cannot be deleted. To delete this partition, you must delete the table.

3) Truncate partition

Truncating a partition refers to deleting the data in a partition, which will not delete the partition or the data in other partitions. When there is only one partition in the table, the partition can be truncated. 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. As a result, partitions will adopt the boundaries of higher partitions. It is worth noting that partitions cannot be merged into partitions with lower boundaries. The following code realizes the merging of P1 and P2 partitions:


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

5) Split partition

Split partition splits a partition into two new partitions. After splitting, the original partition no longer exists. 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)

The combination partition is to join the data in the hash partition to other partitions. When the data in the hash partition is relatively large, the hash partition can be added and then joined. It is worth noting that the combination partition can only be used in the hash partition. Joint zoning by the following code:


ALTER TABLE SALES COALESCA PARTITION;

7) Rename table partition

The following code changes p21 to P2


ALTER TABLE SALES RENAME PARTITION P21 TO P2;

8) Related query

Cross partition query


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)

);

How many partitions are there on the query table


SELECT * FROM useR_TAB_PARTITIONS WHERE TABLE_NAME='tableName' 

Query index information


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

–Display information of all partition tables in the database:


select * from DBA_PART_TABLES 

–Display all partition table information accessible to the current user:


select * from ALL_PART_TABLES 

–Display the information of all partition tables of the current user:


select * from USER_PART_TABLES 

–Display table partition information display detailed partition information of all partition tables in the database:


select * from DBA_TAB_PARTITIONS 

–Displays the detailed partition information of all partition tables accessible to the current user:


select * from ALL_TAB_PARTITIONS 

–Display detailed partition information of all partition tables of the current user:


select * from USER_TAB_PARTITIONS 

–Display sub partition information displays the sub partition information of all combined partition tables in the database:


select * from DBA_TAB_SUBPARTITIONS 

–Display the sub partition information of all combined partition tables accessible to the current user:


select * from ALL_TAB_SUBPARTITIONS

–Display the sub partition information of all combined partition tables of the current user:


select * from USER_TAB_SUBPARTITIONS

–Display partition column displays the partition column information of all partition tables in the database:


select * from DBA_PART_KEY_COLUMNS

–Display partition column information of all partition tables accessible to the current user:


select * from ALL_PART_KEY_COLUMNS 

–Display partition column information of all partition tables of the current user:


select * from USER_PART_KEY_COLUMNS 

–Display sub partition column displays the sub partition column information of all partition tables in the database:


select * from DBA_SUBPART_KEY_COLUMNS

–Displays the sub partition column information of all partition tables accessible to the current user:


select * from ALL_SUBPART_KEY_COLUMNS 

–Display the sub partition column information of all partition tables of the current user:


select * from USER_SUBPART_KEY_COLUMNS 

–How to query all partition tables in Oracle Database


select * from user_tables a where a.partitioned='YES' 

–Deleting a table’s data is


truncate table table_name; 

–Delete partition table. The data of a partition is


alter table table_name truncate partition p5; 

This is the end of this article about Oracle table partition. For more information about Oracle table partition, please search the previous articles of developeppaer or continue to browse the relevant articles below. I hope you will support developeppaer in the future!