pgsql 10.1 分区表之 range 分区
来源:互联网 发布:淘宝网资产处置平台 编辑:程序博客网 时间:2024/06/08 08:15
查看数据库版本
select version();PostgreSQL 10.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-18), 64-bit
create table语法
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [ { column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ] | table_constraint | LIKE source_table [ like_option ... ] } [, ... ]] )[ INHERITS ( parent_table [, ... ] ) ][ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ][ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ][ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ][ TABLESPACE tablespace_name ]CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name OF type_name [ ( { column_name [ WITH OPTIONS ] [ column_constraint [ ... ] ] | table_constraint } [, ... ]) ][ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ][ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ][ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ][ TABLESPACE tablespace_name ]CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name PARTITION OF parent_table [ ( { column_name [ WITH OPTIONS ] [ column_constraint [ ... ] ] | table_constraint } [, ... ]) ] { FOR VALUES partition_bound_spec | DEFAULT }[ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ][ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ][ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ][ TABLESPACE tablespace_name ]and partition_bound_spec is:IN ( { numeric_literal | string_literal | NULL } [, ...] ) |FROM ( { numeric_literal | string_literal | MINVALUE | MAXVALUE } [, ...] ) TO ( { numeric_literal | string_literal | MINVALUE | MAXVALUE } [, ...] ) |WITH ( MODULUS numeric_literal, REMAINDER numeric_literal )
rang 分区
创建分区主表 drop table tmp_par_range
create table tmp_par_range ( id int8, random_char varchar(100), day_id varchar(8)) partition by RANGE(day_id);
创建分区从表
When creating a range partition, the lower bound specified with FROM is an inclusive bound, whereas the upper bound specified with TO is an exclusive bound. That is, the values specified in the FROM list are valid values of the corresponding partition key columns for this partition, whereas those in the TO list are not.
Note that this statement must be understood according to the rules of row-wise comparison (Section 9.23.5). For example, given PARTITION BY RANGE (x,y), a partition bound FROM (1, 2) TO (3, 4) allows x=1 with any y>=2, x=2 with any non-null y, and x=3 with any y<4.
– from >=
– to <
CREATE TABLE tmp_par_range_p20171130_b PARTITION OF tmp_par_range FOR VALUES FROM (MINVALUE) TO ('20171130');CREATE TABLE tmp_par_range_p20171201 PARTITION OF tmp_par_range FOR VALUES FROM ('20171130') TO ('20171201');CREATE TABLE tmp_par_range_p20171202 PARTITION OF tmp_par_range FOR VALUES FROM ('20171201') TO ('20171202');CREATE TABLE tmp_par_range_p20171203 PARTITION OF tmp_par_range FOR VALUES FROM ('20171202') TO ('20171203');CREATE TABLE tmp_par_range_p20171203_a PARTITION OF tmp_par_range FOR VALUES FROM ('20171203') TO (MAXVALUE);
创建索引
create index idx_tmp_par_range_p20171130_b_x1 on tmp_par_range_p20171130_b(day_id);create index idx_tmp_par_range_p20171201_x1 on tmp_par_range_p20171201(day_id);create index idx_tmp_par_range_p20171202_x1 on tmp_par_range_p20171202(day_id);create index idx_tmp_par_range_p20171203_x1 on tmp_par_range_p20171203(day_id);create index idx_tmp_par_range_p20171203_a_x1 on tmp_par_range_p20171203_a(day_id);
插入数据
insert into tmp_par_range select *from ( select generate_series(1, 5) as id, md5(random()::text) as info , '20171129' as day_id union all select generate_series(1, 5) as id, md5(random()::text) as info , '20171130' as day_id union all select generate_series(1, 5) as id, md5(random()::text) as info , '20171201' as day_id union all select generate_series(1, 5) as id, md5(random()::text) as info , '20171202' as day_id union all select generate_series(1, 5) as id, md5(random()::text) as info , '20171203' as day_id union all select generate_series(1, 5) as id, md5(random()::text) as info , '20171204' as day_id ) t0;
查看数据
select * from tmp_par_range order by day_id,id ;select * from public.tmp_par_range_p20171130_b;select * from public.tmp_par_range_p20171201;select * from public.tmp_par_range_p20171202;select * from public.tmp_par_range_p20171203;select * from public.tmp_par_range_p20171203_a;
查看执行计划
explainselect * from tmp_par_range tp where 1=1Append (cost=0.00..64.00 rows=1400 width=260) -> Seq Scan on tmp_par_range_p20171130_b tp (cost=0.00..12.80 rows=280 width=260) -> Seq Scan on tmp_par_range_p20171201 tp_1 (cost=0.00..12.80 rows=280 width=260) -> Seq Scan on tmp_par_range_p20171202 tp_2 (cost=0.00..12.80 rows=280 width=260) -> Seq Scan on tmp_par_range_p20171203 tp_3 (cost=0.00..12.80 rows=280 width=260) -> Seq Scan on tmp_par_range_p20171203_a tp_4 (cost=0.00..12.80 rows=280 width=260)explainselect * from tmp_par_range tp where 1=1 and tp.day_id='20171201'Append (cost=0.15..8.17 rows=1 width=260) -> Index Scan using idx_tmp_par_range_p20171202_x1 on tmp_par_range_p20171202 tp (cost=0.15..8.17 rows=1 width=260) Index Cond: ((day_id)::text = '20171201'::text)explainselect * from tmp_par_range tp where 1=1 and tp.day_id in ( '20171201' , '20171202')Append (cost=0.00..27.00 rows=6 width=260) -> Seq Scan on tmp_par_range_p20171202 tp (cost=0.00..13.50 rows=3 width=260) Filter: ((day_id)::text = ANY ('{20171201,20171202}'::text[])) -> Seq Scan on tmp_par_range_p20171203 tp_1 (cost=0.00..13.50 rows=3 width=260) Filter: ((day_id)::text = ANY ('{20171201,20171202}'::text[]))
参考:
https://www.postgresql.org/docs/devel/static/sql-createtable.html
- pgsql 10.1 分区表之 range 分区
- pgsql 10.1 分区表之 list 分区
- oracle range分区表增加分区
- oracle range分区表增加分区
- MySQl分区表小结-------RANGE分区
- Oracle 分区表——Range分区
- Oracle 分区表——Range分区
- Oracle 分区表——Range分区
- 【oracle】分区表:range分区,list分区,hash分区
- MySQL分区之RANGE分区
- MySQL分区之RANGE分区
- MySQL分区之RANGE分区
- MySQL分区之RANGE分区
- mysql分区之range分区
- 分区表及分区索引(2)--创建range分区
- 分区表及分区索引(5)--创建range-hash组合分区
- 分区表及分区索引(6)--创建range-list组合分区
- oracle range分区表已经有了MAXVALUE 分区,如何添加分区
- Android Handler机制
- Bootstrap(四十三)
- Android 自定义Toast
- 【软件工具】服务器硬件资源监控
- 装饰者模式
- pgsql 10.1 分区表之 range 分区
- Complex analysis review 2(Cauchy Integral Theory)
- Oracle数据类型对应Java类型
- ora-02082
- Bootstrap(四十四)
- 欢迎使用CSDN-markdown编辑器
- 使用FluentNHibernate_NHibernate操作数据库
- 《程序员的成长课》:增加收入的 3 大方向
- 实验_Struts2文件的上传,下载