Oracle分割槽表基礎運維-01分割槽表分類

chenoracle發表於2020-05-14

Oracle運維

Oracle 分割槽表基礎運維 -01 分割槽表分類

關於分割槽表的基本概念，可以檢視Oracle 官方文件，連結如下：

https://docs.oracle.com/en/database/oracle/oracle-database/19/vldbg/vldb-and-partitioning-guide.pdf

Oracle® Database VLDB and Partitioning Guide

分割槽表使用場景：

在某些情況下，需要對錶進行分割槽。

這裡有一些建議，當你應該考慮分割槽一個表的情況:

•大於 2 GB 的表。

應該始終將這些表視為分割槽的候選表。

•包含歷史資料的表，其中新資料被新增到最新的分割槽。

一個典型的例子是歷史表，其中只有當前月份的資料是可更新的，其他11 個月是隻讀的。

•表的內容必須分佈在不同型別的儲存裝置上。

When to Partition a Table

There are certain situations when you would want to partition a table.

Here are some suggestions for situations when you should consider partitioning a table:

• Tables that are greater than 2 GB.

These tables should always be considered as candidates for partitioning.

• Tables that contain historical data, in which new data is added into the newest partition.

A typical example is a historical table where only the current month's data is updatable and the other 11 months are read only.

• Tables whose contents must be distributed across different types of storage devices.

何時對索引進行分割槽

在某些情況下，需要對索引進行分割槽。

以下是一些何時考慮對索引進行分割槽的建議:

•避免在刪除資料時進行索引維護。

•對部分資料進行維護，而不使整個索引失效。

•減少索引對具有單調遞增值的列造成的索引傾斜的影響。

When to Partition an Index

There are certain situations when you would want to partition an index.

Here are some suggestions for when to consider partitioning an index:

• Avoid index maintenance when data is removed.

• Perform maintenance on parts of the data without invalidating the entire index.

• Reduce the effect of index skew caused by an index on a column with a monotonically increasing value.

分割槽策略

Partitioning Strategies

Oracle Partitioning offers three fundamental data distribution methods as basic partitioning strategies that control how data is placed into individual partitions.

Oracle 分割槽提供了三種基本的資料分佈方法作為基本分割槽策略，用於控制如何將資料放置到各個分割槽中。

These strategies are:

• Range

• Hash

• List

組合分割槽

Composite Partitioning

Composite partitioning is a combination of the basic data distribution methods.

With composite partitioning, a table is partitioned by one data distribution method and then each partition is further subdivided into subpartitions using a second data distribution method. All subpartitions for a given partition represent a logical subset of the data.

Composite partitioning supports historical operations, such as adding new range partitions, but also provides higher degrees of potential partition pruning and finer granularity of data placement through subpartitioning. Figure 2-3 offers a graphical view of range-hash and range-list composite partitioning, as an example.