1 分区表
分区表的优点：
- 改善了查询性能
- 增加了可用性
- 维护方便
- 均衡 I/O
1.1 范围分区表
创建范围分区表
create table "TEST"."FX_01"
(
"ID" INT,
"NAME" VARCHAR(20)
)
PARTITION BY RANGE ("ID")
(
PARTITION "P1" VALUES LESS THAN (1000),
PARTITION "P2" VALUES LESS THAN (2000),
PARTITION "P3" VALUES LESS THAN (3000),
PARTITION "PM" VALUES LESS THAN (MAXVALUE)
)
storage(initial 1, next 1, minextents 1, fillfactor 0)
;
插入数据
BEGIN
FOR I IN 1..100000 LOOP
INSERT INTO TEST.FX_01 VALUES(I, 'AAAAAAA'||I);
END LOOP;
COMMIT;
END;
1.2 列表分区表
创建列表分区表
create table "TEST"."FX_LIST_01"
(
"ID" INT,
"NAME" VARCHAR(20),
"SALE_DATE" DATE,
"CITY" VARCHAR(20)
)
PARTITION BY LIST ("CITY")
(
PARTITION "P1" VALUES ('北京', '天津','哈尔滨','青岛'),
PARTITION "P2" VALUES ('上海','南京','杭州'),
PARTITION "P3" VALUES ('武汉','长沙','合肥'),
PARTITION "P4" VALUES ('广州','深圳','福建'),
PARTITION "PD" VALUES (DEFAULT)
)
storage(initial 1, next 1, minextents 1, fillfactor 0)
;
插入数据
INSERT INTO TEST.FX_LIST_01 values(1,'AAAA','2022-04-18','北京');
INSERT INTO TEST.FX_LIST_01 values(1,'BBBB','2022-04-18',' 天津 ');
INSERT INTO TEST.FX_LIST_01 values(1,'CCCC','2022-04-18','天津');
INSERT INTO TEST.FX_LIST_01 values(1,'DDDD','2022-04-18','上海');
INSERT INTO TEST.FX_LIST_01 values(1,'EEEE','2022-04-18','武汉');
INSERT INTO TEST.FX_LIST_01 values(1,'EEEE','2022-04-18','广州');
INSERT INTO TEST.FX_LIST_01 values(1,'EEEE','2022-04-18','海口');
COMMIT;
1.3 哈希分区表
CREATE TABLE "TEST"."FX_HASH_01"
(
"ID" INT,
"NAME" VARCHAR(20))
PARTITION BY HASH("ID")
(
PARTITION "PART_1",
PARTITION "PART_2",
PARTITION "PART_3"
) STORAGE(ON "MAIN", CLUSTERBTR) ;
BEGIN
FOR I IN 1..100000 LOOP
INSERT INTO TEST.FX_HASH_01 VALUES(I, 'AAAAAAA'||I);
END LOOP;
COMMIT;
END;
create table TEST.FX_HASH_02(id int, name varchar(20)) partition by hash (id) partitions 10;
BEGIN
FOR I IN 1..100000 LOOP
INSERT INTO TEST.FX_HASH_02 VALUES(I, 'AAAAAAA'||I);
END LOOP;
COMMIT;
END;
1.4 组合分区表
create table "TEST"."FX_COMPOSE_01"
(
"ID" INT,
"NAME" VARCHAR(20),
"SALE_DATE" DATE,
"CITY" VARCHAR(20)
)
PARTITION BY LIST ("CITY")
SUBPARTITION BY RANGE ("SALE_DATE") SUBPARTITION TEMPLATE
(
SUBPARTITION "PT01" VALUES LESS THAN ('2012-08-01'),
SUBPARTITION "PT02" VALUES LESS THAN ('2013-08-01'),
SUBPARTITION "PT03" VALUES LESS THAN (MAXVALUE)
)
(
PARTITION "P01" VALUES ('北京', '天津','哈尔滨','青岛')
(
SUBPARTITION "P_S_01" VALUES LESS THAN ('2012-04-01'),
SUBPARTITION "P_S_02" VALUES LESS THAN ('2013-04-01'),
SUBPARTITION "P_SM" VALUES LESS THAN (MAXVALUE)
),
PARTITION "P02" VALUES ('上海','南京','杭州'),
PARTITION "P03" VALUES ('武汉','长沙','合肥'),
PARTITION "P04" VALUES ('广州','深圳','福建'),
PARTITION "PD" VALUES (DEFAULT)
)
storage(initial 1, next 1, minextents 1, fillfactor 0)
;
insert into TEST.FX_COMPOSE_01 values(1,'AAAA','2012-05-01','北京');
insert into TEST.FX_COMPOSE_01 values(1,'AAAA','2012-05-01','上海');
insert into TEST.FX_COMPOSE_01 values(1,'AAAA','2012-05-01','武汉');
insert into TEST.FX_COMPOSE_01 values(1,'AAAA','2012-05-01','广州');
insert into TEST.FX_COMPOSE_01 values(1,'AAAA','2012-05-01','海口');
insert into TEST.FX_COMPOSE_01 values(1,'AAAA','2013-05-01','北京');
commit;
1.5 间隔分区表
间隔分区表是范围分区表的一种，可以根据间隔自动生成分区子表，减低维护成本。
create table "TEST"."FX_INTERVAL_01"
(
"ID" INT,
"NAME" VARCHAR(20),
"SALE_DATE" DATE
)
PARTITION BY RANGE ("ID") interval(1000)
(
PARTITION "P1" VALUES LESS THAN (1000)
)
storage(initial 1, next 1, minextents 1, fillfactor 0)
;
INSERT INTO TEST.FX_INTERVAL_01 VALUES(100, 'AAAAA100', '2022-06-25');
INSERT INTO TEST.FX_INTERVAL_01 VALUES(1100, 'AAAAA1100', '2022-06-25');
INSERT INTO TEST.FX_INTERVAL_01 VALUES(2100, 'AAAAA2100', '2022-06-25');
INSERT INTO TEST.FX_INTERVAL_01 VALUES(3100, 'AAAAA3100', '2022-06-25');
commit;
create table "TEST"."FX_INTERVAL_02"
(
"ID" INT,
"NAME" VARCHAR(20),
"SALE_DATE" DATE
)
PARTITION BY RANGE ("SALE_DATE") interval(numtoyminterval(2,'YEAR'))
(
PARTITION "P1" VALUES LESS THAN ('2012-01-01')
)
storage(initial 1, next 1, minextents 1, fillfactor 0)
;
INSERT INTO TEST.FX_INTERVAL_02 VALUES(100, 'AAAAA100', '2010-06-25');
INSERT INTO TEST.FX_INTERVAL_02 VALUES(100, 'AAAAA100', '2011-06-25');
INSERT INTO TEST.FX_INTERVAL_02 VALUES(1100, 'AAAAA1100', '2012-06-25');
INSERT INTO TEST.FX_INTERVAL_02 VALUES(2100, 'AAAAA2100', '2013-06-25');
INSERT INTO TEST.FX_INTERVAL_02 VALUES(3100, 'AAAAA3100', '2014-06-25');
INSERT INTO TEST.FX_INTERVAL_02 VALUES(3100, 'AAAAA3100', '2015-06-25');
INSERT INTO TEST.FX_INTERVAL_02 VALUES(3100, 'AAAAA3100', '2016-06-25');
commit;
1.6 分区表维护
1.6.1 查询
1）查询分区信息
SELECT * FROM dba_tab_partitions WHERE table_owner='TEST';
- 查询分区数据
--通过分区表查询
SELECT COUNT(*) FROM TEST.FX_INTERVAL_02;
--通过分区子表名称查询，只能查到该子表的数据，只是分区表的一部分数据
SELECT COUNT(*) FROM TEST.FX_INTERVAL_02_P1;
1.6.2 增加分区
ALTER TABLE TEST.FX_01 add partition PM values less than(MAXVALUE);
1.6.3 删除分区
ALTER TABLE TEST.FX_01 DROP partition P1;
1.6.4 修改分区名
ALTER TABLE TEST.FX_INTERVAL_02 RENAME PARTITION SYS_P1086_1088 TO P2;
1.6.5 合并分区
ALTER TABLE TEST.FX_01 MERGE PARTITIONS P2,P3 INTO PARTITION P2_3;
1.6.6 拆分分区
ALTER TABLE TEST.FX_01 SPLIT PARTITION P2_3 AT(2000) INTO (PARTITION P2, PARTITION P3);
1.6.7 交换分区
主要用于交换普通表和分区子表的数据。需注意交换时，不检查普通表的数据合法性，若数据不合法，交换后通过分区子表查询时可以查询到，但是通过父表查询时不能查询到不合法数据，count记录时包含不合法记录数。
ALTER TABLE TEST.FX_01 EXCHANGE PARTITION P1 WITH TABLE TEST.T_01;
