Partition Table in oracle, oracle Partition Table

Partition Table in oracle, oracle Partition Table

Partition tables in oracle are frequently used in the system and frequently appear in some systems with large data volumes.

When talking about partition tables, we must first understand what partitions are. In fact, the so-called partition is simply a sub-region, the partition table is to store a table in a sub-region.

So what does the region mean?

That is where the table is stored in the legend-> table space.

Table space: A table space is a collection of one or more data files. All data objects are stored in the specified table space.

So why should we store a good table separately.

When the amount of data in a table increases, the speed of data query slows down, and the performance of applications degrades. In this case, you should consider partitioning the table.

After the table is partitioned, the logical table is still a complete table, but the data in the table is physically stored in multiple "tablespaces" (physical files). In this way, when querying data, instead of scanning the entire table every time, you can find the desired data from the current partition, which greatly improves the data query speed.

As mentioned above, partition tables are frequently used in systems with large data volumes.

So when should we create a partition table?

1. The table size exceeds 2 GB.
2. The table contains historical data. New data is added to the new partition.

Using table partitions to solve big data tables has the following advantages:

1. Improved query performance: You can query partition objects by searching only the partitions you are concerned about, improving the search speed.
2. Enhanced availability: If a partition of the table fails, the data of the table in other partitions is still available;
3. Easy Maintenance: If a partition of the table fails, you only need to fix the partition to fix the data;
4. Balanced I/O: You can map different partitions to different disks to balance I/O and improve the overall system performance.

To convert a common table to a partitioned table, you must use the online redefinition table provided by oracle.

Can refer to the article: http://www.cnblogs.com/hfliyi/p/3626302.html

A partition table is called a partition field. Then, how can we determine the partition field based on the partition field of a partition table.

Here we only talk about two common cases.

1. partition by range (for example, the Transaction completion time in the transaction history table and the event occurrence time in the log table)

2. partition by list (for example, the status value in a business table)

To describe more, selecting a partition field is the same as selecting an index field, which requires a high degree of recognition.

In the extreme analysis of the number order, the minimum degree of recognition for a field is that this field has only one value. Then, if you index or partition the field, it must be a dog hemodialysis.

In addition, partition fields should ensure that the distribution of table data is basically even. If there are million data records and one Status column, only one State is inactive, and the others are active, although this field can be partitioned by list, it cannot achieve our goal of creating a partition table.