In-depth Oracle 11g partitioning

Database partitioning is one of the key functions required by each database. Almost all Oracle databases use the Partition Function to improve query performance. The Oracle 11g Partition Function simplifies the daily management and maintenance of the database, greatly reducing the DBA (Database Design and Management Engineers).

The new features provided in Oracle 11g add more options for database partition functions, making the partition functions more flexible in use and maintenance. At the same time, oracle 11g also provides more options for partitioning.

Oracle 8.0 first introduced the Table Partitioning feature, making Oracle the first RDBMS vendor that supports physical partitions. Both SQL Server (2000) and DB2 support only logical partitions (using the union all view), while SQL Server 2005 does not directly support physical partitions (using the partition function ). The selection of Oracle's partition function is very popular among users, because the partition function can improve the performance, manageability and availability of applications, the most important of which is the DSS application. This feature is widely used, so every time a new version is released, the feature will be continuously improved.

The following table lists the partition functions that are constantly improved with version updates (advanced ):

Oracle Database Version	Partition Function
8.0.5	Introduction of range partitioning
8i	The function of partitioning by scattered columns and combining range hash partitions is introduced.
9i	The list partition and combined range list partition functions are introduced.
10G	The range partition, list partition, and three-column partition functions of the index organization table are introduced. This version also introduces other options for the combined partition function.
11G	Introduce the extended partition function: -Interval Partition -Foreign key Partition -Virtual column partitioning -Introduced the partition recommender.

Partition Function Type

Now let's briefly discuss the features of each of the above partitions:

Range Partition: Data is distributed according to the partition key value range. For example, if we select a date column as the partition key, the partition "JAN-2007" will include all the partition key values from 01-JAN-2007 to 31-JAN-2007 (assuming that the partition range is from the first day of the month the last day of the month ).

Hash Partition: Use the hash algorithm to use the partition key to determine the partition where the specified row is located. This partitioning method can maintain an I/O balance, but cannot be used for range queries or inequality queries.

List Partition: Data is distributed according to the partition key value list. This partitioning method is very useful for discrete lists, such as regions and countries.

Combined partitioning: Two data partition methods can be used as a combined partition method. Use the first data distribution method to partition the table, and then use the second Data Partition Method to perform secondary partitioning for each partition.

The combined partitioning method has the following combinations::

Combination range hash partition, combination range list partition, combination Range partition, combination list range partition, combination list partition, and combination list hash partition.

10 Gb supports range partitioning, list partitioning, or hash partitioning for the index organization table (table with indexes and data), but does not support combining partitioning.

Interval partition: an Enhanced Function of interval partition range partition introduced in version 11g to automate equi-sized range partitions. The created partition is used as metadata. Only the initial partition is a permanent partition. As data increases, more parts are allocated and new partitions and local indexes are automatically created.

 
 

  
  
SQL>CREATE TABLE SALES_PART   
  
  
(TIME_ID NUMBER,   
  
  
REGION_ID NUMBER,   
  
  
ORDER_ID NUMBER,   
  
  
ORDER_DATE DATE,   
  
  
SALES_QTY NUMBER(10,2),   
  
  
SALES_AMOUNT NUMBER(12,2)   
  
  
)   
  
  
PARTITION BY RANGE (ORDER_DATE)   
  
  
INTERVAL (NUMTOYMINTERVAL(1,'month')   
  
  
(PARTITION p_first VALUES LESS THAN ('01-JAN-2006'); 
 
 

The Numtoyminterval function converts numbers TO the interval year to month text ('Year' or 'month ').

The table for Interval partitioning has the traditional range part and the automatically generated interval part. You can use the set interval option of the alter table command to extend a TABLE with range partitions.

Foreign key partition: the introduction of the partition scheme is based on the premise that the relevant table obtains benefits through the same partition policy. The Detail table inherits the same partition scheme from the master table through a PK-FK relationship. We do not need to store the PARTITION key in the detail table, and use the keyword "partition by reference" to obtain the PARTITION Scheme of the master table in the detail table.

Virtual column partitioning: In earlier Oracle versions, Table Partitioning can be implemented only when the partition key exists and the table. The new "virtual columns" feature of Oracle 11G breaks this restriction and allows you to determine the partition key by using the expression of one or more columns in the table. Virtual columns are stored as metadata only.

For example, add a virtual column in table ACCOUNTS:

 
 

  
  
SQL>CREATE TABLE ACCOUNTS   
  
  
(acc_no number(10) not null,   
  
  
acc_name varchar2(50) not null,   
  
  
acc_loc varchar2(5),   
  
  
acc_branch number(2) generated always as   
  
  
(to_number(substr(to_char(acc_no),1,2))); 
 
 

Use a virtual column as the partition key:

 
 

  
  
SQL>CREATE TABLE accounts   
  
  
(acc_no number(10) not null,   
  
  
acc_name varchar2(50) not null,   
  
  
acc_loc varchar2(5),   
  
  
acc_branch number(2) generated always as   
  
  
(to_number(substr(to_char(acc_no),1,2)))   
  
  
partition by list (acc_branch); 
 
 

Partition recommender

Oracle 11g also provides a partition recommender that supports partition recommendation generation, similar to the physical view, physical view log, and index functions supported in 10g. In fact, the partition recommender is part of SQL Access Advisor in Oracle 11g. This recommender helps generate recommendations and shows the results that can be achieved after the recommendation partition is implemented. It also generates scripts for creating valid partitions, which can be manually submitted to Oracle or Enterprise Manager through SQL * Plus.

1. Oracle 11g R2 highlights the RAC technology as scheduled
2. Security Features of Oracle 11g transparent Data Encryption
3. Auto-tuning in Oracle 11g
4. Model Management for new features of Oracle 11g
5. Automatic database maintenance task management in Oracle 11g R1