How to design an Oracle Partition Table

The concept of Partitioned Tables and tablespaces is distinguished between Oracle partition tables:

Table space: A table space is a collection of one or more data files. All data objects are stored in the specified table space.

Partition Table: partitions are designed to solve key issues that support large tables and indexes. It uses a small and easy-to-manage partition-based piece (piece) method. Once a partition is defined, SQL statements can access a partition rather than the entire table, thus improving management efficiency. Partitioning is very effective for Data Warehouse applications because they often store and analyze massive amounts of historical data. 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, it does not scan the entire table every time.

Table partitions:

The partition table 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. However, from the perspective of the application, the partitioned table is exactly the same as the non-partitioned table. You do not need to modify it when using the SQL DML command to access the partitioned table.

When can I use a partitioned table?

1. The table size exceeds 2 GB.

2. The table contains historical data. New data is added to new partitions.

Advantages and disadvantages of Table Partitioning:

Advantages:

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

1. Partition Table: there is no way to directly convert an existing table into a partition table. However, Oracle provides the online table redefinition function.

2. Partitioning is committed to solving the key issues that support large tables and indexes. It uses a small and easy-to-manage partition-based piece (piece) method.

Partition Table Category

1. Range partitioning (Range partitioning)
2. Hash partitioning (Hash partition)
3. List partitioning (List partition)
4. Composite range-hash partitioning (range-hash combined partition)
5. Composite range-list partitioning (range-list combined partitions)

When to select range partitions
You must divide the table records according to the range of values in a column. You want to process some data, which is often within a certain range, such as month. If the data can be evenly distributed according to the partition range, the optimal performance will be achieved. If the data distribution is uneven, you may have to select another partitioning method.

CREATE TABLE sales ( invoice_no NUMBER, sale_year INT NOT NULL, sale_month INT NOT NULL, sale_day INT NOT NULL ) PARTITION BY RANGE (sale_year, sale_month, sale_day) ( PARTITION sales_q1 VALUES LESS THAN (1999, 04, 01) TABLESPACE tsa, PARTITION sales_q2 VALUES LESS THAN (1999, 07, 01) TABLESPACE tsb, PARTITION sales_q3 VALUES LESS THAN (1999, 10, 01) TABLESPACE tsc, PARTITION sales_q4 VALUES LESS THAN (2000, 01, 01) TABLESPACE tsd );

 

When to select HASH partitions
If data is not easy to use, but you want to improve performance and ease of table management. Hash partitions provide a method to evenly distribute data across a specified number of partitions. The row is mapped to the corresponding partition based on the hash value of the partition key. You can flexibly store data by creating and using hash partitions. You can use cross-Access to partition on different I/O devices to improve performance.

CREATE TABLE scubagear (id NUMBER, name VARCHAR2 (60)) PARTITION BY HASH (id) PARTITIONS 4 STORE IN (gear1, gear2, gear3, gear4);

 

When to select list partitions
Using LIST partitions, you can directly control the ing of some data to certain partitions. You can specify a non-consecutive partition key value for a partition. This is different from the RANGE partition (partition by key value RANGE), but also from the HASH partition (cannot control which partition a row is mapped ).

CREATE TABLE q1_sales_by_region (deptno number, deptname varchar2(20), quarterly_sales number(10, 2), state varchar2(2)) PARTITION BY LIST (state) (PARTITION q1_northwest VALUES ('OR', 'WA'), PARTITION q1_southwest VALUES ('AZ', 'UT', 'NM'), PARTITION q1_northeast VALUES ('NY', 'VM', 'NJ'), PARTITION q1_southeast VALUES ('FL', 'GA'), PARTITION q1_northcentral VALUES ('SD', 'WI'), PARTITION q1_southcentral VALUES ('OK', 'TX'));

Partition Table Design
How do I select the partition type and the partition columns? Before that, you must clarify your purpose-ease of management and performance. Which of the following aspects do you pay more attention? The impact of partition tables can be classified into the following types: performance, manageability, and data cleaning.
The following describes the specific impact of a partition table on each item,
Performance:
This is generally the main purpose of partitioning. A partition changes a large table into a small table. When the condition after where reflects the specific value of the partition field, full table scan is avoided.
Easy to manage:
For large tables that contain massive data, the ease of management of partitions is very obvious. When we recommend that you create an index based on a non-partitioned table, the only option is to create the entire index. If a table is partitioned, you can create an index for the table in parallel based on the partition. For example:

alter index par_ind_01 reuild partition yy05; 

In addition, you can also do many things at the same time, such as changing the tablespace where the table is located, exporting the table, and deleting the table data.

Delete data:
We often need to delete some historical data of the table. The general practice is delete, but this will cause the rapid growth of undo and redo information and affect the overall performance of the database. In this case, we can use drop a partition to complete this task. For example:

alter table tab_a drop partition yy01; 

When a table partition is deleted, the corresponding local index is also deleted. If a global index still exists, it will become unusable. To avoid this problem, you can use:

alter table tab_a drop partition yy01 update global indexes;