On the problem of Oracle partitioning

First, Introduction

Oracle partitioning is a technique for handling oversized tables, indexes, and so on. Partitioning is a "divide and conquer" technique that provides scalable performance for large amounts of data by dividing large tables and indexes into manageable chunks, thus avoiding the management of each table as a large, separate object. Partitioning reduces the time required for administrative operations by assigning operations to smaller storage units, improves performance through enhanced parallelism, and increases availability by shielding partitions of failed data.

Ii. Advantages and Disadvantages

Advantages:

Enhanced availability: If one of the table's partitions fails, the table's data in other partitions is still available;

Easy maintenance: If a partition of the table fails, the data needs to be repaired, only the partition can be repaired;

Balanced I/O: can map different partitions to disk to balance I/O, improve the overall system performance;

Improved query performance: Queries on partitioned objects can search only for areas of interest and improve retrieval speed.

Disadvantages:

Partition Table correlation: Existing tables do not have methods that can be directly converted into partitioned tables. However, Oracle provides the ability to redefine tables online.

Iii. Zoning Methods

1. Range Partition:

A range partition is a range of values in a datasheet that determines which partition the data is stored on, depending on the scope of the value. If according to the ordinal partition, according to the business record creation date carries on the partition and so on.

2, hash partition (hash partition):

A hash partition is a type of partition that distributes data evenly by specifying the partition number, because these partitions are identical in size by hashing partitions on the I/O device.

3. List partition (listing partition):

The list partitioning method is used when you need to explicitly control how rows are mapped to partitions. Unlike range and hash partitions, the list partition does not support multiple-column partitions. If you want to partition a table by column, the partitioning key can only be made up of a single column of the table, but all columns that can be partitioned with a range or hash partitioning method can be partitioned using a list partitioning method.

4, Range-hash partition (composite partition):

Sometimes when we need to partition the range, the data in each partition is scattered in several tablespaces, so we use a composite partition. A composite partition is a partitioning method that uses a range partition first and then uses a hash partition within each partition (note: Make sure the range partition is first)

5, Range-list partition (composite partition):

A combination of scope and list technology, first partition the table, and then partition each range partition again with the list technology. Unlike a combo range-hash partition, all of the content of each child partition represents a logical subset of the data, as described by the appropriate scope and list partitioning settings. (Note: Be sure to do range partitioning first)