oracle表分区详解（按天、按月、按年等）
发布时间：2020-12-12 14:06:52 所属栏目：百科 来源：网络整理
导读：分区表的概念： 当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都
|
分区表的概念：
当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表。 分区表的优点： 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,255)"> 5 LAST_NAME VARCHAR2(30) NOT NULL,255)"> 6 PHONE VARCHAR2(15) NOT NULL,255)"> 7 EMAIL VARCHAR2(80),255)"> 8 STATUS CHAR(1) 9 ) 10 PARTITION BY RANGE (andy_ID) 11 ( 12 PARTITION PART1 VALUES LESS THAN (10000),255)">13 PARTITION PART2 VALUES LESS THAN (20000) 14 ); Table created. -- 按时间分区 SQL> CREATE TABLE part_andy2 3 ORDER_ID NUMBER(7) NOT NULL,255)"> 4 ORDER_DATE DATE,255)"> 5 OTAL_AMOUNT NUMBER,255)"> 6 CUSTOTMER_ID NUMBER(7),255)"> 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')),255)">12 PARTITION p2 VALUES LESS THAN (TO_DATE('2015-10-1',255)">13 PARTITION p3 VALUES LESS THAN (TO_DATE('2016-10-1',255)">14 partition p4 values less than (maxvalue) 15 ); 2. Hash分区 概念： 对于那些无法有效划分范围的表，可以使用hash分区，这样对于提高性能还是会有一定的帮助。hash分区会将表中的数据平均分配到你指定的几个分区中，列所在分区是依据分区列的hash值自动分配，因此你并不能控制也不知道哪条记录会被放到哪个分区中，hash分区也可以支持多个依赖列。 注意： hash分区最主要的机制是根据hash算法来计算具体某条纪录应该插入到哪个分区中,hash算法中最重要的是hash函数，Oracle中如果你要使用hash分区，只需指定分区的数量即可。建议分区的数量采用2的n次方，这样可以使得各个分区间数据分布更加均匀。 --按hash分区 SQL> create table part_andy3 3 transaction_id number primary key,255)"> 4 item_id number(8) not null 5 ) 6 partition by hash(transaction_id) 7 ( 8 partition part_01,255)"> 9 partition part_02,255)">10 partition part_03 11 ); 3. List分区 List分区也需要指定列的值，其分区值必须明确指定，该分区列只能有一个，不能像range或者hash分区那样同时指定多个列做为分区依赖列，但它的单个分区对应值可以是多个。 在分区时必须确定分区列可能存在的值，一旦插入的列值不在分区范围内，则插入/更新就会失败，因此通常建议使用list分区时，要创建一个default分区存储那些不在指定范围内的记录，类似range分区中的maxvalue分区。 -- 按list分区 SQL> create table part_andy4 3 id varchar2(15 byte) not null,255)"> 4 city varchar2(20) 6 partition by list (city) 8 partition t_list025 values ('beijing'),255)"> 9 partition t_list372 values ('shanghai'),255)">10 partition t_list510 values ('changsha'),255)">11 partition p_other values (default) 12 ); 4. 组合分区 Oracle10g提供两种分区组合 – Range-hash SQL> create table part_andy5 4 item_id number(8) not null,255)"> 5 item_description varchar2(300),255)"> 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',255)">11 partition part_02 values less than(TO_DATE('2015-10-1',255)">12 partition part_03 values less than(maxvalue) 13 ); – Range-list SQL> CREATE TABLE SALES 3 PRODUCT_ID VARCHAR2(5),255)"> 4 SALES_DATE DATE,255)"> 5 SALES_COST NUMBER(10),255)"> 6 STATUS VARCHAR2(20) 8 PARTITION BY RANGE(SALES_DATE) SUBPARTITION BY LIST (STATUS) 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',255)">13 ( 14 SUBPARTITION P2SUB1 VALUES ('ACTIVE'),255)">15 SUBPARTITION P2SUB2 VALUES ('INACTIVE') 16 ) 17 ); Oracle11g增加了四种组合 – RANGE-RANGE – LIST-RANGE – LIST-HASH – LIST-LIST Oracle 11g 中虚拟列来实现。在11g之前 分区表的partition key必须是物理存在的。11g开始提供了虚拟列，并且可以作为partition key 。 --按星期分区 SQL> CREATE TABLE part_andy6 3 getdate date NOT NULL,255)"> 4 wd NUMBER GENERATED ALWAYS AS (TO_NUMBER (TO_CHAR (getdate,'D'))) VIRTUAL 6 PARTITION BY LIST (wd) 8 PARTITION Mon VALUES (1),255)"> 9 PARTITION Tue VALUES (2),255)">10 PARTITION Wed VALUES (3),255)">11 PARTITION Thu VALUES (4),255)">12 PARTITION Fri VALUES (5),255)">13 PARTITION Sat VALUES (6),255)">14 PARTITION Sun VALUES (7) SQL> SQL> insert into part_andy6(getdate) values(sysdate); 1 row created. SQL> insert into part_andy6(getdate) values(sysdate-1); SQL> insert into part_andy6(getdate) values(sysdate-2); SQL> insert into part_andy6(getdate) values(sysdate-3); SQL> insert into part_andy6(getdate) values(sysdate-4); SQL> insert into part_andy6(getdate) values(sysdate-5); SQL> insert into part_andy6(getdate) values(sysdate-6); SQL> insert into part_andy6(getdate) values(sysdate-7); -- 检查测试成功 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',255)"> 6 PARTITION part2 7 VALUES LESS THAN (TO_DATE('2014-12-1',255)"> 8 ); 注意：如果在建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; --下一个月 d. 观察自动创建的分区： 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 INTERVAL_ANDY7 SYS_P33 INTERVAL_ANDY7 SYS_P34 13 rows selected. 下面创建一个以天为间隔的分区表： 1. 创建分区表： SQL> create table interval_andy8 3 id number,255)"> 4 dt date 6 partition by range (dt) 7 INTERVAL (NUMTODSINTERVAL(1,'day')) 8 ( 9 partition p20141101 values less than (to_date('2014-11-1',255)">10 ); 2. 查看表分区： 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; / 4. 观察自动创建的分区： 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 INTERVAL_ANDY8 SYS_P45 INTERVAL_ANDY8 SYS_P46 13 rows selected. （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |