文档课题：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);

说明：以上内容来自王二暖老师的学习视屏.