/* Partition Table */
Advantages: * Improve query performance, only access partitions when performing queries * Easy to manage and maintain, each partition is independent of maintenance, can reconstruct index, backup, and recovery of partitions separately.
After the table is partitioned, each partition can have different storage parameters, or can be located in different tablespaces. A table can be divided into any number of partitions, but if there is a field containing the LONG or LONG RAW type in the table, Cannot be partitioned
The table needs to be partitioned on the table below 1. The size of the table is more than 2G2. DML operations for parallel to a table are required 3. To balance the hard disk I / O operation, you need to disperse a table in different tablespace 4. Need table Part of the part is set to read, and the other part is updatable.
Oracle 9i has four types of partitions: range partition, list partition, hash partition, composite partition
1. The range partition partition is partitioning tables in accordance with the range of values in the partition field, usually used for the partition field is a table of the date type.
create table sales_range (salesman_id number (5), salesman_name varchar2 (30), sales_amount number (10), sales_data date) partition by range (sales_date) (partition pt_sales1999_q4 values less than (to_date ('01 / 01/2000 ',' DD / MM / YYY ')) tablespace sale1999_q4, pctfree 0 storage (initial 100M next 100M pctincrease 0) partition pt_sales2000_q1 values less than (to_date ('01 / 04/2000', 'DD / MM / YYY')) tablespace sale2000_q1, partition Pt_sales2000_q2 values less Than (To_date ('01 / 07/2000 ') TABLESPACE SALE2000_Q2, Partition Pt_sales2000_Q3 VALUES LESS THAN (To_Date ('01 / 10/2000', 'DD / MM / YYY' )) TABLESPACE SALE2000_Q3, Partition Pt_sales2000_Q4 Values Less Than (MaxValue) TableSpace Sale2000_Q4,) Storage (Initial 50M Next 50m Pctincrease 0 MaxENTENTS 4096) NOLOGGING;
Oracle will think that the NULL value is greater than all other non-NULL values, if the value of the field can be null, you must use the maxValue keyword specified upper limit in some partition
Add new range partition alter table sales_range add partition pt_sales2000_q4 values less (to_date ('01 / 01/2001 ',' DD / mm / yyyy ')) TABLESPACE SALES2000_Q4;
2. List partition (need to specify a list for each partition) If the value of the partition field does not divide the range, the value range of the partition field is just a collection of small values, which can be subsequent partitions.
create table sales_list (salesman_id number (5), salesman_name varchar2 (30), sales_amount number (10), sales_data date) partition by list (sales_state) (partition sales_west values in ( 'california', 'hawaii'), partition sales_east values in ( 'new york', 'virginia', 'florida'), partition sales_central values in ( 'texas', 'illionis'),) storage (initial 50M next 50M pctincrease 0 maxentents 4096) nologging; Add new list partition alter table Sales_list add partition sales_north value, 'motana', 'minnisota';
/ * Partition index * /
In Oracle, you can establish three types of indexes for partition tables 1. Local partition index (establish index partitions separately, each partition is independent, used for DSS environment) 2. Global partition index (for the whole The partition table establishes an index, then partitions the index to the index, used in the OLTP environment) 3. The global non-partition index (that is, the role of the geographical partition index, used for the OLTP environment in)
