示例 – 清空子分区
下列示例从表sales的子分区中删除了数据。使用下列命令来创建表sales:
CREATE TABLE sales
(
dept_no number,
part_no varchar2,
country varchar2(20),
date date,
amount number
)
PARTITION BY RANGE(date) SUBPARTITION BY LIST (country)
(
PARTITION "2011" VALUES LESS THAN('01-JAN-2012')
(
SUBPARTITION europe_2011 VALUES ('ITALY', 'FRANCE'),
SUBPARTITION asia_2011 VALUES ('PAKISTAN', 'INDIA'),
SUBPARTITION americas_2011 VALUES ('US', 'CANADA')
),
PARTITION "2012" VALUES LESS THAN('01-JAN-2013')
(
SUBPARTITION europe_2012 VALUES ('ITALY', 'FRANCE'),
SUBPARTITION asia_2012 VALUES ('PAKISTAN', 'INDIA'),
SUBPARTITION americas_2012 VALUES ('US', 'CANADA')
),
PARTITION "2013" VALUES LESS THAN('01-JAN-2015')
(
SUBPARTITION europe_2013 VALUES ('ITALY', 'FRANCE'),
SUBPARTITION asia_2013 VALUES ('PAKISTAN', 'INDIA'),
SUBPARTITION americas_2013 VALUES ('US', 'CANADA')
)
);
使用下列命令填充表sales:
INSERT INTO sales VALUES
(10, '4519b', 'FRANCE', '17-Jan-2011', '45000'),
(20, '3788a', 'INDIA', '01-Mar-2012', '75000'),
(40, '9519b', 'US', '12-Apr-2012', '145000'),
(20, '3788a', 'PAKISTAN', '04-Jun-2012', '37500'),
(40, '4577b', 'US', '11-Nov-2012', '25000'),
(30, '7588b', 'CANADA', '14-Dec-2011', '50000'),
(30, '4519b', 'CANADA', '08-Apr-2012', '120000'),
(40, '3788a', 'US', '12-May-2011', '4950'),
(20, '3788a', 'US', '04-Apr-2012', '37500'),
(40, '4577b', 'INDIA', '11-Jun-2011', '25000'),
(10, '9519b', 'ITALY', '07-Jul-2012', '15000'),
(20, '4519b', 'INDIA', '2-Dec-2012', '5090');
查询表sales显示了记录已被分配在子分区之中:
acctg=# SELECT tableoid::regclass, * FROM sales;
tableoid | dept_no| part_no| country | date |amount
--------------------+--------+--------+----------+-------------------+-------
sales_2011_europe | 10| 4519b | FRANCE | 17-JAN-11 00:00:00| 45000
sales_2011_asia | 40| 4577b | INDIA | 11-JUN-11 00:00:00| 25000
sales_2011_americas| 30| 7588b | CANADA | 14-DEC-11 00:00:00| 50000
sales_2011_americas| 40| 3788a | US | 12-MAY-11 00:00:00| 4950
sales_2012_europe | 10| 9519b | ITALY | 07-JUL-12 00:00:00| 15000
sales_2012_asia | 20| 3788a | INDIA | 01-MAR-12 00:00:00| 75000
sales_2012_asia | 20| 3788a | PAKISTAN | 04-JUN-12 00:00:00| 37500
sales_2012_asia | 20| 4519b | INDIA | 02-DEC-12 00:00:00| 5090
sales_2012_americas| 40| 9519b | US | 12-APR-12 00:00:00| 145000
sales_2012_americas| 40| 4577b | US | 11-NOV-12 00:00:00| 25000
sales_2012_americas| 30| 4519b | CANADA | 08-APR-12 00:00:00| 120000
sales_2012_americas| 20| 3788a | US | 04-APR-12 00:00:00| 37500
(12 rows)
要删除分区2012_americas的内容,先要调用下列命令:
ALTER TABLE sales TRUNCATE SUBPARTITION "americas_2 012";
现在,查询表sales显示了分区americas_2012的内容已被删除:
acctg=# SELECT tableoid::regclass, * FROM sales;
tableoid | dept_no|part_no| country | date | amount
--------------------+--------+-------+----------+--------------------+-------
sales_2011_europe | 10| 4519b | FRANCE | 17-JAN-11 00:00:00 | 45000
sales_2011_asia | 40| 4577b | INDIA | 11-JUN-11 00:00:00 | 25000
sales_2011_americas| 30| 7588b | CANADA | 14-DEC-11 00:00:00 | 50000
sales_2011_americas| 40| 3788a | US | 12-MAY-11 00:00:00 | 4950
sales_2012_europe | 10| 9519b | ITALY | 07-JUL-12 00:00:00 | 15000
sales_2012_asia | 20| 3788a | INDIA | 01-MAR-12 00:00:00 | 75000
sales_2012_asia | 20| 3788a | PAKISTAN | 04-JUN-12 00:00:00 | 37500
sales_2012_asia | 20| 4519b | INDIA | 02-DEC-12 00:00:00 | 5090
(8 rows)
当删除记录时,分区2012_americas的结构仍然是完整的:
acctg=# SELECT subpartition_name, high_value FROM ALL_TAB_SUBPARTITIONS;
subpartition_name | high_value
-------------------+---------------------
2013_europe | 'ITALY', 'FRANCE'
2012_europe | 'ITALY', 'FRANCE'
2011_europe | 'ITALY', 'FRANCE'
2013_asia | 'PAKISTAN', 'INDIA'
2012_asia | 'PAKISTAN', 'INDIA'
2011_asia | 'PAKISTAN', 'INDIA'
2013_americas | 'US', 'CANADA'
2012_americas | 'US', 'CANADA'
2011_americas | 'US', 'CANADA'
(9
rows)