表分区方法

SQL> SELECT * FROM v$version WHERE rownum=1;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

SQL>
--range分区表
CREATE TABLE sales_range 
(salesman_id  NUMBER(5), 
salesman_name VARCHAR2(30), 
sales_amount  NUMBER(10), 
sales_date    DATE)
PARTITION BY RANGE(sales_date) 
(
PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','MM/DD/YYYY')),
PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','MM/DD/YYYY')),
PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','MM/DD/YYYY')),
PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','MM/DD/YYYY')),
PARTITION sales_all_other2000 VALUES LESS THAN(MAXVALUE)
);

--查看分区
SELECT table_name,partition_name,partition_position 
  FROM dba_tab_partitions 
 WHERE table_name='SALES_RANGE';

1.每个分区都含有VALUES LESS THAN 语句，用于指定分区的最高边界值（不包含），任何等于或大于该值的分区键都会被添加到下一个高分区。
2.所有的分区(除去第一个分)都含有一个最低边界，这个最低边界为上一个分区的VALUES LESS THAN语句指定的值。
3.MAXVALUE被定义为最高分区。

--List分区
使用list分区能够明确的控制行如何映射到分区，需要在分区的描述中指定一个离散（不连续）的值列表。list分区的优势能够使你分组与组织无序非相关的数据集合。

CREATE TABLE sales_list
(salesman_id  NUMBER(5), 
salesman_name VARCHAR2(30),
sales_state   VARCHAR2(20),
sales_amount  NUMBER(10), 
sales_date    DATE)
PARTITION BY LIST(sales_state)
(
PARTITION sales_west VALUES('California', 'Hawaii'),
PARTITION sales_east VALUES ('New York', 'Virginia', 'Florida'),
PARTITION sales_central VALUES('Texas', 'Illinois'),
PARTITION sales_other VALUES(DEFAULT)
);

DEFAUL指定一个默认的分区，以至于当行没有映射到任何分区时不会产生错误。

--散列分区
Hash partitioning enables easy partitioning of data that does not lend itself to range or list partitioning. It does this with a simple syntax and is easy to implement. It is a better choice than range partitioning when:
* You do not know beforehand how much data maps into a given range
* The sizes of range partitions would differ quite substantially or would be difficult to balance manually
* Range partitioning would cause the data to be undesirably clustered
* Performance features such as parallel DML, partition pruning, and partition-wise joins are important

The concepts of splitting, dropping or merging partitions do not apply to hash partitions. Instead, hash partitions can be added and coalesced.

CREATE TABLE sales_hash
(salesman_id  NUMBER(5), 
salesman_name VARCHAR2(30), 
sales_amount  NUMBER(10), 
week_no       NUMBER(2)) 
PARTITION BY HASH(salesman_id) 
PARTITIONS 4 
STORE IN (ts1, ts2, ts3, ts4);

The preceding statement creates a table sales_hash, which is hash partitioned on salesman_id field. The tablespace names are ts1, ts2, ts3, and ts4. With this syntax, we ensure that we create the partitions in a round-robin manner across the specified tablespaces.

--组合分区
组合分区使用range分区方法，在每一个分区里，子分区使用hash或list分区方法。组合range-hash分区提供了range分区的易管理和the data placement, striping, and parallelism advantages of hash partitioning.Composite range-list partitioning provides the manageability of range partitioning and the explicit control of list partitioning for the subpartitions.
Composite partitioning supports historical operations, such as adding new range partitions, but also provides higher degrees of parallelism for DML operations and finer granularity of data placement through subpartitioning.

CREATE TABLE bimonthly_regional_sales
(deptno NUMBER, 
 item_no VARCHAR2(20),
 txn_date DATE, 
 txn_amount NUMBER, 
 state VARCHAR2(2))
PARTITION BY RANGE (txn_date)
SUBPARTITION BY LIST (state)
SUBPARTITION TEMPLATE(
SUBPARTITION east VALUES('NY', 'VA', 'FL') TABLESPACE ts1,
SUBPARTITION west VALUES('CA', 'OR', 'HI') TABLESPACE ts2,
SUBPARTITION central VALUES('IL', 'TX', 'MO') TABLESPACE ts3)
(
PARTITION janfeb_2000 VALUES LESS THAN (TO_DATE('03/01/2000','MM/DD/YYYY')),
PARTITION marapr_2000 VALUES LESS THAN (TO_DATE('05/01/2000','MM/DD/YYYY')),
PARTITION mayjun_2000 VALUES LESS THAN (TO_DATE('07/01/2000','MM/DD/YYYY'))
);

CREATE TABLESPACE ts1
   DATAFILE 'ts1.dbf' SIZE 20M;
CREATE TABLESPACE ts2
   DATAFILE 'ts2.dbf' SIZE 20M;
CREATE TABLESPACE ts3
   DATAFILE 'ts3.dbf' SIZE 20M;
CREATE TABLESPACE ts4
   DATAFILE 'ts4.dbf' SIZE 20M;

CREATE TABLE sales_composite 
(salesman_id  NUMBER(5), 
 salesman_name VARCHAR2(30), 
 sales_amount  NUMBER(10), 
 sales_date    DATE)
PARTITION BY RANGE(sales_date) 
SUBPARTITION BY HASH(salesman_id)
SUBPARTITION TEMPLATE(
SUBPARTITION sp1 TABLESPACE ts1,
SUBPARTITION sp2 TABLESPACE ts2,
SUBPARTITION sp3 TABLESPACE ts3,
SUBPARTITION sp4 TABLESPACE ts4)
(PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','MM/DD/YYYY')),
 PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','MM/DD/YYYY')),
 PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','MM/DD/YYYY')),
 PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','MM/DD/YYYY')),
 PARTITION sales_may2000 VALUES LESS THAN(TO_DATE('06/01/2000','MM/DD/YYYY'))
);
--查看分区
SELECT table_name,partition_name,partition_position,SUBPARTITION_COUNT 
  FROM dba_tab_partitions WHERE table_name=upper('sales_composite');