Oracle partitioned tables and partitioned indexes overview

㈠ Partition Table Technology Overview

⑴range Partition

① Example

CREATE TABLE T
（... Column Definition ... ）
Partition by range (Week_num)
(partition P1 values less than (4) tablespace data0,
Partition P2 values less than (5) tablespace data1,
....
);

② Applicable Scenarios

Range partitions are generally more suitable for storing data over time period

③ Advantages

User knows which partition the specific data falls into
Therefore, the partitioning can effectively implement a variety of large-scale data management operations
For example, delete historical data management for a specified time period, restore a backup of a specified partition, or import an export

④ Disadvantages

Partition data may not be uniform
Range partitions are related to record values, are difficult to implement, and relatively poorly maintainable



⑵list Partition

① Example

CREATE TABLE T
（... Column definition ...)
Partition by List
(Partition P1 values (' Beijing ') tablespace data0,
Partition P2 values (' Shanghai ') tablespace data1,
....
);

The difference between Range and List is that the former is continuous, and the latter is discrete
Therefore, in terms of advantages and disadvantages and applicable scenarios, probably booms
I'm not repeating it here.


⑶hash Partition

① Example

CREATE TABLE T
(... Definition of column:)
Partition by hash (CUSTOMER_NO)
Partition 8 store in
(DATA0,DATA1);

Friendly Reminder OH: Oracle Recommended hash partition number is generally a power of 2

② Applicable Scenarios

hash partition suitable for static data
What is static data?
This type of data is always stored in the database and does not require historical data migration
Example: User data sheet, account information, etc.
Most of this information is accessed through a user ID or Account
If the hash partition by these fields, and establish local prefix partition index, access efficiency is very high OH

③ Advantages

Uniform Data Distribution
Implementation is very simple

④ Disadvantages

The user does not know which partition a record will fall on
Therefore, hash partitioning is not suitable for large-scale data management operations
For example, historical data cleansing, large-scale data import and export, etc.


⑷ Combined Partitioning

Oracle Composite Partitioning is a way of focusing on the benefits.
For example, in most cases, the first dimension is partitioned by Time field,
This is appropriate for large-scale data management operations at the partition level
A second-dimensional hash or list can further improve access performance or reduce implementation difficulty

11g ago Only: Range-hash or Range-list



㈡ partition Indexing Technology Overview

In a production environment, sometimes we encounter:
have made the partition table, how performance has not improved AH? Even more slowly?
One of the reasons for this is that there is no reasonable or even design of a partitioned index

Take a general look at the relationship between the table and the index on the partition:

⑴ Local prefix partition index

Suppose the partition table is a trade flow table T, and a range partition is performed by date of transaction
If you want to create an index on a date field, we can:
CREATE index idx_t on t (date) local;
Idx_t is called the local prefix index.
The so-called local, Index partitioning method is the same as the partitioning method of the corresponding table
The so-called prefix, refers to the partition field is the prefix of the index field

Advantages
Improve query performance
When a partition is drop or merge, Oracle automatically makes the same operation for the corresponding index partition,
The entire local prefix partition index is still valid without rebuild
This greatly guarantees the availability of the table



⑵ Local non-prefixed partition index

Suppose we need to create a partitioned index on the area field of the T table, we can:
CREATE index Idx_t_area on T (area) local;
Idx_t_area is called a local non-prefixed partition index

Non-prefixed to scan all partitions by index, performance may be lower
However, it guarantees the availability of access by index

Applicable scenarios:
Indexes that are not available for long periods of time when historical data is organized very frequently and cannot withstand global partition index rebuilds
At the same time, the daily transaction performance is acceptable, it is recommended to design a local non-prefix partition index



⑶ Global Partition Index

Suppose we need to create a partitioned index on the area field of the T table, we can:
CREATE index Idx_t_g_area on T (area)
Global partition by Range
(partition P1 less than ...);
The so-called global, refers to the partition of the index is independent of the partition of the table
After 10g, Oracle provides 2 global partition indexes:
Global Range Partition Index
Global HASH Partition Index

Disadvantages:
Mainly reflected in the high availability of data
After the drop partition, the global partition index is all invalid, unless rebulid
But the larger the amount of data, the longer it takes to rebuild the index

This article transferred from: http://blog.csdn.net/dba_waterbin/article/details/8836104

Oracle partitioned tables and partitioned indexes overview