Oracle表分区

最新推荐文章于 2025-01-11 20:47:17 发布

早起睡熊

最新推荐文章于 2025-01-11 20:47:17 发布

阅读量1k

点赞数 1

分类专栏：数据库文章标签： oracle分区

本文链接：https://blog.youkuaiyun.com/fan_xiong/article/details/89713060

版权

数据库专栏收录该内容

0 篇文章

订阅专栏

本文详细介绍了Oracle数据库的几种分区方式，包括范围分区、散列分区、组合分区、列表分区、多列范围分区以及索引分区，针对每种分区类型阐述了适用场景及建表脚本示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一。常见的分区类型

1.范围（range）分区

2.散列（hash）分区

3.组合（composite）分区

4.列表（list）分区

5.多列范围（multicolumn range）分区

6.索引分区

二。范围（range）分区

1.适用场景：连续的序列，比如流水号或是日期

2.建表脚本

create table dept
(deptno      number(2),
 dept_name   varchar2(30))
 partition   by range(deptno)
(partition   d1 values less than (10) tablespace TB_1,
 partition   d2 values less than (20) tablespace TB_2,
 partition   d3 values less than (maxvalue) tablespace TB_default);

三。散列（hash）分区

1.适用场景：不连续的序列，比如根据全局唯一的id或是随机字段

2.建表脚本

create table cust_sales_hash
(acct_no     number(5),
 cust_name   varchar2(30),
 sale_day    integer not null,
 sale_mth    integer not null,
 sale_yr     integer not null
 )
 partition   by hash(acct_no)
 partitions  4 
 store in (TB_D_TEST80_STANDARD,TB_D_TEST0213_STANDARD,TB_D_TEST8000_STANDARD,TB_D_WEGOTB170817_STANDARD);

四。组合（composite）分区

1.适用场景：跟范围分区一样

2.建表脚本

create table orders
(ordid       number,
 acct_no     number(5),
 cust_name   char(30),
 orderdate   date,
 productid   number)
 partition   by range(orderdate)
 subpartition by hash(productid) subpartitions 8
(partition   q1 values less than (to_date('01-04-2018','dd-mm-yyyy')) tablespace TB_D_TEST80_STANDARD,
 partition   q2 values less than (to_date('01-07-2018','dd-mm-yyyy')) tablespace TB_D_TEST0213_STANDARD,
 partition   q3 values less than (to_date('01-10-2018','dd-mm-yyyy')) tablespace TB_D_TEST8000_STANDARD,
 partition   q4 values less than (maxvalue) tablespace TB_D_WEGOTB170817_STANDARD);

五。列表（list）分区

1.适用场景：适用基数小的情况，就是列的重复值大，比如性别，地区

2.建表脚本

create table dept_part
(deptno      number(2),
 dname       varchar2(14),
 loc         varchar2(13))
 partition   by list (dname)
(partition   d1_TBt   values ('BOSTON', 'NEW YORK') tablespace TB_D_TEST80_STANDARD,
 partition   d2_west   values ('SAN FRANCISCO', 'LOS ANGELES') tablespace TB_D_TEST0213_STANDARD,
 partition   d3_south  values ('ATLANTA', 'DALLAS') tablespace TB_D_TEST8000_STANDARD,
 partition   d3_north  values ('CHICAGO', 'DETROIT') tablespace TB_D_WEGOTB170817_STANDARD);

六。多列（multicolumn range）分区

1.适用场景：跟范围分区一样

2.建表脚本

create table cust_sales
(acct_no    number(5),
 cust_name  char(30),
 item_id    number(9),
 sale_day   integer not null,
 sale_mth   integer not null,
 sale_yr    integer not null)
 partition by range (sale_yr, sale_mth, sale_day)
(partition cust_sales_q1 values less than (2018, 04 , 01) tablespace TB_D_TEST80_STANDARD,
 partition cust_sales_q2 values less than (2018, 07 , 01) tablespace TB_D_TEST0213_STANDARD,
 partition cust_sales_q3 values less than (2018, 10 , 01) tablespace TB_D_TEST8000_STANDARD,
 partition cust_sales_q4 values less than (2019, 01 , 01) tablespace TB_D_T181114_STANDARD,
 partition cust_sales_qx values less than (maxvalue,maxvalue,maxvalue) tablespace TB_D_WEGOTB170817_STANDARD);

七。索引分区

1.适用场景：表存在索引，查询条件为索引字段

2.建表脚本（全局索引）

CREATE  TABLE GLOBAL_INDEX_EXAMPLE_T(
       ID         NUMBER,
       GIE_NO    VARCHAR2(500),
       GIE_DATE   DATE
)
PARTITION BY RANGE(GIE_DATE)
(PARTITION P1 VALUES LESS THAN (TO_DATE('2019-01-01','YYYY-MM-DD'))
,PARTITION P2 VALUES LESS THAN (TO_DATE('2019-02-01','YYYY-MM-DD'))
,PARTITION P3 VALUES LESS THAN (TO_DATE('2019-03-01','YYYY-MM-DD'))
,PARTITION P4 VALUES LESS THAN (TO_DATE('2019-04-01','YYYY-MM-DD'))
,PARTITION P5 VALUES LESS THAN (TO_DATE('2019-05-01','YYYY-MM-DD'))
,PARTITION P6 VALUES LESS THAN (TO_DATE('2019-06-01','YYYY-MM-DD'))
,PARTITION P7 VALUES LESS THAN (TO_DATE('2019-07-01','YYYY-MM-DD'))
);
--创建全局索引，且索引分区键和表分区键相同
CREATE INDEX SAME_GLOBAL_IDX ON GLOBAL_INDEX_EXAMPLE_T(GIE_DATE) GLOBAL
PARTITION BY RANGE(GIE_DATE)
(PARTITION S1 VALUES LESS THAN (TO_DATE('2019-01-01','YYYY-MM-DD'))
,PARTITION S2 VALUES LESS THAN (TO_DATE('2019-02-01','YYYY-MM-DD'))
,PARTITION S3 VALUES LESS THAN (TO_DATE('2019-03-01','YYYY-MM-DD'))
,PARTITION S4 VALUES LESS THAN (TO_DATE('2019-04-01','YYYY-MM-DD'))
,PARTITION S5 VALUES LESS THAN (TO_DATE('2019-05-01','YYYY-MM-DD'))
,PARTITION S6 VALUES LESS THAN (TO_DATE('2019-06-01','YYYY-MM-DD'))
,PARTITION S7 VALUES LESS THAN (TO_DATE('2019-07-01','YYYY-MM-DD'))
,PARTITION S8 VALUES LESS THAN (MAXVALUE)
);
--创建全局索引，索引的分区键和表分区键不相同
CREATE INDEX DIF_GLOBAL_IDX ON GLOBAL_INDEX_EXAMPLE_T(GIE_NO) GLOBAL 
PARTITION BY RANGE(GIE_NO)
(PARTITION D1 VALUES LESS THAN (2222666)
,PARTITION D2 VALUES LESS THAN (MAXVALUE)
);

3.建表脚本（本地索引）

create  table lacal_index_example_t (id number,example_no varchar2(500))
partition by RANGE (id)
(
partition p1 values less than (100) tablespace TB_D_TEST0213_STANDARD,
partition p2 values less than (200) tablespace TB_D_TEST80_STANDARD,
partition p3 values less than (maxvalue) tablespace TB_D_YUSUAN0801_STANDARD
);
create index prefix_local_index on lacal_index_example_t(id) local;--因ID是分区键，故为前缀本地索引
create index noprefix_local_index on lacal_index_example_t(example_no) local;--因example_no不是分区键，故为非前缀本地索引