选择分区方法

何时使用范围或间隔分区

范围分区使用在历史数据特别有效。范围分区的边界定义了表或索引的分区顺序。

间隔分区是范围分区的扩展，经常成为间隔范围分区。当数据插入到表中时，如果超过现有的范围分区最高边界，数据库自动创建一个指定的时间间隔分区。

范围或间隔分区通常针对日期时间。因此，SQL语句访问范围分区最好聚集在时间范围上。最好的例子如，从以前的一个时间点查询数据。在这种情况下，如果每一个子分区表代表一个月，那么查询06年9月的数据只需要访问06年9月份的子分区表即可。这样减少了访问所有数据的时间，采用的方法为分区裁剪。

范围分区也适用于定期添加数据和清理旧数据的情况，因为在范围分区表上添加或删除子分区表非常容易。

间隔分区表可以在插入的数据达到过渡点时自动创建间隔分区。间隔分区表可以使用在所有的分区维护操作里。更多的间隔分区操作见“第3章水平分区管理”。

综上所述，得出在以下情况使用范围或间隔分区：

使用范围谓词在分区列（如ORDER_DATE或PURCHASE_DATE等）上频繁扫描大表。使用该列分区表可以有效利用智能裁剪技术。

希望维护滚动窗口数据。

不适用于以下管理员操作，如备份和恢复在一个大表的一个时间范围点上，但是可以基于分区范围列划分它们成为小的逻辑块。

下面的例子创建一个为期两年（2005和2006）的表salestable，以s_salesdate列为分区列使用范围分区将其分为8个季度，每一个对应一个子分区表。通过定义月间隔值让后面的子分区表自动创建。

--  清理环境
DROP TABLE salestable;
DROP TABLESPACE ts1;
DROP TABLESPACE ts2;
DROP TABLESPACE ts3;
DROP TABLESPACE ts4;

--  创建表空间
CREATE TABLESPACE ts1 DATAFILE 'ts1.dt' SIZE 5M AUTOEXTEND ON NEXT 5M;
CREATE TABLESPACE ts2 DATAFILE 'ts2.dt' SIZE 5M AUTOEXTEND ON NEXT 5M;
CREATE TABLESPACE ts3 DATAFILE 'ts3.dt' SIZE 5M AUTOEXTEND ON NEXT 5M;
CREATE TABLESPACE ts4 DATAFILE 'ts4.dt' SIZE 5M AUTOEXTEND ON NEXT 5M;

-- 创建分区表
CREATE TABLE salestable
( 
    s_productid NUMERIC,
    s_saledate DATE,
    s_custid NUMERIC,
    s_totalprice NUMERIC
)
PARTITION BY RANGE(s_saledate)
INTERVAL(INTERVAL '1' MONTH) 
STORE IN (ts1,ts2,ts3,ts4)
( 
    PARTITION sal05q1 VALUES LESS THAN ('2005-APR-01') TABLESPACE ts1,
    PARTITION sal05q2 VALUES LESS THAN ('2005-JUL-01') TABLESPACE ts2,
    PARTITION sal05q3 VALUES LESS THAN ('2005-OCT-01') TABLESPACE ts3,
    PARTITION sal05q4 VALUES LESS THAN ('2006-JAN-01') TABLESPACE ts4,
    PARTITION sal06q1 VALUES LESS THAN ('2006-APR-01') TABLESPACE ts1,
    PARTITION sal06q2 VALUES LESS THAN ('2006-JUL-01') TABLESPACE ts2,
    PARTITION sal06q3 VALUES LESS THAN ('2006-OCT-01') TABLESPACE ts3,
    PARTITION sal06q4 VALUES LESS THAN ('2007-JAN-01') TABLESPACE ts4
);

--删除表和表空间
drop table salestable;
DROP TABLESPACE ts1;
DROP TABLESPACE ts2;
DROP TABLESPACE ts3;
DROP TABLESPACE ts4;

何时使用哈希分区

当表不适用于范围分区与列表分区时，可以使用哈希分区对数据进行水平分区，从而达到数据被平均散列的目的。当表既可以使用范围分区，也可以使用哈希分区时，满足以下条件的表将更加适合使用哈希分区。

用户无法事先确定一个子分区表可能存储的数据量。

各范围分区的容量可能相差很大，或很难通过人工进行平衡。

采用范围分区可能导致数据不正常的集中。

应用系统对并行 DML，分区裁剪，及基于分区的智能联接等与性能有关的分区特性要求较高。

以下示例使用哈希分区方法分区表sales_hash，列s_productid作为分区键。并行联接产品表更有利于部分或全部分区智能联接。通过分区裁剪访问单个产品或产品列表更加有效。

--  清理环境
DROP TABLE sales_hash;
DROP TABLESPACE ts1;
DROP TABLESPACE ts2;
DROP TABLESPACE ts3;
DROP TABLESPACE ts4;

--  创建表空间
CREATE TABLESPACE ts1 DATAFILE 'ts1.dt' SIZE 5M AUTOEXTEND ON NEXT 5M;
CREATE TABLESPACE ts2 DATAFILE 'ts2.dt' SIZE 5M AUTOEXTEND ON NEXT 5M;
CREATE TABLESPACE ts3 DATAFILE 'ts3.dt' SIZE 5M AUTOEXTEND ON NEXT 5M;
CREATE TABLESPACE ts4 DATAFILE 'ts4.dt' SIZE 5M AUTOEXTEND ON NEXT 5M;

-- 创建分区表
CREATE TABLE sales_hash
( 
    s_productid NUMERIC,
    s_saledate DATE,
    s_custid NUMERIC,
    s_totalprice NUMERIC
)
PARTITION BY HASH(s_productid)
( 
    PARTITION p1 TABLESPACE ts1,
    PARTITION p2 TABLESPACE ts2,
    PARTITION p3 TABLESPACE ts3,
    PARTITION p4 TABLESPACE ts4
);

--删除表和表空间
drop table sales_hash;
DROP TABLESPACE ts1;
DROP TABLESPACE ts2;
DROP TABLESPACE ts3;
DROP TABLESPACE ts4;

如果没有指定子分区表名称，而只是指定子分区表数目，那么数据库会自动为子分区表命名。同时可以使用STORE IN子句指定哈希子分区表存储在指定表空间。

何时使用列表分区

列表分区适合于，将无序或互不相关的数据进行分组整理。例如，列表集合中的每一个集合包含三个地名，当分区键属于这个集合时，就会被散列到这个分区中。当用户想要把无序或不相关的数据进行分区时，列表分区将是非常好的选择。

例如，Oregon和Washington地区的客户存储在一个子分区表，其他地区的客户存储在其他不同的分区。账户管理员可以利用分区裁剪分析他们的账户信息。

--  清理环境
DROP TABLE accounts;
DROP TABLESPACE ts1;
DROP TABLESPACE ts2;
DROP TABLESPACE ts3;
DROP TABLESPACE ts4;

--  创建表空间
CREATE TABLESPACE ts1 DATAFILE 'ts1.dt' SIZE 5M AUTOEXTEND ON NEXT 5M;
CREATE TABLESPACE ts2 DATAFILE 'ts2.dt' SIZE 5M AUTOEXTEND ON NEXT 5M;
CREATE TABLESPACE ts3 DATAFILE 'ts3.dt' SIZE 5M AUTOEXTEND ON NEXT 5M;
CREATE TABLESPACE ts4 DATAFILE 'ts4.dt' SIZE 5M AUTOEXTEND ON NEXT 5M;

-- 创建分区表
CREATE TABLE accounts
( 
    id NUMERIC,
    account_number NUMERIC,
    customer_id NUMERIC,
    branch_id NUMERIC,
    region VARCHAR(2),
    status VARCHAR(1)
)
PARTITION BY LIST (region)
( 
    PARTITION p_northwest VALUES ('OR', 'WA'),
    PARTITION p_southwest VALUES ('AZ', 'UT', 'NM'),
    PARTITION p_northeast VALUES ('NY', 'VM', 'NJ'),
    PARTITION p_southeast VALUES ('FL', 'GA'),
    PARTITION p_northcentral VALUES ('SD', 'WI'),
    PARTITION p_southcentral VALUES ('OK', 'TX')
);

--删除表和表空间
drop table accounts;
DROP TABLESPACE ts1;
DROP TABLESPACE ts2;
DROP TABLESPACE ts3;
DROP TABLESPACE ts4;

与范围和哈希分区不一样，列表分区不支持多列键值。如果一个表使用列表分区方法，那么分区键只能是表中的单个列。

何时使用间隔分区

间隔分区可以使用在范围分区表及表使用固定间隔值增加新的分区表。当数据插入到表中时，如果超过现有的范围分区表的最高边界，数据库自动创建一个子分区表，存储新的数据。

使用间隔分区的方便之处在于你不用显示创建范围分区表，你只需考虑使用间隔分区方法即可，除非你创建的范围分区表使用的是不同的间隔值或者在创建范围值时指定特定的分区属性。你可以在间隔分区定义时指定一系列的表空间，数据库会使用循环方式将每一个间隔分区分配到表空间。

如果你要升级你的应用程序及使用范围分区表或范围-复合分区表，那么你可以很容易将现有的分区表转为使用间隔分区的表。注意，不能手动添加一个分区表到间隔分区表。如果你使用了自动创建分区表，那么你必须更改程序代码里显示创建范围表的语句。

下面的实例说明如何改变范围分区表sales为一个开始使用间隔值为月的间隔分区表。

ALTER TABLE sales SET INTERVAL (INTERVAL '1' MONTH);

何时使用复合分区

复合分区便于用户进行与时间相关的维护操作，例如添加新的范围子分区表等。同时复合分区还能够利用子分区表实现高度的并行 DML 操作，并对数据分布进行精细的控制。

进一步的分区可以实现进一步的数据划分，如：复合范围-哈希分区既能够发挥范围分区的可管理性优势，也能够发挥哈希分区的数据分布，条带化，及并行化优势；复合范围-列表分区能够发挥范围分区的可管理性优势，也能利用列表分区的显示控制能力。

使用范围-哈希复合分区

范围-哈希复合分区表通常用于存储大量历史数据和频繁同其他大表做联接操作。对于这类型的表，范围-哈希复合分区在范围分区层使用分区裁剪，在哈希分区层则使用并行全部或部分分区智能联接。

范围-哈希复合分区可以使用在哈希分区使用的地方。随着时间的推移，数据可能要从一个存储层移动到另一个存储层，或者压缩，或者存储在只读表空间，甚至清除。信息生命管理（Infromation Lifecycle Management,ILM）通常使用范围分区实现层次存储方法。

以下例子是一个范围-哈希复合分区因特网服务供应商表page_history。该表用于历史分析来指定client_ip值或分析经过的IP地址。

--  清理环境
DROP TABLE page_history;

-- 创建分区表
CREATE TABLE page_history
( 
    id NUMERIC NOT NULL,
    url VARCHAR(300) NOT NULL,
    view_date DATE NOT NULL,
    client_ip VARCHAR(23) NOT NULL,
    from_url VARCHAR(300),
    to_url VARCHAR(300),
    timing_in_seconds NUMERIC
) 
PARTITION BY RANGE(view_date) 
INTERVAL (INTERVAL '1' DAY)
SUBPARTITION BY HASH(client_ip)
SUBPARTITIONS 32
( 
    PARTITION p0 VALUES LESS THAN ('2006-JAN-01')
);

--删除表和表空间
drop table page_history;

何时使用范围-列表复合分区

范围-列表复合分区通常使用在存储历史数据的大表及访问多方面的信息。通常历史数据视图是一个访问路径，但是某些商业案例还为访问路径分类。例如，地区账户管理员感兴趣的是在某个期间里他们区域有多少新客户登记。

下面的例子创建一个范围-列表复合分区表call_detail_records，随着时间的推移，电信公司可以使用这个表分析指定类型的电话。该表在from_number和to_number列上使用局部索引。

--  清理环境
DROP TABLE call_detail_records;
DROP TABLESPACE ts1;
DROP TABLESPACE ts2;
DROP TABLESPACE ts3;
DROP TABLESPACE ts4;

--  创建表空间
CREATE TABLESPACE ts1 DATAFILE 'ts1.dt' SIZE 5M AUTOEXTEND ON NEXT 5M;
CREATE TABLESPACE ts2 DATAFILE 'ts2.dt' SIZE 5M AUTOEXTEND ON NEXT 5M;
CREATE TABLESPACE ts3 DATAFILE 'ts3.dt' SIZE 5M AUTOEXTEND ON NEXT 5M;
CREATE TABLESPACE ts4 DATAFILE 'ts4.dt' SIZE 5M AUTOEXTEND ON NEXT 5M;

-- 创建分区表
CREATE TABLE call_detail_records
( 
    id NUMERIC,
    from_number VARCHAR(20),
    to_number VARCHAR(20),
    date_of_call DATE,
    distance VARCHAR(1),
    call_duration_in_s NUMERIC(4)
) 
PARTITION BY RANGE(date_of_call)
INTERVAL (INTERVAL '1' DAY)
SUBPARTITION BY LIST(distance)
SUBPARTITION TEMPLATE
( 
    SUBPARTITION local VALUES('L') TABLESPACE ts1,
    SUBPARTITION medium_long VALUES ('M') TABLESPACE ts2,
    SUBPARTITION long_distance VALUES ('D') TABLESPACE ts3,
    SUBPARTITION international VALUES ('I') TABLESPACE ts4
)
(
    PARTITION p0 VALUES LESS THAN ('2005-JAN-01')
);

CREATE INDEX from_number_ix ON call_detail_records(from_number) LOCAL ;
CREATE INDEX to_number_ix ON call_detail_records(to_number) LOCAL ;

--删除表和表空间
drop table call_detail_records;
DROP TABLESPACE ts1;
DROP TABLESPACE ts2;
DROP TABLESPACE ts3;
DROP TABLESPACE ts4;

何时使用范围-范围复合分区

范围-范围复合分区适合使用在依赖于多方面时间的应用。通常应用不会单纯使用一个方面时间访问数据，而是使用多方面时间访问数据。例如，一个网络零售商想基于何时下订单及何时装货分析他的销售数据。

以下例子指出范围-范围复合分区表account_balance_history的用法。一个银行可能通过使用存取来了解它的客户的最低余额或指定相关主要客户类型。

--  清理环境
DROP TABLE account_balance_history;
DROP TABLESPACE ts1;
DROP TABLESPACE ts2;
DROP TABLESPACE ts3;
DROP TABLESPACE ts4;

--  创建表空间
CREATE TABLESPACE ts1 DATAFILE 'ts1.dt' SIZE 5M AUTOEXTEND ON NEXT 5M;
CREATE TABLESPACE ts2 DATAFILE 'ts2.dt' SIZE 5M AUTOEXTEND ON NEXT 5M;
CREATE TABLESPACE ts3 DATAFILE 'ts3.dt' SIZE 5M AUTOEXTEND ON NEXT 5M;
CREATE TABLESPACE ts4 DATAFILE 'ts4.dt' SIZE 5M AUTOEXTEND ON NEXT 5M;

-- 创建分区表
CREATE TABLE account_balance_history
( 
    id NUMERIC NOT NULL,
    account_number NUMERIC NOT NULL,
    customer_id NUMERIC NOT NULL,
    transaction_date DATE NOT NULL,
    amount_credited NUMERIC,
    amount_debited NUMERIC,
    end_of_day_balance NUMERIC NOT NULL
) 
PARTITION BY RANGE(transaction_date)
INTERVAL (INTERVAL '7' DAY)
SUBPARTITION BY RANGE(end_of_day_balance)
SUBPARTITION TEMPLATE
( 
    SUBPARTITION unacceptable VALUES LESS THAN (-1000),
    SUBPARTITION credit VALUES LESS THAN (0),
    SUBPARTITION low VALUES LESS THAN (500),
    SUBPARTITION normal VALUES LESS THAN (5000),
    SUBPARTITION high VALUES LESS THAN (20000)
)
(
    PARTITION p0 VALUES LESS THAN ('2007-JAN-01')
);

--删除表和表空间
drop table account_balance_history;
DROP TABLESPACE ts1;
DROP TABLESPACE ts2;
DROP TABLESPACE ts3;
DROP TABLESPACE ts4;

何时使用列表-哈希复合分区

列表-哈希复合分区适用于一方面访问大表，但是（由于自身的大小）在另一个方面用到并行全部或部分分区智能联接到其它的大表的方法。

实例如下，列表-哈希复合分区表credit_card_account。该表在region上采用列表分区让账户管理者能够快速访问他们区域的账户。在customer_id上采用哈希分区策略为了能够快速查询该业务表，也能够采用全部分区智能联接。该表在is_active列上有局部索引。

--  清理环境
DROP TABLE credit_card_accounts;

-- 创建分区表
CREATE TABLE credit_card_accounts
( 
    account_number NUMERIC(16) NOT NULL,
    customer_id NUMERIC NOT NULL,
    customer_region VARCHAR(2) NOT NULL,
    is_active VARCHAR(1) NOT NULL,
    date_opened DATE NOT NULL
) 
PARTITION BY LIST (customer_region)
SUBPARTITION BY HASH (customer_id)
SUBPARTITIONS 16
( 
    PARTITION emea VALUES ('EU','ME','AF'),
    PARTITION amer VALUES ('NA','LA'),
    PARTITION apac VALUES ('SA','AU','NZ','IN','CH')
);

CREATE INDEX is_active_bix ON credit_card_accounts(is_active) LOCAL;

--删除表和表空间
drop table credit_card_accounts;

何时使用列表-列表复合分区

列表-列表复合分区适用于访问在不同范围的大表。在这些范围上可以基于不连续的值将行映射到不同的分区。

实例如下，列表-列表复合分区表current_inventory。该表经常更新超市供应商本地仓库的当前货物清单。存在容易腐坏的食物从仓库供应到超市，所以优化供应及交付就显得特别重要。该表在warehouse_id和product_id上存在局部索引。

--  清理环境
DROP TABLE current_inventory;

-- 创建分区表
CREATE TABLE current_inventory
( 
    warehouse_id NUMERIC,
    warehouse_region VARCHAR(2),
    product_id NUMERIC,
    product_category VARCHAR(12),
    amount_in_stock NUMERIC,
    unit_of_shipping VARCHAR(20),
    products_per_unit NUMERIC,
    last_updated DATE
) 
PARTITION BY LIST (warehouse_region)
SUBPARTITION BY LIST (product_category)
SUBPARTITION TEMPLATE
( 
    SUBPARTITION perishable VALUES ('DAIRY','PRODUCE','MEAT','BREAD'),
    SUBPARTITION non_perishable VALUES ('CANNED','PACKAGED'),
    SUBPARTITION durable VALUES ('TOYS','KITCHENWARE')
)
( 
    PARTITION p_northwest VALUES ('OR', 'WA'),
    PARTITION p_southwest VALUES ('AZ', 'UT', 'NM'),
    PARTITION p_northeast VALUES ('NY', 'VM', 'NJ'),
    PARTITION p_southeast VALUES ('FL', 'GA'),
    PARTITION p_northcentral VALUES ('SD', 'WI'),
    PARTITION p_southcentral VALUES ('OK', 'TX')
);

CREATE INDEX warehouse_id_ix ON current_inventory(warehouse_id) LOCAL;
CREATE INDEX product_id_ix ON current_inventory(product_id) LOCAL;

--删除表和表空间
drop table current_inventory;

何时使用列表-范围复合分区

列表-范围复合分区适用于访问在不同范围的大表。对于常用的范围，你可以根据不连续的值映射行到分区表。列表-范围复合分区通常是对在列表分区中使用范围值，而范围-列表复合分区是在范围分区中使用不连续的列表值。列表-范围极少用于存储历史数据，即使它功效等同于这种情况。范围-列表复合分区可以用间隔-列表复合分区代替，但是列表-范围复合分区不支持interval分区。

以下例子说明donations表存储不同捐款货币。捐款根据它的总额将其分为低、中和高。根据货币的不同，范围是不同的。

--  清理环境
DROP TABLE donations;

-- 创建分区表
CREATE TABLE donations
( 
    id NUMERIC,
    name VARCHAR(60),
    beneficiary VARCHAR(80),
    payment_method VARCHAR(30),
    currency VARCHAR(3),
    amount NUMERIC
) 
PARTITION BY LIST (currency)
SUBPARTITION BY RANGE (amount)
( 
    PARTITION p_eur VALUES ('EUR')
    ( 
        SUBPARTITION p_eur_small VALUES LESS THAN (8),
        SUBPARTITION p_eur_medium VALUES LESS THAN (80),
        SUBPARTITION p_eur_high VALUES LESS THAN (MAXVALUE)
    ),
    PARTITION p_gbp VALUES ('GBP')
    ( 
        SUBPARTITION p_gbp_small VALUES LESS THAN (5),
        SUBPARTITION p_gbp_medium VALUES LESS THAN (50),
        SUBPARTITION p_gbp_high VALUES LESS THAN (MAXVALUE)
    ),
    PARTITION p_aud_nzd_chf VALUES ('AUD','NZD','CHF')
    ( 
        SUBPARTITION p_aud_nzd_chf_small VALUES LESS THAN (12),
        SUBPARTITION p_aud_nzd_chf_medium VALUES LESS THAN (120),
        SUBPARTITION p_aud_nzd_chf_high VALUES LESS THAN (MAXVALUE)
    ),
    PARTITION p_jpy VALUES ('JPY')
    ( 
        SUBPARTITION p_jpy_small VALUES LESS THAN (1200),
        SUBPARTITION p_jpy_medium VALUES LESS THAN (12000),
        SUBPARTITION p_jpy_high VALUES LESS THAN (MAXVALUE)
    ),
    PARTITION p_inr VALUES ('INR')
    ( 
        SUBPARTITION p_inr_small VALUES LESS THAN (400),
        SUBPARTITION p_inr_medium VALUES LESS THAN (4000),
        SUBPARTITION p_inr_high VALUES LESS THAN (MAXVALUE)
    ),
    PARTITION p_zar VALUES ('ZAR')
    ( 
        SUBPARTITION p_zar_small VALUES LESS THAN (70),
        SUBPARTITION p_zar_medium VALUES LESS THAN (700),
        SUBPARTITION p_zar_high VALUES LESS THAN (MAXVALUE)
    ),
    PARTITION p_default VALUES (DEFAULT)
    ( 
        SUBPARTITION p_default_small VALUES LESS THAN (10),
        SUBPARTITION p_default_medium VALUES LESS THAN (100),
        SUBPARTITION p_default_high VALUES LESS THAN (MAXVALUE)
    )
);

--删除表和表空间
drop table donations;