Database Optimization-create and classify oracle table partitions

Database Optimization-create and classify oracle table partitions

When the amount of data in a table increases, the speed of data query slows down, and the performance of applications degrades. In this case, 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 tablespaces (physical files, you may not need to scan the entire table every time.

 

The Table Partitioning function of Oracle brings great benefits to various applications by improving manageability, performance, and availability. In general, partitions 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 a gigabit data system or a super high availability system.

 

The partition function further segments a table, index, or index organization table into segments. The segments of these database objects are called partitions. Each partition has its own name, and you can select its own storage features. From the perspective of the database administrator, the objects in a partition have multiple segments, which can be managed collectively or separately, this makes the database administrator quite flexible in managing the objects after the partition. From the perspective of the application, the partitioned table is exactly the same as the non-partitioned table. You do not need to modify the table after accessing the partitioned table using the SQL DML command.

 

When to use a partition table:
1. The table size exceeds 2 GB or the number of rows in the table may exceed 5 million.

2. Different data needs to be processed in batches.

 

Advantages and disadvantages of Table Partitioning

Advantages of Table Partitioning:

1. Improved performance: You can query partition objects by searching only the partitions you are concerned about, improving the usage 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, you only need to fix the partition to fix the data;

4. Balanced I/O: You can map different partitions to disks to balance I/O and improve the overall system performance.

 

Disadvantages of Table Partitioning:
1. If improperly used, performance will be reduced. For example, read operations without specified partitions;

2. In important data scenarios, if a partition error occurs, the Service (transaction) must be stopped, such as the payment system;

3. After partitions are used, backup data is much slower and much larger than before partitions are available;

4. If improperly used, the throughput will be reduced

 

Table partition types and operation methods

I. Range partitioning:
Range partitions map data to each partition based on the range. This range is determined by the partition key you specified when creating the partition. This partitioning method is the most commonly used, and the partition key usually uses the date. For example, you may partition the sales data by month.

When using range partitioning, consider the following rules:

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

2. All partitions except the first partition have an implicit lower limit. This value is the upper limit of the previous partition.

3. MAXVALUE is defined in the highest partition. MAXVALUE indicates an uncertain value. This VALUE is higher than the VALUE of any partition key in other partitions. It can also be understood as a VALUE higher than the value less then specified in any partition and a null VALUE.

Example 1:

Suppose there is a tel table with 20 million rows of data. We partition the table by id. Each partition stores 10 million rows. We save each partition to a separate tablespace, in this way, data files can span across multiple physical disks. The following code creates a table and a partition:

CREATE TABLE TEL(ID NUMBER NOT NULL PRIMARY KEY,PHONE VARCHAR2(15) NOT NULL)PARTITION BY RANGE (ID)(PARTITION TEL_PART1 VALUES LESS THAN (100000000) TABLESPACE TEL_TS01,PARTITION TEL_PART2 VALUES LESS THAN (200000000) TABLESPACETEL_TS02)

Ii. List partition:

This partition has only a few values in a column. Based on this feature, we can use list partitions. For example, in our tel table, this table is mainly used in Shanghai and Beijing,

create table TEL(ID INTEGER not null,PROV_REGION_CODE INTEGER,CITY_REGION_CODE INTEGER,SUB_CITY_REGION_CODE INTEGER,PHONE VARCHAR2(16))partition by list(PROV_REGION_CODE)(partition PART11 values (110000),partition PART31 values (310000),partition PARTDFT values (default));

This partition is created on the PROV_REGION_CODE field.

Partition PART11values (110000) indicates Beijing;

Partition PART31values (310000) indicates Shanghai;

Partition PARTDFTvalues (default) where data is stored in this default partition.

110000 and 310000 are provincial-level code for China's national planning.

 

 

3. Hash partitions:
This type of partition uses the hash algorithm on the column value to determine which partition the row is placed in. When the column value does not have an appropriate condition, hash partitions are recommended.

Hash partitioning is a type of partitioning that distributes data evenly by specifying the Partition Number, because the size of these partitions is consistent by performing hash partitioning on the I/O device.

Example 1:

CREATE TABLE HASH_TEL(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);

The primary mechanism of hash partitions is to calculate the partition to which a specific record should be inserted based on the hash algorithm. The most important part of the hash algorithm is the hash function. If you want to use hash partitions in Oracle, you only need to specify the number of partitions. We recommend that the number of partitions use the N power of 2 to make the data distribution in each shard more even.

Hash partition write efficiency is very good, but the Read efficiency is not optimistic, so pay attention to business scenarios.

 

4. Composite partitioning
A composite partition is a type of partition that is embedded into another type of partition. The following example is based on the range partition and list partition. the table first partitions by a certain column, then list partitions by a column. The partitions in a partition are called subpartitions.

CREATE TABLESALES(ID INTEGER,SALES_DATEDATE,STATUSVARCHAR2(20))PARTITION BYRANGE(SALES_DATE) SUBPARTITION BY LIST (STATUS)(PARTITION P1 VALUES LESSTHAN(TO_DATE('2012-01-01','YYYY-MM-DD'))(SUBPARTITION P1SUB1 VALUES('ACTIVE'),SUBPARTITION P1SUB2 VALUES('INACTIVE')),PARTITION P2 VALUES LESS THAN (TO_DATE('2013-01-01','YYYY-MM-DD'))(SUBPARTITION P2SUB1 VALUES('ACTIVE'),SUBPARTITION P2SUB2 VALUES('INACTIVE')))

Be cautious when using it. The more complex the object is, the more difficult it is to grasp it.

 

Some special scenarios:

1. use table partitions to record logs. Generally, logs retained for one year or more are deleted. If you want to delete data and release space, if you do not perform special processing, you generally need to stop the application; in this case, you can use table partitions to create a partition in one year, and then delete the expired partition;

Alter table sales truncate partition P2;

If you want to use partitions for an important project, you 'd better read some professional books. For example, "Oracle10g SQL and PL/SQL programming guide. Everything is okay.