pg 分区表唯一联合索引分区表和索引

转载

mob64ca1410eb61 2024-04-02 12:39:24

文章标签 pg 分区表唯一联合索引 Oracle 数据 List 表空间 文章分类 数据仓库大数据

　什么时候使用分区：
　　1、大数据量的表，比如大于2GB。一方面2GB文件对于32位os是一个上限，另外备份时间长。
　　2、包括历史数据的表，比如最新的数据放入到最新的分区中。典型的例子：历史表，只有当前月份的数据可以被修改，而其他月份只能read-only
　　ORACLE只支持以下分区：tables, indexes on tables, materialized views, and indexes on materialized views
　　分区对SQL和DML是透明的(应用程序不必知道已经作了分区)，但是DDL可以对不同的分区进行管理。
　　不同的分区之间必须有相同的逻辑属性，比如共同的表名，列名，数据类型，约束;
　　但是可以有不同的物理属性，比如pctfree, pctused, and tablespaces.
　　分区独立性：即使某些分区不可用，其他分区仍然可用。
　　最多可以分成64000个分区，但是具有LONG or LONG RAW列的表不可以，但是有CLOB or BLOB列的表可以。
　　可以不用to_date函数，比如：
　　

alter session set nls_date_format='mm/dd/yyyy'; 

　　CREATE TABLE sales_range 

　　(salesman_id NUMBER(5), 

　　salesman_name VARCHAR2(30), 

　　sales_amount NUMBER(10), 

　　sales_date DATE) 

　　PARTITION BY RANGE(sales_date) 

　　( 

　　PARTITION sales_jan2000 VALUES LESS THAN('02/01/2000'), 

　　PARTITION sales_feb2000 VALUES LESS THAN('03/01/2000'), 

　　PARTITION sales_mar2000 VALUES LESS THAN('04/01/2000'), 

　　PARTITION sales_apr2000 VALUES LESS THAN('05/01/2000') 

　　);

　　Partition Key：最多16个columns,可以是nullable的
　　非分区的表可以有分区或者非分区的索引;
　　分区表可以有分区或者非分区的索引;
　　Partitioning 方法：
　

Range Partitioning 

　　List Partitioning 

　　Hash Partitioning 

　　Composite Partitioning

　　Composite Partitioning:组合，以及 range-hash and range-list composite partitioning
　　Range Partitioning：
　　每个分区都有VALUES LESS THAN子句，表示这个分区小于(<)某个上限，而大于等于(>=)前一个分区的VALUES LESS THAN值。
　　MAXVALUE定义最高的分区，他表示一个虚拟的无限大的值。这个分区包括null值。
　　

CREATE TABLE sales_range 

　　(salesman_id NUMBER(5), 

　　salesman_name VARCHAR2(30), 

　　sales_amount NUMBER(10), 

　　sales_date DATE) 

　　PARTITION BY RANGE(sales_date) 

　　( 

　　PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('01/02/2000','DD/MM/YYYY')), 

　　PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('01/03/2000','DD/MM/YYYY')), 

　　PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('01/04/2000','DD/MM/YYYY')), 

　　PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('01/05/2000','DD/MM/YYYY')), 

　　PARTITION sales_2000 VALUES LESS THAN(MAXVALUE) 

　　);

　　插入数据：
　

Insert into sales_range values(1,2,3,to_date('21-04-2000','DD-MM-YYYY')); 

　　Insert into sales_range values(1,2,3,sysdate);

　　选择数据：
　

select * from sales_range; 

　　select * from sales_range partition(sales_apr2000); 

　　select * from sales_range partition(sales_mar2000); 

　　select * from sales_range partition(sales_2000);

　　按照多个列分区：
　　

CREATE TABLE sales_range1 

　　(salesman_id NUMBER(5), 

　　salesman_name VARCHAR2(30), 

　　sales_amount NUMBER(10), 

　　sales_date DATE) 

　　PARTITION BY RANGE(sales_date, sales_amount) 

　　( 

　　PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('01/02/2000','DD/MM/YYYY'),1000), 

　　PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('01/03/2000','DD/MM/YYYY'),2000), 

　　PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('01/04/2000','DD/MM/YYYY'),3000), 

　　PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('01/05/2000','DD/MM/YYYY'),4000), 

　　PARTITION sales_2000 VALUES LESS THAN(MAXVALUE, MAXVALUE) 

　　); 

　　Insert into sales_range1 values(1,2,500, TO_DATE('21/01/2000','DD/MM/YYYY')); 

　　Insert into sales_range1 values(2,3,1500, sysdate);

　　如果多个分区列的值冲突，则按照从左到右的优先级。
　　List Partitioning：
　　可以组织无序的，或者没有关系的数据在相同的分区。
　　不支持多列的(multicolumn) partition keys，只能是一个列。
　　DEFAULT表示不满足条件的都放在这个分区。
　　

CREATE TABLE sales_list 

　　(salesman_id NUMBER(5), 

　　salesman_name VARCHAR2(30), 

　　sales_state VARCHAR2(20), 

　　sales_amount NUMBER(10), 

　　sales_date DATE) 

　　PARTITION BY LIST(sales_state) 

　　( 

　　PARTITION sales_west VALUES('California', 'Hawaii'), 

　　PARTITION sales_east VALUES ('New York', 'Virginia', 'Florida'), 

　　PARTITION sales_central VALUES('Texas', 'Illinois'), 

　　PARTITION sales_other VALUES(DEFAULT) 

　　); 

　　Hash Partitioning：

　　不可以作splitting, dropping or merging操作。但是可以added and coalesced.
　　当我们无法判断有多少数据映射或者怎样映射到各个分区时，可以使用这种方法。分区数据最好是2的幂，这样可以平均分配数据。
　　

CREATE TABLE sales_hash1 

　　(salesman_id NUMBER(5), 

　　salesman_name VARCHAR2(30), 

　　sales_amount NUMBER(10), 

　　week_no NUMBER(2)) 

　　PARTITION BY HASH(salesman_id) 

　　PARTITIONS 4 

　　STORE IN (users, TOOLS, TEST, TABLESPACE1);　　--表空间 

　　CREATE TABLE sales_hash 

　　(salesman_id NUMBER(5), 

　　salesman_name VARCHAR2(30), 

　　sales_amount NUMBER(10), 

　　week_no NUMBER(2)) 

　　PARTITION BY HASH(salesman_id) 

　　( 

　　PARTITION p1 tablespace users, 

　　PARTITION p2 tablespace system 

　　); 

　　Composite Partitioning:

　　先按照range分区，每个子分区又按照list or hash分区。
　　

CREATE TABLE sales_composite 

　　(salesman_id NUMBER(5), 

　　salesman_name VARCHAR2(30), 

　　sales_amount NUMBER(10), 

　　sales_date DATE) 

　　PARTITION BY RANGE(sales_date) 

　　SUBPARTITION BY HASH(salesman_id)　　--子分区 

　　SUBPARTITION TEMPLATE( 

　　SUBPARTITION sp1 TABLESPACE data1, 

　　SUBPARTITION sp2 TABLESPACE data2, 

　　SUBPARTITION sp3 TABLESPACE data3, 

　　SUBPARTITION sp4 TABLESPACE data4) 

　　(PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','DD/MM/YYYY')) 

　　PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','DD/MM/YYYY')) 

　　PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','DD/MM/YYYY')) 

　　PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','DD/MM/YYYY')) 

　　PARTITION sales_may2000 VALUES LESS THAN(TO_DATE('06/01/2000','DD/MM/YYYY')));

　　使用TEMPLATE，oracle会这样命名子分区：分区_子分区，比如sales_jan2000_sp1表示将数据放在data1表空间
　　

Range-list: 

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

　　SUBPARTITION TEMPLATE( 

　　SUBPARTITION east VALUES('NY', 'VA', 'FL') TABLESPACE system, 

　　SUBPARTITION west VALUES('CA', 'OR', 'HI') TABLESPACE users, 

　　SUBPARTITION central VALUES('IL', 'TX', 'MO') TABLESPACE tools) 

　　( PARTITION janfeb_2000 VALUES LESS THAN (TO_DATE('1-03-2000','DD-Mm-YYYY')), PARTITION marapr_2000 VALUES LESS THAN (TO_DATE('1-05-2000','DD-Mm-YYYY')), PARTITION mayjun_2000 VALUES LESS THAN (TO_DATE('1-07-2000','DD-Mm-YYYY')) );

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。