标签：11g 12 30 partition ORCL 分区表 2023 oracle leo
文档课题：oracle 11g分区表相关知识.
数据库：oracle 11.2.0.4
1、相关知识
表分区指允许用户将一个表分成多个分区，用户可以只访问表中的特定分区.可以将不同的分区存储在不同的磁盘，提高访问性能和安全性，可以独立地备份和恢复每个分区.主要有范围分区、散列分区、列表分区、复合分区.
2、范围分区
2.1、理论知识
说明：以表中的某列或一组列的值的范围进行分区.
范围分区语法：
partition by range(column_name)
(
partition part1 value less than(range1),
partition part2 value less than(range2),
......
partition partn value less than(maxvalue)
);
2.2、实际操作
leo@ORCL 2023-12-30 15:59:41> create table sales(
product_id varchar2(5),sales_count number(10,2)
)
partition by range(sales_count)
(
partition p1 values less than(1000),
partition p2 values less than(2000),
partition p3 values less than(3000)
);
Table created.
leo@ORCL 2023-12-30 16:10:35> select table_owner,table_name,partition_name,high_value,partition_position from dba_tab_partitions u where u.table_name='SALES';
TABLE_OWNE TABLE_NAME PARTITION_NAME HIGH_VALUE PARTITION_POSITION
---------- ---------- ------------------------------ --------------- ------------------
LEO SALES P1 1000 1
LEO SALES P2 2000 2
LEO SALES P3 3000 3
--测试insert语句.
leo@ORCL 2023-12-30 16:10:37> insert into sales values('1',600);
1 row created.
leo@ORCL 2023-12-30 16:16:52> insert into sales values('2',1000);
1 row created.
leo@ORCL 2023-12-30 16:16:57> insert into sales values('3',2300);
1 row created.
leo@ORCL 2023-12-30 16:17:02> insert into sales values('4',6000);
insert into sales values('4',6000)
*
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition
--查询表数据
leo@ORCL 2023-12-30 16:18:17> select * from sales partition(p1);
PRODU SALES_COUNT
----- -----------
1 600
leo@ORCL 2023-12-30 16:19:05> select * from sales partition(p2);
PRODU SALES_COUNT
----- -----------
2 1000
leo@ORCL 2023-12-30 16:19:20> alter table sales add partition p4 values less than(maxvalue);
Table altered.
leo@ORCL 2023-12-30 16:20:54> insert into sales values('4',6000);
1 row created.
leo@ORCL 2023-12-30 16:21:04> select * from sales partition(p4);
PRODU SALES_COUNT
----- -----------
4 6000
leo@ORCL 2023-12-30 16:21:23> select table_owner,table_name,partition_name,high_value,partition_position from dba_tab_partitions u where u.table_name='SALES';
TABLE_OWNE TABLE_NAME PARTITION_NAME HIGH_VALUE PARTITION_POSITION
---------- ---------- ------------------------------ --------------- ------------------
LEO SALES P1 1000 1
LEO SALES P2 2000 2
LEO SALES P3 3000 3
LEO SALES P4 MAXVALUE 4
3、散列分区
3.1、理论知识
a、 允许用户对不具有逻辑范围的数据进行分区；
b、 通过在分区建上执行HASH函数决定存储的分区；
c、 将数据平均分布到不同分区.
3.2、实际操作
leo@ORCL 2023-12-30 16:21:39> create table my_emp(
empno number,ename varchar2(10)
)
partition by hash(empno)
(
partition p1,partition p2
);
leo@ORCL 2023-12-30 16:31:06> select table_name,partition_name,high_value,partition_position from user_tab_partitions u where u.table_name='MY_EMP';
TABLE_NAME PARTITION_NAME HIGH_VALUE PARTITION_POSITION
---------- ------------------------------ --------------- ------------------
MY_EMP P1 1
MY_EMP P2 2
leo@ORCL 2023-12-30 16:31:12> insert into my_emp values(1,'A');
1 row created.
leo@ORCL 2023-12-30 16:33:38> insert into my_emp values(2,'B');
1 row created.
leo@ORCL 2023-12-30 16:33:42> insert into my_emp values(3,'C');
1 row created.
leo@ORCL 2023-12-30 16:33:47> select * from my_emp partition(p1);
EMPNO ENAME
---------- ----------
2 B
leo@ORCL 2023-12-30 16:34:33> select * from my_emp partition(p2);
EMPNO ENAME
---------- ----------
1 A
3 C
4、列表分区
4.1、理论知识
a、允许用户将不相关的数据组织在一起.
列表分区语法：
partition by list (column_name)
(
partition part1 values (values_list1)，
partition part2 values (values_list2)，
...
partition partn values (default)
);
4.2、实际操作
leo@ORCL 2023-12-30 16:40:57> create table personCity(
id number,name varchar2(10),city varchar2(10)
)
partition by list(city)
(
partition 东边 values('开封','商丘'),
partition 西边 values('洛阳'),
partition 南边 values('许昌'),
partition 北边 values('新乡')
);
leo@ORCL 2023-12-30 16:43:53> insert into personcity values(1,'A','开封');
1 row created.
leo@ORCL 2023-12-30 16:43:58> insert into personcity values(2,'B','商丘');
1 row created.
leo@ORCL 2023-12-30 16:44:02> insert into personcity values(3,'C','洛阳');
1 row created.
leo@ORCL 2023-12-30 16:45:49> col name for a15
leo@ORCL 2023-12-30 16:45:54> select * from personcity partition(东边);
ID NAME CITY
---------- --------------- ----------
1 A 开封
2 B 商丘
leo@ORCL 2023-12-30 16:45:55> create table student(
sno number,sname varchar2(10)
)
partition by range(sno)
subpartition by hash(sname)
subpartitions 4
(
partition p1 values less than(1000),
partition p2 values less than(2000),
partition p3 values less than(maxvalue)
);
Table created.
说明：subpartitions 4表示每个范围分区都有4个散列子分区，所以最终会得到12个子分区.
leo@ORCL 2023-12-30 17:01:06> select table_name,partition_name,subpartition_count,high_value,partition_position from user_tab_partitions u where u.table_name='STUDENT'
TABLE_NAME PARTITION_NAME SUBPARTITION_COUNT HIGH_VALUE PARTITION_POSITION
--------------- --------------- ------------------ --------------- ------------------
STUDENT P1 4 1000 1
STUDENT P2 4 2000 2
STUDENT P3 4 MAXVALUE 3
说明：每个范围分区的4个散列子分区可以通过EM查看.
5、引用分区
5.1、理论知识
引用分区是指基于外键引用父表分区的方法，其依赖于已有的父表子表关系，子表通过外键关联到父表，进而继承父表的分区方式而不需自己创建，子表还继承父表的维护操作.
a、主表是范围分区，子表是引用分区；
b、主表是列表分区，子表是引用分区；
c、主表是散列分区，子表是引用分区.
5.2、实际操作
leo@ORCL 2023-12-30 17:08:23> drop table my_emp;
Table dropped.
leo@ORCL 2023-12-30 17:08:29> drop table personcity;
Table dropped.
leo@ORCL 2023-12-30 17:08:39> drop table student;
Table dropped.
leo@ORCL 2023-12-30 17:08:49> purge recyclebin;
Recyclebin purged.
leo@ORCL 2023-12-30 17:45:16> create table student(
stu_id number primary key,stu_name varchar2(10),grade varchar2(10)
)
partition by range(stu_id)
(
partition par_stu1 values less than(1000),
partition par_stu2 values less than(2000),
partition par_stu3 values less than(maxvalue)
);
Table created.
leo@ORCL 2023-12-30 17:50:12> create table score(
id number primary key,stu_id number not null,course_name varchar2(20),
score number,
constraint fk_score foreign key(stu_id) references student(stu_id)
) partition by reference(fk_score);
Table created.
leo@ORCL 2023-12-30 17:52:37> select table_name,partition_name,subpartition_count,high_value,partition_position from user_tab_partitions u where u.table_name in ('STUDENT','SCORE');
TABLE_NAME PARTITION_NAME SUBPARTITION_COUNT HIGH_VALUE PARTITION_POSITION
--------------- --------------- ------------------ --------------- ------------------
SCORE PAR_STU1 0 1
SCORE PAR_STU2 0 2
SCORE PAR_STU3 0 3
STUDENT PAR_STU1 0 1000 1
STUDENT PAR_STU2 0 2000 2
STUDENT PAR_STU3 0 MAXVALUE 3
6 rows selected.
说明：SCORE和STUDENT表的high value值一样.
leo@ORCL 2023-12-30 17:53:02> insert into student values(1,'AA','一年级');
1 row created.
leo@ORCL 2023-12-30 18:00:11> insert into student values(22,'BB','三年级');
1 row created.
leo@ORCL 2023-12-30 18:00:12> select * from student partition(par_stu1);
STU_ID STU_NAME GRADE
---------- ---------- ----------
1 AA 一年级
22 BB 三年级
leo@ORCL 2023-12-30 18:00:57> insert into score values(1,1,'语文',70);
1 row created.
leo@ORCL 2023-12-30 18:01:22> insert into score values(2,22,'数学',80);
1 row created.
leo@ORCL 2023-12-30 18:01:23> select * from score partition(par_stu1);
ID STU_ID COURSE_NAME SCORE
---------- ---------- -------------------- ----------
1 1 语文 70
2 22 数学 80
说明：可以看到在score表的stu_id列上进行分区.
6、间隔分区
6.1、理论知识
间隔分区指完全自动地根据间隔阀值创建范围分区，它是范围分区的扩展，在数据仓库中有广泛应用.
6.2、实际操作
leo@ORCL 2023-12-30 19:27:02> create table sale_detail(
sale_detail_id number,product_id number,quantity number,sale_date date
)
partition by range(sale_date)
interval (numtoyminterval(1,'MONTH'))
(
partition p_201006 values less than(to_date('20100601','yyyymmdd'))
);
leo@ORCL 2023-12-30 19:29:42> select table_name,partition_name,high_value,partition_position from user_tab_partitions u where u.table_name='SALE_DETAIL';
TABLE_NAME PARTITION_NAME HIGH_VALUE PARTITION_POSITION
------------- --------------- ----------------------------------------------------------------------------------- ------------------
SALE_DETAIL P_201206 TO_DATE(' 2010-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') 1
leo@ORCL 2023-12-30 19:29:42> insert into sale_detail values(1,100,20,to_date('20100121','yyyymmdd'));
1 row created.
leo@ORCL 2023-12-30 19:32:47> insert into sale_detail values(2,100,30,to_date('20100621','yyyymmdd'));
1 row created.
leo@ORCL 2023-12-30 19:32:53> insert into sale_detail values(3,100,40,to_date('20100721','yyyymmdd'));
1 row created.
leo@ORCL 2023-12-30 19:36:03> select table_name,partition_name,high_value,partition_position from user_tab_partitions u where u.table_name='SALE_DETAIL';
TABLE_NAME PARTITION_NAME HIGH_VALUE PARTITION_POSITION
------------- --------------- ----------------------------------------------------------------------------------- ------------------
SALE_DETAIL P_201006 TO_DATE(' 2010-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') 1
SALE_DETAIL SYS_P63 TO_DATE(' 2010-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') 2
SALE_DETAIL SYS_P64 TO_DATE(' 2010-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') 3
leo@ORCL 2023-12-30 19:35:10> select * from sale_detail partition(p_201006);
SALE_DETAIL_ID PRODUCT_ID QUANTITY SALE_DATE
-------------- ---------- ---------- -------------------
1 100 20 2010-01-21 00:00:00
leo@ORCL 2023-12-30 19:36:49> insert into sale_detail values(4,100,50,to_date('20101121','yyyymmdd'));
1 row created.
leo@ORCL 2023-12-30 19:38:25> select table_name,partition_name,high_value,partition_position from user_tab_partitions u where u.table_name='SALE_DETAIL';
TABLE_NAME PARTITION_NAME HIGH_VALUE PARTITION_POSITION
------------- --------------- ----------------------------------------------------------------------------------- ------------------
SALE_DETAIL P_201006 TO_DATE(' 2010-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') 1
SALE_DETAIL SYS_P63 TO_DATE(' 2010-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') 2
SALE_DETAIL SYS_P64 TO_DATE(' 2010-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') 3
SALE_DETAIL SYS_P65 TO_DATE(' 2010-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') 4
7、基于虚拟列的分区
7.1、理论知识
基于虚拟列的分区是指把分区建立在某个虚拟列上，即建立在函数或表达式的计算结果上，来完成某种任务。
7.2、实际操作
create table sale(
sale_id number primary key,product_id number,price number,
quantity number,sale_date date,
total_price as(price*quantity) virtual
)
partition by range(total_price)
(
partition p_1000 values less than(1000),
partition p_2000 values less than(2000),
partition p_max values less than(maxvalue)
);
insert into sale(sale_id,product_id,price,quantity,sale_date) values (1,100,15,20,sysdate);
leo@ORCL 2023-12-30 19:46:34> select * from sale partition(p_1000);
SALE_ID PRODUCT_ID PRICE QUANTITY SALE_DATE TOTAL_PRICE
---------- ---------- ---------- ---------- ------------------- -----------
1 100 15 20 2023-12-30 19:46:34 300
8、系统分区
8.1、理论知识
不指定分区列，由oracle来完成分区的控制和管理，其没有范围分区或列表分区的界限。
8.2、实际操作
leo@ORCL 2023-12-30 20:01:20> create table person(
id number,name varchar2(20),address varchar2(20)
)
partition by system
(partition p1,partition p2,partition p3);
Table created.
leo@ORCL 2023-12-30 20:02:52> select table_name,partition_name,high_value,partition_position from user_tab_partitions u where u.table_name='PERSON';
TABLE_NAME PARTITION_NAME HIGH_VALUE PARTITION_POSITION
--------------- --------------- --------------- ------------------
PERSON P1 1
PERSON P2 2
PERSON P3 3
9、操作分区表
说明：在已分区的表中插入数据与操作普通表完全相同，oracle会自动将数据保存到对应的分区。查询、修改和删除分区表时需显示指定要操作的分区
添加分区-在最后一个分区后添加新分区
alter table sales add partition p4 values less than (4000);
删除分区-删除指定分区，分区的数据也随之删除
alter table sales drop partition p4;
截断分区-删除指定分区中的所有记录
alter table sales truncate partition p3;
合并分区-将范围分区或复合分区的两个相邻分区连接起来
alter table sales merge partitions s1,s2 into partition s2;
拆分分区-将一个大分区中的记录拆分到两个分区中
alter table sales split partition p2 at (1500) into (partition p21,partition p22);
说明：以上内容来自王二暖老师的学习视屏.
标签：11g,
12,
30,
partition,
ORCL,
分区表,
2023,
oracle,
leo
From： https://blog.51cto.com/u_12991611/9042121