Partition Function in Oracle Database 10 GB

Welcome to the Oracle community forum and interact with 2 million technical staff. The Oracle Partition Function Improves the manageability, performance, and availability of many applications. Through the partition function, you can further segment the table, index, and index organization table to more accurately manage and access these database objects. Oracle provides a wide range of points

Welcome to the Oracle community forum and interact with 2 million technicians> the Oracle Partition Function Improves the manageability, performance, and availability of many applications. Through the partition function, you can further segment the table, index, and index organization table to more accurately manage and access these database objects. Oracle provides a wide range of points

Welcome to the Oracle community forum and interact with 2 million technical staff> enter

Oracle partitioning improves the manageability, performance, and availability of many applications. Through the partition function, you can further segment the table, index, and index organization table to more accurately manage and access these database objects. Oracle provides a wide range of partitioning solutions to meet each business requirement. In addition, partitions in SQL statements are completely transparent, so this function can be applied to almost any application.

　　Advantages of the Partition Function

By improving manageability, performance, and availability, the partitioning function brings great benefits to various applications. In general, partitions can greatly improve the performance of some queries and maintenance operations. In addition, partitions can greatly simplify common management tasks. Through partitioning, database designers and administrators can solve some of the challenges posed by cutting-edge applications. Partitioning is a key tool for building a gigabit data system or an ultra-high availability system.

　　Basic knowledge about the Partition Function

The partition function further segments a table, index, or index organization table. The segments of these database objects are called partitions. Each partition has its own name, and you can select its own storage features. From the perspective of the database administrator, the objects in a partition have multiple segments, which can be managed collectively or separately. This makes the database administrator quite flexible in managing the objects after the partition. However, from the perspective of the application, the partitioned table is exactly the same as the non-partitioned table. You do not need to modify it when using the SQL DML command to access the partitioned table.

Table partitions are implemented through the "partition key". The partition key refers to some columns that determine the partition of a row. Oracle Database 10 Gb provides six techniques for partitioning tables:

　　Range Partition

Each partition is specified by a partition key value range (for a table with a date column as the partition key, the "January 2005" partition contains rows whose partition key value is from "January 1, 2005" to "January 31, 2005 ).

　　List Partition

Each partition is specified by a list of partition key values (for a table with a region column as the partition key, the "North America" partition may contain values "Canada", "United States", and "Mexico ").

　　Hash Partition

Uses the hash algorithm as the partition key to determine the partition where the specified row is located.

　　Combine range hash partitions

A combination of range and hash Partitioning technology. With this combination, the table is first partitioned by range, and then further subdivided by hash Partitioning technology for each individual range partition. The index organization table can only be partitioned by range.

　List partitions

A combination of range and list partition technology. With this combination, the table is first partitioned in a range, and then the list partition technology is used for further subdivision for each individual range partition. The index organization table can be partitioned by range, list, or hash.

Oracle Database 10 Gb also provides three types of partition indexes:

　　Local index

Local indexes are the same indexes as the partitions in the basic table. Each partition of the Local index corresponds to only one partition of the base table.

　　Global partition Index

A global partition index is an index that uses a partition key different from the partition key of the table in which it is located. The index can be a partition table or a non-partition table. Global partition indexes can be partitioned using range or hash partitions. For example, a table can perform range partitioning by month, so it has 12 partitions, and the index on the table can use different partition keys for range partitioning, thus having different partitions.

[1] [2]