In-depth understanding of Oracle partitions (2): FAQs In the partitioning Solution

MinuteThe design and implementation of regional solutions is a philosophy that pursues comprehensive balance and is full of dialectical unity,
It is also an art that accumulates experience and technology.
However, vulnerabilities frequently occur in actual projects, resulting in massive data crashes.

(I) Objective misunderstandings

① Problem Analysis

In many partition design schemes, its guiding ideology often only takes some goals into account.
Especially when you are overly concerned about the performance requirements of the design
However, the partitions are ignored in terms of data lifecycle, data backup and recovery, and high availability.

② Correct strategy

Comprehensive Consideration and comprehensive balancing of various goals
We recommend that you specify the target priority.


(Ii) Table partition errors

① Problem Analysis

In actual projects, many partition schemes only adopt the Oracle range partition policy.
Less use of other Oracle Partitioning technologies

② Correct strategy

The correct policy for partition table design should be after fully understanding the features of various Oracle partition technologies,
Then, we design them based on specific application requirements.
For example:
For tables that require historical data migration, use range, list, interval, and other data such as phone bill data whenever possible.
Hash Partitioning technology can be used for tables that do not have historical data migration requirements, such as user data information.


(Iii) mistakes in application design and development are not fully taken into account

① Problem Analysis

During the design of the partition scheme, the application software design and development were not fully investigated,
In particular, the Partitioning technology is unavailable because the key SQL statements are not thoroughly analyzed.

② Correct strategy

Application requirement analysis is the basis of any design and development work.
Conduct research and analysis on application software design and development, especially the in-depth analysis of key SQL statements


(Iv) misunderstandings in Partition Table Space Design

① Problem Analysis

In the Oracle partition design, you can specify the storage properties of each partition, especially the tablespace design at the partition level.
However, in many projects, there is a large degree of randomness in this aspect, and there is a lack of careful consideration and design.
As a result, business-related data management cannot be performed at the tablespace level.
For example, in applications such as historical data cleaning and data publishing, table space migration cannot be used, and partition exchange and merge cannot be combined.
In addition, such partitions cannot be backed up and restored on a monthly basis at the tablespace level during backup and recovery.

② Correct strategy

The partition table space design of Oracle provides a good technical foundation for mass data processing.
Only by fully understanding the Oracle-related technologies at the tablespace level can we weigh in the partition scheme design.
In short, don't put all the eggs in a basket.


(V) misunderstandings in Mass Data Processing

① Problem Analysis

In many projects, designers and developers are still using the most primitive DML for mass data processing,
Not only is it inefficient, but it also produces a large number of Undo redo operations, which increases the burden on Backup Recovery and resource management.

② Correct strategy

Partition technology must be taken into account for mass data processing.
For example, the use of table space migration and Partition Table Technology (truncate, drop, add, exchange, merge, split, etc,
Technical solution for efficient loading of Massive Data Warehouses

(Vi) mistakes in index partitioning

① Problem Analysis

In many scheme designs, the index is rarely or even not partitioned.
Or simply design all partition indexes as local partition indexes.

② Correct strategy

Although partition indexes are difficult, they are very important. Remember to design them at will!
For example, a local index with high availability and maintainability can be designed for tables with historical data cleaning and archiving requirements.
Global prefix indexes with better performance can be designed for tables without historical data cleaning and archiving.