1.7.分区表测试.txt - 墨天轮文档

1.7.分区表测试.txt

悟道

3页

1次

2024-08-01

免费下载

1.创建水平分区表，包括范围分区表、哈希分区表、列表分区表。

--范围分区

create table tab_range (c1 int , c2 varchar(10), c3 date)partition by range(c3)

(partition p1 values less than ('2012-3-1'),partition p2 values less than

('2012-6-1'),partition p3 values less than ('2012-9-1'),partition p4 values less

than (maxvalue));

--列表分区

create table tab_list (

id number,

name varchar2(32),

city varchar2(32))

partition by list(city)

(

partition p_jiangsu values ('nanjing', 'suzhou'),

partition p_zhejiang values('hangzhou', 'jiaxing')

);

--哈希分区

create table tab_hash(

id number,

name varchar2(32))

partition by hash(id)

(

partition p1,

partition p2

);

2.分区列包含多个列。

create table sales_data (

sale_id number,

sale_date date,

region varchar2(50),

amount number

)

partition by range (sale_date) subpartition by list (region) (

partition p_2021 values less than ('2022-01-01') (

subpartition sp_americas values ('americas'),

subpartition sp_europe values ('europe'),

subpartition sp_asia values ('asia'),

subpartition sp_other values (default)

partition p_2022 values less than ('2023-01-01') (

subpartition sp_americas values ('americas'),

subpartition sp_europe values ('europe'),

subpartition sp_asia values ('asia'),

subpartition sp_other values (default)

)

);

\d+ sales_data

3.分区表操作，包括查询分区、增加分区、删除分区、截断分区、合并分区、拆分分区、交换分区、重命名

分区。

--查询分区

insert into tab_range values(1, 'a', '2012-1-1');

insert into tab_range values(2, 'b','2012-5-1');

insert into tab_range values(3, 'c','2012-12-1');

select* from tab_range_1_prt_p1;

select* from tab_range_1_prt_p2;

select* from tab_range_1_prt_p3;

select* from tab_range_1_prt_p4;

--增加分区

create table tab_range_new (

id number,

name varchar2(32),

create_time date)

partition by range(create_time)

(

partition p1 values less than (to_date('2023-02-01', 'yyyy-mm-dd')),

partition p2 values less than (to_date('2023-03-01', 'yyyy-mm-dd')),

partition p3 values less than (to_date('2023-04-01', 'yyyy-mm-dd'))

);

alter table tab_range_new add partition p4 values less than (to_date('2023-05-

01', 'yyyy-mm-dd'));

\d+ tab_range_new;

--删除分区

alter table tab_range_new drop partition p4;

\d+ tab_range_new;

--截断分区

alter table tab_range truncate partition p4;

select * from tab_range_1_prt_p4;

--合并分区

alter table tab_range_new add partition p4 values less than (to_date('2023-05-

01', 'yyyy-mm-dd'));

alter table tab_range_new merge partitions p3,p4_new into partition p3p4;

\d+ tab_range_new;

--拆分分区

drop table sales_data;

create table sales_data (

sale_id number,

sale_date date,

amount number

)

partition by range (sale_date) (

partition p_2023 values less than (to_date('2024-01-01', 'yyyy-mm-dd'))

);

alter table sales_data split partition p_2023 at (to_date('2023-07-01', 'yyyy-

mm-dd'))

into (partition p_2023_h1, partition p_2023_h2);

\d+ sales_data

--交换分区

drop table sales_data;

create table sales_data (

sale_id number,

sale_date date,

amount number

)

partition by range (sale_date) (

partition p_2023 values less than (to_date('2024-01-01', 'yyyy-mm-dd'))

);

insert into sales_data (sale_id, sale_date, amount) values (1, to_date('2023-04-

01', 'yyyy-mm-dd'), 100);

create table sales_data_temp (

sale_id number,

sale_date date,

amount number

);

alter table sales_data exchange partition p_2023 with table sales_data_temp;

\d+ sales_data_temp

select * from sales_data_temp;

--重命名分区

alter table sales_data rename partition p_2023 to p_2023_new;

of 3

免费下载

test

关注

评论