Partitioned tables in Oracle

Partitioned tables in Oracle are frequently used in systems that are more frequent in systems with large amounts of data.

When you refer to a partitioned table, you must first understand what a partition is. In fact, the so-called partition is simply a sub-region, the partition table is to put a table sub-region storage.

So what are the regions in the subregion referring to?

That's where the legend stores the table---> table space.

Tablespace: is a collection of one or more data files, all of which are stored in the specified table space, but are primarily tables, so called table spaces.

So why take a good watch and keep it separate.

When the amount of data in the table is increasing, the query data slows down and the performance of the application degrades, so you should consider partitioning the table.

Table is partitioned, the logical table is still a complete table, just the data in the table is physically stored in multiple "table space" (physical file), so that the query data, not every time the whole table is scanned, but only from the current partition to find the data you want to greatly improve the speed of data query.

As mentioned earlier, the use of partitioned tables in systems with large data volumes is more frequent.

So when do we have to create a partitioned table?

1, the size of the table is more than 2GB.
2. The table contains historical data, and new data is added to the new partition.

There are several advantages to using table partitioning to address the big Data scale:

1. Improve query performance: Queries on partitioned objects can search only the partitions they care about and improve the retrieval speed.
2. Enhanced usability: If one partition of the table fails, the data of the table in other partitions is still available;
3, Maintenance Convenience: If a partition of the table fails, you need to repair the data, only repair the partition;
4. Balanced I/O: Different partitions can be mapped to different disks to balance I/O and improve overall system performance.

General table if you want to convert to a partitioned table, use the online redefine table provided by Oracle.

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

The partition table has a concept called the partition field. So how to confirm the partition field, the partition field of a partitioned table is determined by what.

Here we only talk about two kinds of common situations.

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

2. Partitioning by list (for example, status values in a business table)

To say the same: Select the Partition field and select the Index field, the identification requirements for this field are relatively high.

In the extreme analysis of the sequence, the lowest identification of a field is that the field has only one value, so the index or partition on it is definitely the dog's blood.

and the partition field should be able to ensure that the distribution of the table data is basically uniform, if there is 100w data, there is a status column, only one state is inactive, the other is active, then this field can be partitioned by the list, but it does not achieve the purpose of our partition table.

Partitioned tables in Oracle