欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

oracle按天、按月、按年进行表分区详解

程序员文章站 2023-01-28 13:56:22
分区表的概念: 当表中的数据量不断增大,查询数据的速度就会变慢,应用程序的性能就会下降,这时就应该考虑对表进行分区。表进行分区后,逻辑上表仍然是一张完整的表,只是将表中的数据在物理上存放到多个表空间...

分区表的概念:

当表中的数据量不断增大,查询数据的速度就会变慢,应用程序的性能就会下降,这时就应该考虑对表进行分区。表进行分区后,逻辑上表仍然是一张完整的表,只是将表中的数据在物理上存放到多个表空间(物理文件上),这样查询数据时,不至于每次都扫描整张表。

分区表的优点:

1) 改善查询性能:对分区对象的查询可以仅搜索自己关心的分区,提高检索速度。

2) 增强可用性:如果表的某个分区出现故障,表在其他分区的数据仍然可用;

3) 维护方便:如果表的某个分区出现故障,需要修复数据,只修复该分区即可;

4) 均衡i/o:可以把不同的分区映射到磁盘以平衡i/o,改善整个性能。

分区表的种类:

1.范围分区

概念: 范围分区将数据基于范围映射到每一个分区,这个范围是你在创建分区时指定的分区键决定的。这种分区方式是最为常用的,并且分区键经常采用日期。举个例子:你可能会将销售数据按照月份进行分区。

-- 按行分区

sql> create table part_andy1

2 (

3 andy_id number not null primary key,

4 first_name varchar2(30) not null,

5 last_name varchar2(30) not null,

6 phone varchar2(15) not null,

7 email varchar2(80),

8 status char(1)

9 )

10 partition by range (andy_id)

11 (

12 partition part1 values less than (10000) ,

13 partition part2 values less than (20000)

14 );

table created.

-- 按时间分区

sql> create table part_andy2

2 (

3 order_id number(7) not null,

4 order_date date,

5 otal_amount number,

6 custotmer_id number(7),

7 paid char(1)

8 )

9 partition by range (order_date)

10 (

11 partition p1 values less than (to_date('2014-10-1', 'yyyy-mm-dd')) ,

12 partition p2 values less than (to_date('2015-10-1', 'yyyy-mm-dd')) ,

13 partition p3 values less than (to_date('2016-10-1', 'yyyy-mm-dd')) ,

14 partition p4 values less than (maxvalue)

15 );

table created.

2. hash分区

概念:

对于那些无法有效划分范围的表,可以使用hash分区,这样对于提高性能还是会有一定的帮助。hash分区会将表中的数据平均分配到你指定的几个分区中,列所在分区是依据分区列的hash值自动分配,因此你并不能控制也不知道哪条记录会被放到哪个分区中,hash分区也可以支持多个依赖列。

注意:

hash分区最主要的机制是根据hash算法来计算具体某条纪录应该插入到哪个分区中,hash算法中最重要的是hash函数,oracle中如果你要使用hash分区,只需指定分区的数量即可。建议分区的数量采用2的n次方,这样可以使得各个分区间数据分布更加均匀。

--按hash分区

sql> create table part_andy3

2 (

3 transaction_id number primary key,

4 item_id number(8) not null

5 )

6 partition by hash(transaction_id)

7 (

8 partition part_01 ,

9 partition part_02 ,

10 partition part_03

11 );

table created.

3. list分区

概念:

list分区也需要指定列的值,其分区值必须明确指定,该分区列只能有一个,不能像range或者hash分区那样同时指定多个列做为分区依赖列,但它的单个分区对应值可以是多个。

注意:

在分区时必须确定分区列可能存在的值,一旦插入的列值不在分区范围内,则插入/更新就会失败,因此通常建议使用list分区时,要创建一个default分区存储那些不在指定范围内的记录,类似range分区中的maxvalue分区。

-- 按list分区

sql> create table part_andy4

2 (

3 id varchar2(15 byte) not null,

4 city varchar2(20)

5 )

6 partition by list (city)

7 (

8 partition t_list025 values ('beijing'),

9 partition t_list372 values ('shanghai') ,

10 partition t_list510 values ('changsha'),

11 partition p_other values (default)

12 );

table created.

4. 组合分区

oracle10g提供两种分区组合

– range-hash

sql> create table part_andy5

2 (

3 transaction_id number primary key,

4 item_id number(8) not null,

5 item_description varchar2(300),

6 transaction_date date

7 )

8 partition by range(transaction_date)subpartition by hash(transaction_id)

9 (

10 partition part_01 values less than(to_date('2014-10-1', 'yyyy-mm-dd')),

11 partition part_02 values less than(to_date('2015-10-1', 'yyyy-mm-dd')),

12 partition part_03 values less than(maxvalue)

13 );

table created.

– range-list

sql> create table sales

2 (

3 product_id varchar2(5),

4 sales_date date,

5 sales_cost number(10),

6 status varchar2(20)

7 )

8 partition by range(sales_date) subpartition by list (status)

9 (

10 partition p1 values less than(to_date('2014-10-1', 'yyyy-mm-dd'))

11 (subpartition p1sub1 values ('active') ,subpartition p1sub2 values ('inactive')

12 ),partition p2 values less than (to_date('2015-10-1', 'yyyy-mm-dd'))

13 (

14 subpartition p2sub1 values ('active') ,

15 subpartition p2sub2 values ('inactive')

16 )

17 );

table created.

oracle11g增加了四种组合

– range-range

– list-range

– list-hash

– list-list

oracle 11g 中虚拟列来实现。在11g之前 分区表的partition key必须是物理存在的。11g开始提供了虚拟列,并且可以作为partition key 。

--按星期分区

sql> create table part_andy6

2 (

3 getdate date not null,

4 wd number generated always as (to_number (to_char (getdate, 'd'))) virtual

5 )

6 partition by list (wd)

7 (

8 partition mon values (1),

9 partition tue values (2),

10 partition wed values (3),

11 partition thu values (4),

12 partition fri values (5),

13 partition sat values (6),

14 partition sun values (7)

15 );

table created.

sql>

sql> insert into part_andy6(getdate) values(sysdate);

1 row created.

sql> insert into part_andy6(getdate) values(sysdate-1);

1 row created.

sql> insert into part_andy6(getdate) values(sysdate-2);

1 row created.

sql> insert into part_andy6(getdate) values(sysdate-3);

1 row created.

sql> insert into part_andy6(getdate) values(sysdate-4);

1 row created.

sql> insert into part_andy6(getdate) values(sysdate-5);

1 row created.

sql> insert into part_andy6(getdate) values(sysdate-6);

1 row created.

sql> insert into part_andy6(getdate) values(sysdate-7);

1 row created.

-- 检查测试成功

sql> select * from part_andy6;

getdate wd

------------------- ----------

2014-11-23 16:35:07 1

2014-11-24 16:35:07 2

2014-11-25 16:35:07 3

2014-11-26 16:35:07 4

2014-11-27 16:35:07 5

2014-11-28 16:35:07 6

2014-11-29 16:35:07 7

2014-11-22 16:35:08 7

8 rows selected.

oracle database 11g,interval类型分区表,可以根据加载数据,自动创建指定间隔的分区。

创建按月分区的分区表:

a. 创建分区表

sql> create table interval_andy7 (a1 number, a2 date)

2 partition by range (a2)

3 interval ( numtoyminterval (1, 'month') )

4 (partition part1

5 values less than (to_date('2014-11-1', 'yyyy-mm-dd')),

6 partition part2

7 values less than (to_date('2014-12-1', 'yyyy-mm-dd'))

8 );

table created.

注意:如果在建interval分区表是没有把所有的分区写完成,在插入相关数据后会自动生成分区

b. 查看现在表的分区:

sql> select table_name,partition_name from user_tab_partitions where table_name='interval_andy7';

table_name partition_name

------------------------------ ------------------------------

intervalpart part1

intervalpart part2

c. 插入测试数据:

sql> begin

2 for i in 0 .. 11 loop

3 insert into interval_andy7 values(i,add_months(to_date('2014-11-1','yyyy-mm-dd'),i));

4 end loop ;

5 commit;

6 end;

7 /

pl/sql procedure successfully completed.

pl/sql 过程已成功完成。

补充:add_months()函数获取前一个月或者下一个月的月份, 参数中 负数 代表 往前, 正数 代表 往后。

--上一个月

select to_char(add_months(trunc(sysdate),-1),'yyyymm') from dual;

--下一个月

select to_char(add_months(trunc(sysdate),1),'yyyymm') from dual;

d. 观察自动创建的分区:

sql> select table_name,partition_name from user_tab_partitions where table_name='interval_andy7';

table_name partition_name

------------------------------ ------------------------------

interval_andy7 part1

interval_andy7 part2

interval_andy7 sys_p24

interval_andy7 sys_p25

interval_andy7 sys_p26

interval_andy7 sys_p27

interval_andy7 sys_p28

interval_andy7 sys_p29

interval_andy7 sys_p30

interval_andy7 sys_p31

interval_andy7 sys_p32

table_name partition_name

------------------------------ ------------------------------

interval_andy7 sys_p33

interval_andy7 sys_p34

13 rows selected.

下面创建一个以天为间隔的分区表:

1. 创建分区表:

sql> create table interval_andy8

2 (

3 id number,

4 dt date

5 )

6 partition by range (dt)

7 interval (numtodsinterval(1,'day'))

8 (

9 partition p20141101 values less than (to_date('2014-11-1','yyyy-mm-dd'))

10 );

table created.

2. 查看表分区:

sql> select table_name,partition_name from user_tab_partitions where table_name='interval_andy8';

table_name partition_name

------------------------------ ------------------------------

interval_andy8 p20141101

3. 插入测试数据:

begin

for i in 1 .. 12 loop

insert into interval_andy8 values(i,trunc(to_date('2014-11-1','yyyy-mm-dd')+i));

end loop;

commit;

end;

/

pl/sql 过程已成功完成。

4. 观察自动创建的分区:

sql> select table_name,partition_name from user_tab_partitions where table_name='interval_andy8';

table_name partition_name

------------------------------ ------------------------------

interval_andy8 p20141101

interval_andy8 sys_p35

interval_andy8 sys_p36

interval_andy8 sys_p37

interval_andy8 sys_p38

interval_andy8 sys_p39

interval_andy8 sys_p40

interval_andy8 sys_p41

interval_andy8 sys_p42

interval_andy8 sys_p43

interval_andy8 sys_p44

table_name partition_name

------------------------------ ------------------------------

interval_andy8 sys_p45

interval_andy8 sys_p46

13 rows selected.