一、分区表基础知识

Oracle提供了分区技术。分区表通过对分区列的判断，把分区列不同的记录，放到不同的分区中。分区完全对应用透明。Oracle的分区表可以包括多个分区，每个分区都是一个独立的段（SEGMENT），可以存放到不同的表空间中。查询时可以通过查询表来访问各个分区中的数据，也可以通过在查询时直接指定分区的方法来进行查询。

When to Partition a Table什么时候需要分区表，官网的2个建议如下：

1、Tables greater than 2GB should always be considered for partitioning.

2、Tables containing historical data, in which new data is added into the newest partition. A typical example is a historical table where only the current month's data is updatable and the other 11 months are read only.

在Oracle 10g中最多支持：1024k-1个分区：
Tables can be partitioned into up to 1024K-1 separate partitions

分区提供以下优点：

（1）由于将数据分散到各个分区中，减少了数据损坏的可能性；
（2）可以对单独的分区进行备份和恢复；
（3）可以将分区映射到不同的物理磁盘上，来分散IO；
（4）提高可管理性、可用性和性能。

Oracle 10g提供了以下几种分区类型：

（1）范围分区（range）；
（2）哈希分区（hash）；
（3）列表分区（list）；

（4）间隔分区（interval）

（5）引用分区（reference）
（5）复合分区（composite）；

以下是11g官方文档上每一种分区表的举例说明：

********************************************************************************

Creating Range-Partitioned Tables and Global Indexes（范围分区表）

The PARTITION BY RANGE clause of the CREATE TABLE statement specifies that the table or index is to be range-partitioned. The PARTITION clauses identify the individual partition ranges, and the optional subclauses of a PARTITION clause can specify physical and other attributes specific to a partition segment. If not overridden at the partition level, partitions inherit the attributes of their underlying table.

CREATE TABLE语句的PARTITION BY RANGE子句指定表或索引将进行范围分区。 PARTITION子句标识各个分区范围，PARTITION子句的可选子句可以指定特定于分区段的物理属性和其他属性。如果不在分区级别覆盖，分区将继承其基础表的属性。

Creating a Range-Partitioned Table

Example 4-1 creates a table of four partitions, one for each quarter of sales. The columns sale_year, sale_month, and sale_day are the partitioning columns, while their values constitute the partitioning key of a specific row. The VALUES LESS THAN clause determines the partition bound: rows with partitioning key values that compare less than the ordered list of values specified by the clause are stored in the partition. Each partition is given a name (sales_q1, sales_q2, ...), and each partition is contained in a separate tablespace (tsa, tsb, ...).

示例4-1创建一个四个分区的表，每个销售季度一个。列sales_year，sale_month和sale_day是分区列，而它们的值构成特定行的分区键。 VALUES LESS THAN子句确定分区绑定：具有比小于子句指定的值的有序列表的分区键值的行存储在分区中。每个分区都有一个名称（sales_q1，sales_q2，...），每个分区都包含在一个单独的表空间（tsa，tsb，...）中

Example 4-1 Creating a range-partitioned table

CREATE TABLE sales

  ( prod_id       NUMBER(6)

  , cust_id       NUMBER

  , time_id       DATE

  , channel_id    CHAR(1)

  , promo_id      NUMBER(6)

  , quantity_sold NUMBER(3)

  , amount_sold   NUMBER(10,2)

 PARTITION BY RANGE (time_id)

 ( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy'))

    TABLESPACE tsa

 , PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy'))

    TABLESPACE tsb

 , PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))

    TABLESPACE tsc

 , PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))

    TABLESPACE tsd

);

A row with time_id=17-MAR-2006 would be stored in partition sales_q1_2006.

For more information, refer to "Using Multicolumn Partitioning Keys".

In Example 4-2, more complexity is added to the example presented earlier for a range-partitioned table. Storage parameters and a LOGGING attribute are specified at the table level. These replace the corresponding defaults inherited from the tablespace level for the table itself, and are inherited by the range partitions. However, because there was little business in the first quarter, the storage attributes for partition sales_q1_2006 are made smaller. The ENABLE ROW MOVEMENT clause is specified to allow the automatic migration of a row to a new partition if an update to a key value is made that would place the row in a different partition.

示例4-2中，对于范围分区表，在前面提到示例的基础上增加了更多的复杂性。 在表级别制定了存储参数和LOGGING属性。这些替换表本身继承自表空间级别的相应缺省值，并由范围分区继承。 但是，由于第一季度的业务很少，因此分区sales_q1_2006的存储属性较小。 指定ENABLE ROW MOVEMENT子句允许将行自动迁移到新分区，如果对键值的更新将使行位于不同的分区。

Example 4-2 Creating a range-partitioned table with ENABLE ROW MOVEMENT

CREATE TABLE sales

  ( prod_id       NUMBER(6)

  , cust_id       NUMBER

  , time_id       DATE

  , channel_id    CHAR(1)

  , promo_id      NUMBER(6)

  , quantity_sold NUMBER(3)

  , amount_sold   NUMBER(10,2)

 STORAGE (INITIAL 100K NEXT 50K) LOGGING

 PARTITION BY RANGE (time_id)

 ( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy'))

    TABLESPACE tsa STORAGE (INITIAL 20K NEXT 10K)

 , PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy'))

    TABLESPACE tsb

 , PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))

    TABLESPACE tsc

 , PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))

    TABLESPACE tsd

 ENABLE ROW MOVEMENT;

Creating a Range-Partitioned Global Index

The rules for creating range-partitioned global indexes are similar to those for creating range-partitioned tables. Example 4-3 creates a range-partitioned global index on sale_month for the tables created in the previous examples. Each index partition is named but is stored in the default tablespace for the index.

创建范围分区全局索引的规则与创建范围分区表的规则相似。 示例4-3在前面的示例中创建的表中，在sale_month上创建一个范围分区的全局索引。 命名每个索引分区，但存储在索引的默认表空间中

Example 4-3 Creating a range-partitioned global index table

CREATE INDEX amount_sold_ix ON sales(amount_sold)

   GLOBAL PARTITION BY RANGE(sale_month)

      ( PARTITION p_100 VALUES LESS THAN (100)

      , PARTITION p_1000 VALUES LESS THAN (1000)

      , PARTITION p_10000 VALUES LESS THAN (10000)

      , PARTITION p_100000 VALUES LESS THAN (100000)

      , PARTITION p_1000000 VALUES LESS THAN (1000000)

      , PARTITION p_greater_than_1000000 VALUES LESS THAN (maxvalue)

);

Note:

If your enterprise has databases using different character sets, use caution when partitioning on character columns, because the sort sequence of characters is not identical in all character sets. For more information, see Oracle Database Globalization Support Guide.

如果你所在的企业具有使用不同字符集的数据库，则在字符列分区时请小心，因为字符的排序顺序在所有字符集中都不相同。

Creating Interval-Partitioned Tables（间隔分区表）

The INTERVAL clause of the CREATE TABLE statement establishes interval partitioning for the table. You must specify at least one range partition using thePARTITION clause. The range partitioning key value determines the high value of the range partitions, which is called the transition point, and the database automatically creates interval partitions for data beyond that transition point. The lower boundary of every interval partition is the non-inclusive upper boundary of the previous range or interval partition.

CREATE TABLE语句的INTERVAL子句建立表的间隔分区。你必须使用PARTITION子句指定至少一个范围分区。范围分区键值确定范围分区的高值，称为转换点，数据库自动创建超出该转换点的数据的间隔分区。每个间隔分区的下边界是前一范围或间隔分区的不包含上限。

For example, if you create an interval partitioned table with monthly intervals and the transition point is at January 1, 2010, then the lower boundary for the January 2010 interval is January 1, 2010. The lower boundary for the July 2010 interval is July 1, 2010, regardless of whether the June 2010 partition was previously created. Note, however, that using a date where the high or low bound of the partition would be out of the range set for storage causes an error. For example, TO_DATE('9999-12-01', 'YYYY-MM-DD') causes the high bound to be 10000-01-01, which would not be storable if 10000 is out of the legal range.

例如，如果你每隔一个月创建一个间隔分区表，并且转换点是2010年1月1日，则2010年1月间隔的下边界是2010年1月1日；2010年7月间隔的下边界是7月1日，2010年，无论2010年6月分区是否以前创建。但是请注意，使用分区的高或低限制超出为存储设置的范围的日期会导致错误。例如，TO_DATE（'9999-12-01'，'YYYY-MM-DD'）导致高限为10000-01-01，如果10000超出合法范围，则不能存储。

For interval partitioning, the partitioning key can only be a single column name from the table and it must be of NUMBER or DATE type. The optional STORE INclause lets you specify one or more tablespaces into which the database stores interval partition data using a round-robin algorithm for subsequently created interval partitions.

对于间隔分区，分区键只能是表中的单个列名称，它必须为NUMBER或DATE类型。可选的STORE IN子句允许您使用用于后续创建的间隔分区的循环算法来指定数据库存储间隔分区数据的一个或多个表空间。

Example 4-4 specifies four partitions with varying interval widths. It also specifies that above the transition point of January 1, 2010, partitions are created with an interval width of one month.

示例4-4指定了具有不同间隔宽度的四个分区。它还指定在2010年1月1日的转换点之后，创建分区，间隔宽度为一个月。

Example 4-4 Creating an interval-partitioned table

CREATE TABLE interval_sales

    ( prod_id        NUMBER(6)

    , cust_id        NUMBER

    , time_id        DATE

    , channel_id     CHAR(1)

    , promo_id       NUMBER(6)

    , quantity_sold  NUMBER(3)

    , amount_sold    NUMBER(10,2)

  PARTITION BY RANGE (time_id)

  INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))

    ( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2008', 'DD-MM-YYYY')),

      PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2009', 'DD-MM-YYYY')),

      PARTITION p2 VALUES LESS THAN (TO_DATE('1-7-2009', 'DD-MM-YYYY')),

      PARTITION p3 VALUES LESS THAN (TO_DATE('1-1-2010', 'DD-MM-YYYY')) );

The high bound of partition p3 represents the transition point. p3 and all partitions below it (p0, p1, and p2 in this example) are in the range section while all partitions above it fall into the interval section.

分区p3的高界面表示转换点。 p3及其下方的所有分区（本示例中为p0，p1和p2）位于范围部分，而其上方的所有分区都落入间隔部分。

Creating Hash-Partitioned Tables and Global Indexes（Hash分区表）

The PARTITION BY HASH clause of the CREATE TABLE statement identifies that the table is to be hash-partitioned. The PARTITIONS clause can then be used to specify the number of partitions to create, and optionally, the tablespaces to store them in. Alternatively, you can use PARTITION clauses to name the individual partitions and their tablespaces.

CREATE TABLE语句的PARTITION BY HASH子句标识表要进行散列分区。 然后可以使用PARTITIONS子句来指定要创建的分区数，以及可选的表空间来存储它们。或者，可以使用PARTITION子句命名各个分区及其表空间。

The only attribute you can specify for hash partitions is TABLESPACE. All of the hash partitions of a table must share the same segment attributes (exceptTABLESPACE), which are inherited from the table level.

可以为散列分区指定的唯一属性是TABLESPACE。 表的所有哈希分区必须共享相同的段属性（TABLESPACE除外），它们从表级继承。

Creating a Hash Partitioned Table

Example 4-5 creates a hash-partitioned table. The partitioning column is id, four partitions are created and assigned system generated names, and they are placed in four named tablespaces (gear1, gear2, ...).

示例4-5创建一个散列分区表。 分区列是id，创建了四个分区，并分配了系统生成的名称，并将它们放在四个命名的表空间（gear1，gear2，...）中。

Example 4-5 Creating a hash-partitioned table

CREATE TABLE scubagear

     (id NUMBER,

      name VARCHAR2 (60))

   PARTITION BY HASH (id)

   PARTITIONS 4

   STORE IN (gear1, gear2, gear3, gear4);

For more information, refer to "Using Multicolumn Partitioning Keys".

The following examples illustrate two methods of creating a hash-partitioned table named dept. In the first example the number of partitions is specified, but system generated names are assigned to them and they are stored in the default tablespace of the table.

下面示例说明了创建名为dept的散列分区表的两种方法。 在第一个示例中，指定了分区数，但是将系统生成的名称分配给它们，并将它们存储在表的默认表空间中。

CREATE TABLE dept (deptno NUMBER, deptname VARCHAR(32))

     PARTITION BY HASH(deptno) PARTITIONS 16;

In the following example, names of individual partitions, and tablespaces in which they are to reside, are specified. The initial extent size for each hash partition (segment) is also explicitly stated at the table level, and all partitions inherit this attribute.

在下面的示例中，指定单个分区的名称以及它们所在的表空间。 每个散列分区（段）的初始扩展区大小也在表级别明确说明，所有分区都继承此属性。

CREATE TABLE dept (deptno NUMBER, deptname VARCHAR(32))

     STORAGE (INITIAL 10K)

     PARTITION BY HASH(deptno)

       (PARTITION p1 TABLESPACE ts1, PARTITION p2 TABLESPACE ts2,

        PARTITION p3 TABLESPACE ts1, PARTITION p4 TABLESPACE ts3);

If you create a local index for this table, the database constructs the index so that it is equipartitioned with the underlying table. The database also ensures that the index is maintained automatically when maintenance operations are performed on the underlying table. The following is an example of creating a local index on the table dept:

如果你为此表创建本地索引，则数据库将构建索引，以便将其与基础表进行分区。 数据库还确保在基础表上执行维护操作时自动维护索引。 以下是在表dept上创建本地索引的示例：

CREATE INDEX loc_dept_ix ON dept(deptno) LOCAL;

You can optionally name the hash partitions and tablespaces into which the local index partitions are to be stored, but if you do not do so, then the database uses the name of the corresponding base partition as the index partition name, and stores the index partition in the same tablespace as the table partition.

你可以选择性的命名要存储本地索引分区的散列分区和表空间，但如果不这样做，则数据库将使用相应的基本分区的名称作为索引分区名称，并将索引分区存储在与表分区相同的表空间中。

Creating a Hash-Partitioned Global Index

Hash-partitioned global indexes can improve the performance of indexes where a small number of leaf blocks in the index have high contention in multiuser OLTP environments. Hash-partitioned global indexes can also limit the impact of index skew on monotonously increasing column values. Queries involving the equality and IN predicates on the index partitioning key can efficiently use hash-partitioned global indexes.

散列分区的全局索引可以提高索引的性能，其中索引中少量的叶块在多用户OLTP环境中具有高争用。 散列分区的全局索引也可以限制索引偏移对列值单调递增的影响。 涉及索引分区关键字的等式和IN谓词的查询可以有效地使用散列分区的全局索引。

The syntax for creating a hash partitioned global index is similar to that used for a hash partitioned table. For example, the statement in Example 4-6 creates a hash-partitioned global index:

用于创建散列分区全局索引的语法与用于散列分区表的语法相似。 例如，示例4-6中的语句创建了一个散列分区的全局索引：

Example 4-6 Creating a hash-partitioned global index

CREATE INDEX hgidx ON tab (c1,c2,c3) GLOBAL

     PARTITION BY HASH (c1,c2)

     (PARTITION p1  TABLESPACE tbs_1,

      PARTITION p2  TABLESPACE tbs_2,

      PARTITION p3  TABLESPACE tbs_3,

      PARTITION p4  TABLESPACE tbs_4);

Creating List-Partitioned Tables（列表分区）

The semantics for creating list partitions are very similar to those for creating range partitions. However, to create list partitions, you specify a PARTITION BY LIST clause in the CREATE TABLE statement, and the PARTITION clauses specify lists of literal values, which are the discrete values of the partitioning columns that qualify rows to be included in the partition. For list partitioning, the partitioning key can only be a single column name from the table.

用于创建列表分区的语义与创建范围分区的语义非常相似。但是，要创建列表分区，请在CREATE TABLE语句中指定一个PARTITION BY LIST子句，并且PARTITION子句指定文本值列表，这些列表是限定要包含在分区中的行的分区列的离散值。对于列表分区，分区键只能是表中的单列名称。

Available only with list partitioning, you can use the keyword DEFAULT to describe the value list for a partition. This identifies a partition that accommodates rows that do not map into any of the other partitions.

只能使用列表分区，可以使用关键字DEFAULT来描述分区的值列表。这标识了一个容纳不映射到任何其他分区的行的分区。

As with range partitions, optional subclauses of a PARTITION clause can specify physical and other attributes specific to a partition segment. If not overridden at the partition level, partitions inherit the attributes of their parent table.

与范围分区一样，PARTITION子句的可选子句可以指定特定于分区段的物理属性和其他属性。如果不在分区级别覆盖，分区将继承其父表的属性。

Example 4-7 creates a list-partitioned table. It creates table q1_sales_by_region which is partitioned by regions consisting of groups of U.S. states.

示例4-7创建一个列表分区表。创建表q1_sales_by_region，该表由美国各州组成的区域划分。

Example 4-7 Creating a list-partitioned table

CREATE TABLE q1_sales_by_region

      (deptno number,

       deptname varchar2(20),

       quarterly_sales number(10, 2),

       state varchar2(2))

   PARTITION BY LIST (state)

      (PARTITION q1_northwest VALUES ('OR', 'WA'),

       PARTITION q1_southwest VALUES ('AZ', 'UT', 'NM'),

       PARTITION q1_northeast VALUES  ('NY', 'VM', 'NJ'),

       PARTITION q1_southeast VALUES ('FL', 'GA'),

       PARTITION q1_northcentral VALUES ('SD', 'WI'),

       PARTITION q1_southcentral VALUES ('OK', 'TX'));

A row is mapped to a partition by checking whether the value of the partitioning column for a row matches a value in the value list that describes the partition.

通过检查行的分区列的值是否与描述分区的值列表中的值相匹配，将行映射到分区。

For example, some sample rows are inserted as follows:

· (10, 'accounting', 100, 'WA') maps to partition q1_northwest

· (20, 'R&D', 150, 'OR') maps to partition q1_northwest

· (30, 'sales', 100, 'FL') maps to partition q1_southeast

· (40, 'HR', 10, 'TX') maps to partition q1_southwest

· (50, 'systems engineering', 10, 'CA') does not map to any partition in the table and raises an error

Unlike range partitioning, with list partitioning, there is no apparent sense of order between partitions. You can also specify a default partition into which rows that do not map to any other partition are mapped. If a default partition were specified in the preceding example, the state CA would map to that partition.

与范围分区不同，使用列表分区，分区之间没有明显的顺序感。 你还可以指定一个默认分区，哪些行不映射到任何其他分区。 如果在上述示例中指定了默认分区，则状态CA将映射到该分区。

Example 4-8 creates table sales_by_region and partitions it using the list method. The first two PARTITION clauses specify physical attributes, which override the table-level defaults. The remaining PARTITION clauses do not specify attributes and those partitions inherit their physical attributes from table-level defaults. A default partition is also specified.

示例4-8创建表sales_by_region并使用list方法对其进行分区。 前两个PARTITION子句指定物理属性，它们覆盖表级默认值。 剩余的PARTITION子句不指定属性，这些分区从表级默认值继承其物理属性。 还指定了默认分区。

Example 4-8 Creating a list-partitioned table with a default partition

CREATE TABLE sales_by_region (item# INTEGER, qty INTEGER,

             store_name VARCHAR(30), state_code VARCHAR(2),

             sale_date DATE)

     STORAGE(INITIAL 10K NEXT 20K) TABLESPACE tbs5

     PARTITION BY LIST (state_code)

     PARTITION region_east

        VALUES ('MA','NY','CT','NH','ME','MD','VA','PA','NJ')

        STORAGE (INITIAL 8M)

        TABLESPACE tbs8,

     PARTITION region_west

        VALUES ('CA','AZ','NM','OR','WA','UT','NV','CO')

        NOLOGGING,

     PARTITION region_south

        VALUES ('TX','KY','TN','LA','MS','AR','AL','GA'),

     PARTITION region_central

        VALUES ('OH','ND','SD','MO','IL','MI','IA'),

     PARTITION region_null

        VALUES (NULL),

     PARTITION region_unknown

        VALUES (DEFAULT)

);

Creating Reference-Partitioned Tables（引用分区表）

To create a reference-partitioned table, you specify a PARTITION BY REFERENCE clause in the CREATE TABLE statement. This clause specifies the name of a referential constraint and this constraint becomes the partitioning referential constraint that is used as the basis for reference partitioning in the table. The referential constraint must be enabled and enforced.

要创建引用分区表，请在CREATE TABLE语句中指定一个PARTITION BY REFERENCE子句。 此子句指定引用约束的名称，并且此约束成为用作表中引用分区基础的分区参照约束。 必须启用和执行参照约束。

As with other partitioned tables, you can specify object-level default attributes, and you can optionally specify partition descriptors that override the object-level defaults on a per-partition basis.

与其他分区表一样，你可以指定对象级的默认属性，并且可以选择指定在每个分区的基础上覆盖对象级默认值的分区描述符。

Example 4-9 creates a parent table orders which is range-partitioned on order_date. The reference-partitioned child table order_items is created with four partitions, Q1_2005, Q2_2005, Q3_2005, and Q4_2005, where each partition contains the order_items rows corresponding to orders in the respective parent partition.

示例4-9创建了在order_date上进行范围分区的父表顺序。 使用四个分区Q1_2005，Q2_2005，Q3_2005和Q4_2005创建引用分区子表order_item，其中每个分区包含与相应父分区中的订单相对应的order_item行。

Example 4-9 Creating reference-partitioned tables

CREATE TABLE orders

    ( order_id           NUMBER(12),

      order_date         TIMESTAMP WITH LOCAL TIME ZONE,

      order_mode         VARCHAR2(8),

      customer_id        NUMBER(6),

      order_status       NUMBER(2),

      order_total        NUMBER(8,2),

      sales_rep_id       NUMBER(6),

      promotion_id       NUMBER(6),

      CONSTRAINT orders_pk PRIMARY KEY(order_id)

  PARTITION BY RANGE(order_date)

    ( PARTITION Q1_2005 VALUES LESS THAN (TO_DATE('01-APR-2005','DD-MON-YYYY')),

      PARTITION Q2_2005 VALUES LESS THAN (TO_DATE('01-JUL-2005','DD-MON-YYYY')),

      PARTITION Q3_2005 VALUES LESS THAN (TO_DATE('01-OCT-2005','DD-MON-YYYY')),

      PARTITION Q4_2005 VALUES LESS THAN (TO_DATE('01-JAN-2006','DD-MON-YYYY'))

);

CREATE TABLE order_items

    ( order_id           NUMBER(12) NOT NULL,

      line_item_id       NUMBER(3)  NOT NULL,

      product_id         NUMBER(6)  NOT NULL,

      unit_price         NUMBER(8,2),

      quantity           NUMBER(8),

      CONSTRAINT order_items_fk

      FOREIGN KEY(order_id) REFERENCES orders(order_id)

    PARTITION BY REFERENCE(order_items_fk);

If partition descriptors are provided, then the number of partitions described must exactly equal the number of partitions or subpartitions in the referenced table. If the parent table is a composite partitioned table, then the table has one partition for each subpartition of its parent; otherwise the table has one partition for each partition of its parent.

如果提供了分区描述符，则描述的分区数必须等于引用表中的分区或子分区数。 如果父表是一个复合分区表，那么该表对其父类的每个子分区具有一个分区; 否则表中的父分区的每个分区都有一个分区。

Partition bounds cannot be specified for the partitions of a reference-partitioned table.

不能为引用分区表的分区指定分区范围。

The partitions of a reference-partitioned table can be named. If a partition is not explicitly named, then it inherits its name from the corresponding partition in the parent table, unless this inherited name conflicts with an existing explicit name. In this case, the partition has a system-generated name.

引用分区表的分区可以命名。 如果分区没有被明确命名，那么它会从父表中的相应分区继承其名称，除非该继承的名称与现有的显式名称冲突。在这种情况下，分区具有系统生成的名称。

Partitions of a reference-partitioned table collocate with the corresponding partition of the parent table, if no explicit tablespace is specified for the reference-partitioned table's partition.

如果未为引用分区表分区指定显式表空间，引用分区表的分区与父表的相应分区并置。

Creating Composite Partitioned Tables（组合分区表）

To create a composite partitioned table, you start by using the PARTITION BY [RANGE | LIST] clause of a CREATE TABLE statement. Next, you specify a SUBPARTITION BY [RANGE | LIST | HASH] clause that follows similar syntax and rules as the PARTITION BY [RANGE | LIST | HASH] clause. The individual PARTITION andSUBPARTITION or SUBPARTITIONS clauses, and optionally a SUBPARTITION TEMPLATE clause, follow.

Creating Composite Range-Hash Partitioned Tables（范围-散列组合分区表）

The statement in Example 4-10 creates a range-hash partitioned table. Four range partitions are created, each containing eight subpartitions. Because the subpartitions are not named, system generated names are assigned, but the STORE IN clause distributes them across the 4 specified tablespaces (ts1, ...,ts4).

示例4-10中的语句创建了一个范围散列分区表。 创建四个范围分区，每个分区包含八个子分区。 由于子分区未命名，系统生成的名称将被分配，但STORE IN子句会分布在四个指定的表空间（ts1，...，ts4）中。

Example 4-10 Creating a composite range-hash partitioned table

CREATE TABLE sales

  ( prod_id       NUMBER(6)

  , cust_id       NUMBER

  , time_id       DATE

  , channel_id    CHAR(1)

  , promo_id      NUMBER(6)

  , quantity_sold NUMBER(3)

  , amount_sold   NUMBER(10,2)

 PARTITION BY RANGE (time_id) SUBPARTITION BY HASH (cust_id)

  SUBPARTITIONS 8 STORE IN (ts1, ts2, ts3, ts4)

 ( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy'))

 , PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy'))

 , PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))

 , PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))

);

The partitions of a range-hash partitioned table are logical structures only, because their data is stored in the segments of their subpartitions. As with partitions, these subpartitions share the same logical attributes. Unlike range partitions in a range-partitioned table, the subpartitions cannot have different physical attributes from the owning partition, although they are not required to reside in the same tablespace.

范围散列分区表的分区只是逻辑结构，因为它们的数据存储在子分区的段中。这些子分区具有相同的逻辑属性。 与范围分区表中的分区不同，子分区不具有与所属分区不同的物理属性，尽管它们不需要驻留在相同的表空间中。

Attributes specified for a range partition apply to all subpartitions of that partition. You can specify different attributes for each range partition, and you can specify a STORE IN clause at the partition level if the list of tablespaces across which the subpartitions of that partition should be spread is different from those of other partitions. All of this is illustrated in the following example.

范围分区指定的属性适用于该分区的所有子分区。你可以为每个范围分区指定不同的属性，如果该分区的子分区在其上展开的表空间列表与其他分区的列表不同，则可以在分区级别指定STORE IN子句。 以下示例说明了所有这些。

CREATE TABLE emp (deptno NUMBER, empname VARCHAR(32), grade NUMBER)

     PARTITION BY RANGE(deptno) SUBPARTITION BY HASH(empname)

        SUBPARTITIONS 8 STORE IN (ts1, ts3, ts5, ts7)

    (PARTITION p1 VALUES LESS THAN (1000),

     PARTITION p2 VALUES LESS THAN (2000)

        STORE IN (ts2, ts4, ts6, ts8),

     PARTITION p3 VALUES LESS THAN (MAXVALUE)

       (SUBPARTITION p3_s1 TABLESPACE ts4,

        SUBPARTITION p3_s2 TABLESPACE ts5));

To learn how using a subpartition template can simplify the specification of a composite partitioned table, see "Using Subpartition Templates to Describe Composite Partitioned Tables".

The following statement is an example of creating a local index on the emp table where the index segments are spread across tablespaces ts7, ts8, and ts9.

以下语句是在emp表上创建本地索引的示例，其中索引段分布在表空间ts7，ts8和ts9之间。

CREATE INDEX emp_ix ON emp(deptno)

     LOCAL STORE IN (ts7, ts8, ts9);

This local index is equipartitioned with the base table as follows:

· It consists of as many partitions as the base table.

· Each index partition consists of as many subpartitions as the corresponding base table partition.

· Index entries for rows in a given subpartition of the base table are stored in the corresponding subpartition of the index.

这个本地索引与基准表分配如下：

?它由与基表一样多的分区组成。

?每个索引分区由与相应的基表分区一样多的子分区组成。

?基表的给定子分区中的行的索引条目存储在索引的相应子分区中。

Creating Composite Range-List Partitioned Tables（范围-列表组合分区）

The range partitions of a range-list composite partitioned table are described as for non-composite range partitioned tables. This enables optional subclauses of a PARTITION clause to specify physical and other attributes, including tablespace, specific to a partition segment. If not overridden at the partition level, partitions inherit the attributes of their underlying table.

范围列表复合分区表的范围分区描述为非复合范围分区表。 这使得PARTITION子句的可选子句可以指定特定于分区段的物理属性和其他属性，包括表空间。 如果不在分区级别覆盖，分区将继承其基础表的属性。

The list subpartition descriptions, in the SUBPARTITION clauses, are described as for non-composite list partitions, except the only physical attribute that can be specified is a tablespace (optional). Subpartitions inherit all other physical attributes from the partition description.

在SUBPARTITION子句中的列表子分类描述被描述为非复合列表分区，可以指定的唯一物理属性是表空间（可选）。 子分区从分区描述继承所有其他物理属性。

Example 4-11 illustrates how range-list partitioning might be used. The example tracks sales data of products by quarters and within each quarter, groups it by specified states.

示例4-11说明了如何使用范围列表分区。 该示例跟踪季度和季度内产品的销售数据，由指定的州分组。

Example 4-11 Creating a composite range-list partitioned table

CREATE TABLE quarterly_regional_sales

      (deptno number, item_no varchar2(20),

       txn_date date, txn_amount number, state varchar2(2))

  TABLESPACE ts4

  PARTITION BY RANGE (txn_date)

    SUBPARTITION BY LIST (state)

      (PARTITION q1_1999 VALUES LESS THAN (TO_DATE('1-APR-1999','DD-MON-YYYY'))

         (SUBPARTITION q1_1999_northwest VALUES ('OR', 'WA'),

          SUBPARTITION q1_1999_southwest VALUES ('AZ', 'UT', 'NM'),

          SUBPARTITION q1_1999_northeast VALUES ('NY', 'VM', 'NJ'),

          SUBPARTITION q1_1999_southeast VALUES ('FL', 'GA'),

          SUBPARTITION q1_1999_northcentral VALUES ('SD', 'WI'),

          SUBPARTITION q1_1999_southcentral VALUES ('OK', 'TX')

),

       PARTITION q2_1999 VALUES LESS THAN ( TO_DATE('1-JUL-1999','DD-MON-YYYY'))

         (SUBPARTITION q2_1999_northwest VALUES ('OR', 'WA'),

          SUBPARTITION q2_1999_southwest VALUES ('AZ', 'UT', 'NM'),

          SUBPARTITION q2_1999_northeast VALUES ('NY', 'VM', 'NJ'),

          SUBPARTITION q2_1999_southeast VALUES ('FL', 'GA'),

          SUBPARTITION q2_1999_northcentral VALUES ('SD', 'WI'),

          SUBPARTITION q2_1999_southcentral VALUES ('OK', 'TX')

),

       PARTITION q3_1999 VALUES LESS THAN (TO_DATE('1-OCT-1999','DD-MON-YYYY'))

         (SUBPARTITION q3_1999_northwest VALUES ('OR', 'WA'),

          SUBPARTITION q3_1999_southwest VALUES ('AZ', 'UT', 'NM'),

          SUBPARTITION q3_1999_northeast VALUES ('NY', 'VM', 'NJ'),

          SUBPARTITION q3_1999_southeast VALUES ('FL', 'GA'),

          SUBPARTITION q3_1999_northcentral VALUES ('SD', 'WI'),

          SUBPARTITION q3_1999_southcentral VALUES ('OK', 'TX')

),

       PARTITION q4_1999 VALUES LESS THAN ( TO_DATE('1-JAN-2000','DD-MON-YYYY'))

         (SUBPARTITION q4_1999_northwest VALUES ('OR', 'WA'),

          SUBPARTITION q4_1999_southwest VALUES ('AZ', 'UT', 'NM'),

          SUBPARTITION q4_1999_northeast VALUES ('NY', 'VM', 'NJ'),

          SUBPARTITION q4_1999_southeast VALUES ('FL', 'GA'),

          SUBPARTITION q4_1999_northcentral VALUES ('SD', 'WI'),

          SUBPARTITION q4_1999_southcentral VALUES ('OK', 'TX')

);

A row is mapped to a partition by checking whether the value of the partitioning column for a row falls within a specific partition range. The row is then mapped to a subpartition within that partition by identifying the subpartition whose descriptor value list contains a value matching the subpartition column value.

通过检查行的分区列的值是否在特定分区范围来实现将一行映射到分区。然后通过标识其描述符值列表包含与子分区列值匹配的值的子分区，将该行映射到该分区内的子分区。

For example, some sample rows are inserted as follows:

· (10, 4532130, '23-Jan-1999', 8934.10, 'WA') maps to subpartition q1_1999_northwest

· (20, 5671621, '15-May-1999', 49021.21, 'OR') maps to subpartition q2_1999_northwest

· (30, 9977612, '07-Sep-1999', 30987.90, 'FL') maps to subpartition q3_1999_southeast

· (40, 9977612, '29-Nov-1999', 67891.45, 'TX') maps to subpartition q4_1999_southcentral

· (40, 4532130, '5-Jan-2000', 897231.55, 'TX') does not map to any partition in the table and displays an error

· (50, 5671621, '17-Dec-1999', 76123.35, 'CA') does not map to any subpartition in the table and displays an error

The partitions of a range-list partitioned table are logical structures only, because their data is stored in the segments of their subpartitions. The list subpartitions have the same characteristics as list partitions. You can specify a default subpartition, just as you specify a default partition for list partitioning.

范围列表分区表的分区只是逻辑结构，因为它们的数据存储在子分区的段中。 列表子分区与列表分区具有相同的特征。你可以指定默认子分区，就像为列表分区指定默认分区一样。

The following example creates a table that specifies a tablespace at the partition and subpartition levels. The number of subpartitions within each partition varies, and default subpartitions are specified.

下面示例创建一个表，该表指定分区和子分区级别的表空间。 每个分区中的子分区数量有所不同，并且指定了默认子分区。

CREATE TABLE sample_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)

      (PARTITION q1_1999 VALUES LESS THAN (TO_DATE('1-APR-1999','DD-MON-YYYY'))

          TABLESPACE tbs_1

         (SUBPARTITION q1_1999_northwest VALUES ('OR', 'WA'),

          SUBPARTITION q1_1999_southwest VALUES ('AZ', 'UT', 'NM'),

          SUBPARTITION q1_1999_northeast VALUES ('NY', 'VM', 'NJ'),

          SUBPARTITION q1_1999_southeast VALUES ('FL', 'GA'),

          SUBPARTITION q1_others VALUES (DEFAULT) TABLESPACE tbs_4

),

       PARTITION q2_1999 VALUES LESS THAN ( TO_DATE('1-JUL-1999','DD-MON-YYYY'))

          TABLESPACE tbs_2

         (SUBPARTITION q2_1999_northwest VALUES ('OR', 'WA'),

          SUBPARTITION q2_1999_southwest VALUES ('AZ', 'UT', 'NM'),

          SUBPARTITION q2_1999_northeast VALUES ('NY', 'VM', 'NJ'),

          SUBPARTITION q2_1999_southeast VALUES ('FL', 'GA'),

          SUBPARTITION q2_1999_northcentral VALUES ('SD', 'WI'),

          SUBPARTITION q2_1999_southcentral VALUES ('OK', 'TX')

),

       PARTITION q3_1999 VALUES LESS THAN (TO_DATE('1-OCT-1999','DD-MON-YYYY'))

          TABLESPACE tbs_3

         (SUBPARTITION q3_1999_northwest VALUES ('OR', 'WA'),

          SUBPARTITION q3_1999_southwest VALUES ('AZ', 'UT', 'NM'),

          SUBPARTITION q3_others VALUES (DEFAULT) TABLESPACE tbs_4

),

       PARTITION q4_1999 VALUES LESS THAN ( TO_DATE('1-JAN-2000','DD-MON-YYYY'))

          TABLESPACE tbs_4

);

This example results in the following subpartition descriptions:

· All subpartitions inherit their physical attributes, other than tablespace, from tablespace level defaults. This is because the only physical attribute that has been specified for partitions or subpartitions is tablespace. There are no table level physical attributes specified, thus tablespace level defaults are inherited at all levels.

· 所有子分区从表空间级别默认值继承其他物理属性（表空间除外）。这是因为为分区或子分区指定的唯一物理属性是表空间。没有指定表级物理属性，因此表空间级别的默认值在所有级别都是继承的。

· The first 4 subpartitions of partition q1_1999 are all contained in tbs_1, except for the subpartition q1_others, which is stored in tbs_4 and contains all rows that do not map to any of the other partitions.

· 分区q1_1999的前4个子部分都包含在tbs_1中，除了存储在tbs_4中的子分区q1_others，并包含不映射到任何其他分区的所有行。

· The 6 subpartitions of partition q2_1999 are all stored in tbs_2.

· 分区q2_1999的6个子部分都存储在tbs_2中。

· The first 2 subpartitions of partition q3_1999 are all contained in tbs_3, except for the subpartition q3_others, which is stored in tbs_4 and contains all rows that do not map to any of the other partitions.

· 分区q3_1999的前2个子部分都包含在tbs_3中，除了存储在tbs_4中的子分区q3_others，并包含不映射到任何其他分区的所有行。

· There is no subpartition description for partition q4_1999. This results in one default subpartition being created and stored in tbs_4. The subpartition name is system generated in the form SYS_SUBPn.

· 分区q4_1999没有子分区描述。这将导致一个默认子分区被创建并存储在tbs_4中。子分区名称以SYS_SUBPn的形式生成。

更多组合分区表的类型在这里就不详细的一一列出了，详细请查看Oracle 11g官方文档！

作者：SEian.G（苦练七十二变，笑对八十一难）

ITPub博客

Oracle分区表