一、 分区类型
1. 范围分区(Range Partitioning)
适用于 连续/按时间排序的数据
2. 散列分区(Hash Partitioning)
适用于 不连续/数据记录固定的数据
3. 组合分区 Range-Hash
Range-List
4. 列表分区 List Partitioning
适用于对不连续域的数据分区
更准确的控制数据的分区存储
适用于 位置类数据
二、 分区表的维护
准备工作SYS@ORA11G>create tablespace sales_ts012 datafile'/u01/app/oracle/oradata/ORA11G/sales_ts01_01.dbf' size 10m;Tablespace created.SYS@ORA11G>create tablespace sales_ts022 datafile'/u01/app/oracle/oradata/ORA11G/sales_ts02_01.dbf' size 10m;Tablespace created.SYS@ORA11G>create tablespace sales_ts032 datafile'/u01/app/oracle/oradata/ORA11G/sales_ts03_01.dbf' size 10m;Tablespace created.SYS@ORA11G>create tablespace sales_ts042 datafile'/u01/app/oracle/oradata/ORA11G/sales_ts04_01.dbf' size 10m;Tablespace created.SYS@ORA11G>create tablespace sales_ts052 datafile'/u01/app/oracle/oradata/ORA11G/sales_ts05_01.dbf' size 10m;Tablespace created.SYS@ORA11G>create tablespace sales_ts062 datafile'/u01/app/oracle/oradata/ORA11G/sales_ts06_01.dbf' size 10m;Tablespace created.SYS@ORA11G>SYS@ORA11G>SYS@ORA11G>SYS@ORA11G>conn tyger/tygerConnected.TYGER@ORA11G>TYGER@ORA11G>TYGER@ORA11G>CREATE TABLE SALES(PROD_ID NUMBER,CUST_ID NUMBER,TIME_ID DATE,CHANNEL_ID NUMBER,PROMO_ID NUMBER,QUANTITY_SOLD NUMBER(10,2),AMOUNT_SOLD NUMBER(10,2))PARTITION BY RANGE(TIME_ID)(PARTITION sales01 values less than ('01-Feb-2004')TABLESPACE SALES_TS01,PARTITION sales02 values less than ('01-Mar-2004')TABLESPACE SALES_TS02,PARTITION sales03 values less than ('01-Apr-2004')TABLESPACE SALES_TS03,PARTITION sales04 values less than ('01-May-2004')TABLESPACE SALES_TS04,PARTITION sales05 values less than ('01-Jun-2004')TABLESPACE SALES_TS05,PARTITION sales06 values less than ('01-Jul-2004')TABLESPACE SALES_TS06); 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17Table created.TYGER@ORA11G>selectTABLE_NAME,PARTITIONING_TYPE,SUBPARTITIONING_TYPE,STATUS2 from user_part_tables;TABLE_NAME PARTITION SUBPARTIT STATUS------------------------------ --------- -----------------SALES RANGE NONE VALIDTYGER@ORA11G>col table_name for a20TYGER@ORA11G>col tablespace_name for a20TYGER@ORA11G>l1 selectTABLE_NAME,PARTITION_NAME,TABLESPACE_NAME2* fromuser_tab_partitionsTYGER@ORA11G>/TABLE_NAME PARTITION_NAME TABLESPACE_NAME-------------------- --------------------------------------------------SALES SALES01 SALES_TS01SALES SALES02 SALES_TS02SALES SALES03 SALES_TS03SALES SALES04 SALES_TS04SALES SALES05 SALES_TS05SALES SALES06 SALES_TS066 rows selected.语法:ALTERTABLE ALTER INDEXADD PARTITION -DROP PARTITIONCOALESCE PARTITION - MODIFY PARTITIONDROP PARTITION - MODIFY DEFAULT ATTRIBUTESEXCHANGE PARTITION - MODIFY PARTITION COALESCEMERGE PARTITIONS - REBUILD PARTITIONMODIFY PARTITION - RENAME PARTITIONMODIFY DEFAULT -SPLIT PARTITIONATTRIBUTES - UNUSABLEMOVE PARTITIONRENAME PARTITIONSPLIT PARTITION
2.1 删除表分区Alter table sales droppartition sales01;TYGER@ORA11G>alter table sales drop partitionsales01;Table altered.TYGER@ORA11G>selectTABLE_NAME,PARTITION_NAME,TABLESPACE_NAME2 from user_tab_partitions;TABLE_NAME PARTITION_NAME TABLESPACE_NAME-------------------- --------------------------------------------------SALES SALES02 SALES_TS02SALES SALES03 SALES_TS03SALES SALES04 SALES_TS04SALES SALES05 SALES_TS05SALES SALES06 SALES_TS06
2.2 增加表分区
增加分区的分区范围必须比当前分区的最后一个分区更高TYGER@ORA11G>alter table sales add partitionsales01 values less than ('01-Feb-2004') tablespace sales_ts01;alter table sales add partition sales01 values lessthan ('01-Feb-2004') tablespace sales_ts01*ERROR at line 1:ORA-14074: partition bound mustcollate higher than that of the last partitionTYGER@ORA11G>alter table sales add partitionsales012 values less than ('01-Aug-2004') tablespacesales_ts01;Table altered.TYGER@ORA11G>selecttable_name,partition_name,tablespace_name2 from user_tab_partitions;TABLE_NAME PARTITION_NAME TABLESPACE_NAME-------------------- --------------------------------------------------SALES SALES02 SALES_TS02SALES SALES03 SALES_TS03SALES SALES04 SALES_TS04SALES SALES05 SALES_TS05SALES SALES06 SALES_TS06SALES SALES01 SALES_TS01
2.3 合并分区
必须是相邻的范围分区
继承最大的范围边界TYGER@ORA11G>alter table sales2 merge partitions sales06,sales01 intopartition sales07;Table altered.TYGER@ORA11G>select table_name,partition_name,tablespace_name2 from user_tab_partitions;TABLE_NAME PARTITION_NAME TABLESPACE_NAME-------------------- --------------------------------------------------SALES SALES07 USERSSALES SALES02 SALES_TS02SALES SALES03 SALES_TS03SALES SALES04 SALES_TS04SALES SALES05 SALES_TS05
2.4 移动表分区
移动分区数据到另一个表空间
重新整理数据减少碎片
改变物理属性TYGER@ORA11G>alter table sales move partitionsales012 tablespace sales_ts_move;
2.5 拆分表分区
拆分表分区———一个分区变的太大,导致备份,恢复和分区性能操作花费时间太长,重新分配I/O负载,关于oracle分区技术初了解》(https://www.unjs.com)。TYGER@ORA11G>alter table sales split partition sales072 at ('01-Jul-2004') // 按哪个时间点拆分3 into (partition sales01 tablespacesales_ts01,4 partition sales06 tablespacesales_ts06);Table altered.TYGER@ORA11G>selecttable_name,partition_name,tablespace_name2 from user_tab_partitions;TABLE_NAME PARTITION_NAME TABLESPACE_NAME-------------------- --------------------------------------------------SALES SALES02 SALES_TS02SALES SALES03 SALES_TS03SALES SALES04 SALES_TS04SALES SALES05 SALES_TS05SALES SALES06 SALES_TS06SALES SALES01 SALES_TS016 rows selected.