oracle关于分区,关于oracle分区技术初了解 -电脑资料

一、 分区类型

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.