一：前言
二：分区技术概述
三：创建表分区
1：范围分区
1 ----创建一个商品零售表，然后为该表按照销售日期所在季度创建4个分区 2 3 SYSTEM@orcl> ho clear 4 5 6 SYSTEM@orcl> select tablespace_name from dba_data_files; 7 8 TABLESPACE_NAME 9 ------------------------------ 10 USERS 11 UNDOTBS1 12 SYSAUX 13 SYSTEM 14 EXAMPLE 15 TBS_TEST1 16 TBS_TEST_3 17 TBS_TEST4 18 TBS_TEST5 19 TBS_BIG_1 20 21 10 rows selected. 22 23 SYSTEM@orcl> conn scott/scott; 24 Connected. 25 SCOTT@orcl> create table ware_retail_part --创建一个描述商品零售的数据表 26 2 ( 27 3 id integer primary key,--销售编号 28 4 retail_date date,--销售日期 29 5 ware_name varchar2(50)--商品名称 30 6 ) 31 7 partition by range(retail_date) 32 8 ( 33 9 --2011年第一个季度为part_01分区 34 10 partition par_01 values less than(to_date('2011-04-01','yyyy-mm-dd')) tablespace TBS_TEST1, 35 11 --2011年第二个季度为part_02分区 36 12 partition par_02 values less than(to_date('2011-07-01','yyyy-mm-dd')) tablespace TBS_TEST_3, 37 13 --2011年第三个季度为part_03分区 38 14 partition par_03 values less than(to_date('2011-10-01','yyyy-mm-dd')) tablespace TBS_TEST4, 39 15 --2011年第四个季度为part_04分区 40 16 partition par_04 values less than(to_date('2012-01-01','yyyy-mm-dd')) tablespace TBS_TEST1 41 17 ); 42 43 44 Table created. 45 46 SCOTT@orcl> SCOTT@orcl>
1 create table ware_retail_part2 --创建一个描述商品零售的数据表 2 ( 3 id integer primary key,--销售编号 4 retail_date date,--销售日期 5 ware_name varchar2(50)--商品名称 6 ) 7 partition by range(id,retail_date)--按照销售序号和销售日期分区 8 ( 9 --第一个分区part_01 10 partition par_01 values less than(10000,to_date('2011-12-01','yyyy-mm-dd')) tablespace TBSP_1, 11 --第一个分区part_02 12 partition par_02 values less than(20000,to_date('2012-12-01','yyyy-mm-dd')) tablespace TBSP_1, 13 --第一个分区part_03 14 partition par_03 values less than(maxvalue,maxvalue) tablespace TBSP_2 15 ); 16
2: 散列分区
1 SCOTT@orcl> 2 SCOTT@orcl> conn sys/oracle as sysdba; 3 Connected. 4 SYS@orcl> select tablespace_name from dba_data_files; 5 6 TABLESPACE_NAME 7 ------------------------------ 8 USERS 9 UNDOTBS1 10 SYSAUX 11 SYSTEM 12 EXAMPLE 13 TBS_TEST1 14 TBS_TEST_3 15 TBS_TEST4 16 TBS_TEST5 17 TBS_BIG_1 18 19 10 rows selected. 20 21 SYS@orcl> conn scott/scott; 22 Connected. 23 SCOTT@orcl> create table ware_retail_part3 --创建一个描述商品零售的数据表 24 2 ( 25 3 id integer primary key,--销售编号 26 4 retail_date date,--销售日期 27 5 ware_name varchar2(50)--商品名称 28 6 ) 29 7 partition by hash(id) 30 8 ( 31 9 partition par_01 tablespace TBS_TEST1, 32 10 partition par_02 tablespace TBS_TEST4 33 11 ); 34 35 Table created. 36 37 SCOTT@orcl>
1 2 SCOTT@orcl> insert into ware_retail_part3 values(99,to_date('2018-03-22','yyyy-mm-dd'),'adfadsfas'); 3 4 1 row created. 5 6 SCOTT@orcl> select * from ware_retail_part3 partition(par_02); 7 8 ID RETAIL_DA WARE_NAME 9 ---------- --------- -------------------------------------------------- 10 99 22-MAR-18 adfadsfas 11 12 SCOTT@orcl> select * from ware_retail_part3 partition(par_01); 13 14 no rows selected 15 16 SCOTT@orcl>
1 SCOTT@orcl> conn sys/oracle as sysdba; 2 Connected. 3 SYS@orcl> select tablespace_name from dba_data_files; 4 5 TABLESPACE_NAME 6 ------------------------------ 7 USERS 8 UNDOTBS1 9 SYSAUX 10 SYSTEM 11 EXAMPLE 12 TBS_TEST1 13 TBS_TEST_3 14 TBS_TEST4 15 TBS_TEST5 16 TBS_BIG_1 17 18 10 rows selected. 19 20 SYS@orcl> conn scott/scott; 21 Connected. 22 SCOTT@orcl> create table person( 23 2 id number primary key, 24 3 name varchar2(20), 25 4 sex varchar2(2) 26 5 )partition by hash(id) 27 6 partitions 2 28 7 store in(TBS_TEST1,TBS_TEST_3); 29 30 31 Table created. 32 33 SCOTT@orcl> SCOTT@orcl>
1 SCOTT@orcl> conn sys/oracle as sysdba; 2 Connected. 3 SYS@orcl> select tablespace_name from dba_data_files; 4 5 TABLESPACE_NAME 6 ------------------------------ 7 USERS 8 UNDOTBS1 9 SYSAUX 10 SYSTEM 11 EXAMPLE 12 TBS_TEST1 13 TBS_TEST_3 14 TBS_TEST4 15 TBS_TEST5 16 TBS_BIG_1 17 18 10 rows selected. 19 20 SYS@orcl> conn scott/scott; 21 Connected. 22 SCOTT@orcl> create table goods_1 ( --//定义包含商品信息表 23 2 id number, --编号 24 3 goodname varchar2(50) --名称 25 4 )storage(initial 2048k) ---定义表分区的初始化空间大小为 2048 kb 26 5 partition by hash(id) --创建id列作为分区键的hash表分区 27 6 ( partition par1 tablespace TBS_TEST1, 28 7 partition par2 tablespace TBS_TEST_3 29 8 ); 30 31 Table created. 32 33 SCOTT@orcl>
3：列表分区
1 create table clients 2 ( 3 id integer primary key, 4 name varchar2(50), 5 province varchar2(20) 6 ) 7 partition by list(province) 8 ( 9 partition shandong values('山东省'), 10 partition guangdong values('广东省'), 11 partition yunnan values('云南省') 12 );
4：组合分区
1 2 SCOTT@orcl> conn sys/oracle as sysdba; 3 Connected. 4 SYS@orcl> select tablespace_name from dba_data_files; 5 6 TABLESPACE_NAME 7 ------------------------------ 8 USERS 9 UNDOTBS1 10 SYSAUX 11 SYSTEM 12 EXAMPLE 13 TBS_TEST1 14 TBS_TEST_3 15 TBS_TEST4 16 TBS_TEST5 17 TBS_BIG_1 18 19 10 rows selected. 20 21 SYS@orcl> conn scott/scott; 22 Connected. 23 24 SCOTT@orcl> create table person2 --创建以一个描述个人信息的表 25 2 ( 26 3 pid number primary key, --个人的编号 27 4 name varchar2(20), --姓名 28 5 sex varchar2(2) --性别 29 6 ) 30 7 partition by range(pid )--以id作为分区键创建范围分区 31 8 subpartition by hash(name)--以name列作为分区键创建hash子分区 32 9 subpartitions 2 store in(TBS_TEST_3,TBS_TEST4)--hash子分区公有两个，分别存储在两个不同的命名空间中 33 10 ( 34 11 partition par1 values less than(5000),--范围分区，id小于5000 35 12 partition par2 values less than(10000),--范围分区，id小于10000 36 13 partition par3 values less than(maxvalue)--范围分区，id不小于10000 37 14 ); 38 39 Table created. 40 41 SCOTT@orcl>
5：interval 分区
1 2 SCOTT@orcl> conn sys/oracle as sysdba; 3 Connected. 4 SYS@orcl> select tablespace_name from dba_data_files; 5 6 TABLESPACE_NAME 7 ------------------------------ 8 USERS 9 UNDOTBS1 10 SYSAUX 11 SYSTEM 12 EXAMPLE 13 TBS_TEST1 14 TBS_TEST_3 15 TBS_TEST4 16 TBS_TEST5 17 TBS_BIG_1 18 19 10 rows selected. 20 21 SYS@orcl> conn scott/scott; 22 Connected. 23 SCOTT@orcl> create table saleRecord( 24 2 id number primary key, --编号 25 3 goodsname varchar2(50),--商品名称 26 4 saledate date,--销售日期 27 5 quantity number--销售量 28 6 ) 29 7 partition by range(saledate) 30 8 interval (numtoyminterval(1,'year')) 31 9 ( 32 10 --设置分区键值日期小于2012-01-01 33 11 partition par_fist values less than (to_date('2012-01-01','yyyy-mm-dd')) 34 12 ); 35 36 Table created. 37 38 SCOTT@orcl>
四：表分区策略
五：管理表分区
1：添加表分区
1 alter table clients add partition hebei values('河北省') storage(inital 10k next 20kl) tablespace tbsp_1 nologging; 2
2: 合并分区
、
1:合并散列分区
2: 合并复合分区
1 2 SCOTT@orcl> alter table person2 modify partition par3 coalesce subpartition; 3 4 Table altered. 5 6 SCOTT@orcl>
3：删除分区
1：删除一个表分区
1 SCOTT@orcl> alter table WARE_RETAIL_PART drop partition par_04; 2 3 Table altered. 4 5 SCOTT@orcl>
2：删除有数据和全局索引的表分区
1 SCOTT@orcl> alter table WARE_RETAIL_PART drop partition par_04; 2 3 Table altered. 4 5 SCOTT@orcl> select t.*,i.index_type from user_ind_columns t,user_indexes i where t.index_name = i.index_name and t.table_name = i.table_name and t.table_name ='WARE_RETAIL_PART'; 6 7 INDEX_NAME TABLE_NAME 8 ------------------------------ ------------------------------ 9 COLUMN_NAME 10 -------------------------------------------------------------------------------- 11 COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESC INDEX_TYPE 12 --------------- ------------- ----------- ---- --------------------------- 13 SYS_C0012488 WARE_RETAIL_PART 14 ID 15 1 22 0 ASC NORMAL 16 17 18 SCOTT@orcl> select t.*,i.index_type from user_ind_columns t,user_indexes i where t.index_name = i.index_name and t.table_name = i.table_name and t.index_name ='WARE_index'; 19 20 no rows selected 21 22 SCOTT@orcl> alter index SYS_C0012488 rebuild; 23 24 Index altered. 25 26 SCOTT@orcl>
3：使用 delete 和 alter table… drop partition 语句
1 SCOTT@orcl> delete from WARE_RETAIL_PART where retail_date >=to_date('201110-01','yyyy-mm-dd') ; 2 3 0 rows deleted. 4 5 SCOTT@orcl> alater table ware_retail_part drop partition par_04;
4：删除 具有完整性约束的分区
4: 并入分区
1 SCOTT@orcl> conn sys/oracle as sysdba; 2 Connected. 3 SYS@orcl> select tablespace_name from dba_data_files; 4 5 TABLESPACE_NAME 6 ------------------------------ 7 USERS 8 UNDOTBS1 9 SYSAUX 10 SYSTEM 11 EXAMPLE 12 TBS_TEST1 13 TBS_TEST_3 14 TBS_TEST4 15 TBS_TEST5 16 TBS_BIG_1 17 18 10 rows selected. 19 20 SYS@orcl> conn scott/scott; 21 Connected. 22 SCOTT@orcl> select table_name from user_tables; 23 24 TABLE_NAME 25 ------------------------------ 26 DEPT 27 EMP 28 BONUS 29 SALGRADE 30 DEPT_LOG 31 GOODS 32 DDL_OPER_LOG 33 TB_TEST 34 STUDENTS 35 STUDENTS_DEMO 36 STUDENTS_DEMO1 37 38 TABLE_NAME 39 ------------------------------ 40 STUDENTS_3 41 STUDENTS_4 42 BOOKS 43 BOOKS_1 44 MEMBERS 45 WARE_RETAIL_PART 46 WARE_RETAIL_PART3 47 PERSON 48 GOODS_1 49 PERSON2 50 SALERECORD 51 52 TABLE_NAME 53 ------------------------------ 54 STUDENTS_7 55 STUDENTS_5 56 57 24 rows selected. 58 59 SCOTT@orcl> --创建表和分区 60 SCOTT@orcl> create table sales--创建一个销售记录表 61 2 ( 62 3 id number primary key,--记录编号 63 4 goodsname varchar2(10),--商品名 64 5 saledate date--销售日期 65 6 ) 66 7 partition by range(saledate)--按照日期分区 67 8 ( 68 9 --第一季度数据 69 10 partition part_sea1 values less than(to_date('2011-04-01','yyyy-mm-dd')) tablespace TBS_TEST1 , 70 11 --第二季度数据 71 12 partition part_sea2 values less than(to_date('2011-07-01','yyyy-mm-dd')) tablespace TBS_TEST_3 , 72 13 --第三季度数据 73 14 partition part_sea3 values less than(to_date('2011-10-01','yyyy-mm-dd')) tablespace TBS_TEST1 , 74 15 --第四季度数据 75 16 partition part_sea4 values less than(to_date('2012-01-01','yyyy-mm-dd')) tablespace TBS_TEST_3 76 17 ); 77 78 79 Table created. 80 81 SCOTT@orcl> SCOTT@orcl>
1 2 SCOTT@orcl> SCOTT@orcl> create index index_3_4 on sales(saledate) 3 2 local( 4 3 partition part_seal tablespace TBS_TEST1, 5 4 partition part_sea2 tablespace TBS_TEST_3, 6 5 partition part_sea3 tablespace TBS_TEST1, 7 6 partition part_sea4 tablespace TBS_TEST_3 8 7 ); 9 10 Index created. 11 12 SCOTT@orcl>
1 2 SCOTT@orcl> --并入分区 3 SCOTT@orcl> alter table sales merge partitions part_sea3,part_sea4 into partition part_sea4; 4 5 6 Table altered. 7 8 SCOTT@orcl> SCOTT@orcl>
1 SCOTT@orcl> SCOTT@orcl> --重建局部索引 2 SCOTT@orcl> alter table sales modify partition part_sea4 rebuild unusable local indexes; 3 4 Table altered. 5 6 SCOTT@orcl>
六：创建索引分区
1：索引分区概述
2：本地索引分区
1:准备好需要的表空间
1 SYS@orcl> create tablespace ts_1 datafile '/u01/app/oracle/oradata/orcl/ts1.dbf' size 10m extent management local autoallocate ; 2 3 Tablespace created. 4 5 SYS@orcl> create tablespace ts_2 datafile '/u01/app/oracle/oradata/orcl/ts2.dbf' size 10m extent management local autoallocate ; 6 7 Tablespace created. 8 9 SYS@orcl> create tablespace ts_3 datafile '/u01/app/oracle/oradata/orcl/ts3.dbf' size 10m extent management local autoallocate ; 10 11 Tablespace created. 12 13 SYS@orcl>2：创建一个储存学生成绩的分区表studentgrade,该表共有3个分区，分别位于表空间ts_1/ts_2/ts_3；
1 2 SYS@orcl> conn scott/scott 3 Connected. 4 SCOTT@orcl> create table studentgrade( 5 2 id number primary key, 6 3 name varchar2(10), 7 4 subject varchar2(10), 8 5 grade number 9 6 ) 10 7 partition by range(grade) 11 8 ( 12 9 partition par_nopass values less than (60) tablespace ts_1, 13 10 partition par_pass values less than(70) tablespace ts_2, 14 11 partition par_good values less than(maxvalue) tablespace ts_3 15 12 ); 16 17 Table created. 18 19 SCOTT@orcl>3：根据表分区创建本地索引分区，与表分区一样。索引分区也是3个分区（p1,p2,p3）
1 SCOTT@orcl> create index grade_index on studentgrade(grade) local 2 2 ( 3 3 partition p1 tablespace ts_1, 4 4 partition p2 tablespace ts_2, 5 5 partition p3 tablespace ts_3 6 6 ); 7 8 Index created. 9
4: 通过 dba_ind_partitions 视图来进行查看索引分区信息
1 SCOTT@orcl> conn sys/oracle as sysdba; 2 Connected. 3 4 SYS@orcl> desc dba_ind_partitions; 5 Name Null? Type 6 ----------------------------------------- -------- ---------------------------- 7 INDEX_OWNER VARCHAR2(30) 8 INDEX_NAME VARCHAR2(30) 9 COMPOSITE VARCHAR2(3) 10 PARTITION_NAME VARCHAR2(30) 11 SUBPARTITION_COUNT NUMBER 12 HIGH_VALUE LONG 13 HIGH_VALUE_LENGTH NUMBER 14 PARTITION_POSITION NUMBER 15 STATUS VARCHAR2(8) 16 TABLESPACE_NAME VARCHAR2(30) 17 PCT_FREE NUMBER 18 INI_TRANS NUMBER 19 MAX_TRANS NUMBER 20 INITIAL_EXTENT NUMBER 21 NEXT_EXTENT NUMBER 22 MIN_EXTENT NUMBER 23 MAX_EXTENT NUMBER 24 MAX_SIZE NUMBER 25 PCT_INCREASE NUMBER 26 FREELISTS NUMBER 27 FREELIST_GROUPS NUMBER 28 LOGGING VARCHAR2(7) 29 COMPRESSION VARCHAR2(8) 30 BLEVEL NUMBER 31 LEAF_BLOCKS NUMBER 32 DISTINCT_KEYS NUMBER 33 AVG_LEAF_BLOCKS_PER_KEY NUMBER 34 AVG_DATA_BLOCKS_PER_KEY NUMBER 35 CLUSTERING_FACTOR NUMBER 36 NUM_ROWS NUMBER 37 SAMPLE_SIZE NUMBER 38 LAST_ANALYZED DATE 39 BUFFER_POOL VARCHAR2(7) 40 FLASH_CACHE VARCHAR2(7) 41 CELL_FLASH_CACHE VARCHAR2(7) 42 USER_STATS VARCHAR2(3) 43 PCT_DIRECT_ACCESS NUMBER 44 GLOBAL_STATS VARCHAR2(3) 45 DOMIDX_OPSTATUS VARCHAR2(6) 46 PARAMETERS VARCHAR2(1000) 47 INTERVAL VARCHAR2(3) 48 SEGMENT_CREATED VARCHAR2(3)1 2 SYS@orcl> select index_owner,index_name ,partition_name,tablespace_name from dba_ind_partitions where INDEX_NAME='GRADE_INDEX'; 3 4 INDEX_OWNER INDEX_NAME 5 ------------------------------ ------------------------------ 6 PARTITION_NAME TABLESPACE_NAME 7 ------------------------------ ------------------------------ 8 SCOTT GRADE_INDEX 9 P1 TS_1 10 11 SCOTT GRADE_INDEX 12 P2 TS_2 13 14 SCOTT GRADE_INDEX 15 P3 TS_3 16 17 18 SYS@orcl> INDEX_NAME
3： 全局索引分区
1 2 SCOTT@orcl> create index index_saleprice on BOOKS(saleprice) global partition by range(saleprice) ( partition p1 values less than(30),partition p2 values less than(50),partition p3 values less than(maxvalue) ); 3 4 Index created.
1 SCOTT@orcl> create index index_ISBN on books(ISBN) global partition by hash(ISBN); 2 3 Index created. 4 5 SCOTT@orcl>
七：管理索引分区
1：索引分区管理的操作列表
2：索引分区管理的实际操作
1：删除索引分区
1 2 SCOTT@orcl> alter index index_saleprice drop partition p2; 3 4 Index altered.
1 SCOTT@orcl> alter index index_saleprice drop partition p1; 2 3 Index altered. 4 SCOTT@orcl> alter index index_saleprice drop partition p1; 5 6 Index altered.2：重命名索引分区
1 2 SCOTT@orcl> alter index index_saleprice rename partition p3 to p_new; 3 4 Index altered. 5 6 SCOTT@orcl>
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------