Oracle Partitioning technology

Oracle partition option is a technology used to process super large tables. Partitioning is a "divide and conquer" technology. By dividing large tables and indexes into small pieces that can be managed, you can avoid managing each table as a large and separate object, it provides Scalable Performance for a large amount of data. By allocating operations to smaller storage units, partitions reduce the time required for management operations, and improve performance through enhanced parallel processing. By shielding the partitions of faulty data, availability is also increased.

Oracle partition tables are divided by field values in a range, hash partitions by field hash function values, and range partitions first, hash-based composite partitions. in Oracle9i, the listing partitioning method is enhanced.
The administrator can specify the storage attribute of each partition and the placement of the partition in the host file system. This increases the granularity of super large databases ). Partitions can be deleted, detached, loaded, backed up, and restored separately, reducing the time required for management operations.

You can also create independent index partitions for table partitions, which reduces the time required for index maintenance. In addition, it provides a wide range of local and global indexing technologies. Partition operations can also be executed in parallel.
Partitioning technology also improves data availability. When some data is unavailable due to faults or other reasons, the available data in other partitions will not affect the continued use.

Partitions are transparent to applications. You can operate partition tables using standard SQL statements. The Oracle optimizer analyzes data partitions when accessing data. During queries, partitions that do not contain any query data are ignored, which greatly improves system performance.

Partition principle

1. Table partition Guide

A. Table size
Partitioning a large table is beneficial to the performance of large table operations and big table data maintenance. When the table size exceeds 1.5 GB-2 GB, or the OLTP system has more than 10 million records, partition the table.

B. Data Access features
Most table-Based Query applications only access a small amount of data in the table. In this way, you can make full use of the features of partition exclusion irrelevant data queries.

C. Data Maintenance
For data maintenance of some tables, batch data is often deleted by time period, for example, historical data is deleted by month. Partitions must be considered for such tables to meet maintenance requirements. Because a large amount of data is deleted, the system overhead is very high and sometimes unacceptable.

D. read-only data
If most of the data in a table is read-only, you can partition the table to store the read-only data in the read-only tablespace, which is very helpful for database backup.

E. Parallel Data Operations (parallel DML)
Partitions should be considered for tables that frequently execute parallel operations (such as parallel insert and parallel update.

F. Table availability
When the availability requirements for some data in a table are high, you should consider partitioning the table.

2. Select the partition key)

When determining a partition field, there are two main factors to consider:

A. Enhanced table management and maintainability
With the partition key, data maintenance can be performed based on a certain partition, such as drop or truncate one or more partitions. The paratition key can be used to control the storage of read-only data in the corresponding partitions, and these partitions are stored in the read-only tablespace, which improves the performance of data backup. This type of partition key is usually related to time.
B. Improve table access performance
You can use the partition key to locate the queried data in one or a small number of partitions. This requires the most common query conditions. Note that while improving the query efficiency, we should also take into account the data maintenance and management factors to avoid conflicts between them as much as possible.

Copyright description:ArticleFrom http://www.javaeye.com/topic/174265