下面来简要介绍下范围分区,范围分区的主要优点主要如下:
1:分区表可以将表存储在多个表空间内,进而离散I/O;
2:同时各个分区维护各自的本地索引(一般使用local索引,而不是global索引);
3:编程语言
iis7站长之家语句可以根据索引进行分区范围扫描,减少查询语句所带来的一致性读;
4:可以对单个分区进行备份或者truncate,归档或者清除过期的数据;
5: 可以方便的对表的分区进行添加,删除,truncate,拆分和合并操作
一:创建一张分区表,分区的条件是以销售日期来界定,同时分区的索引为本地索引,每个分区的对应一个单独的表空间,基于离散I/O和方便管理的双重需要
SQL> create table sale_data
2 (sale_id number(5), salesman_name varchar2(30),sales_date date)
3 partition by range(sales_date)
4 (
5 partition sales_01 values less than (to_date('01/02/2012','DD/MM/YYYY')) tablespace tbs_sale01,
6 partition sales_02 values less than (to_date('01/03/2012','DD/MM/YYYY')) tablespace tbs_sale02,
7 partition sales_03 values less than (to_date('01/04/2012','DD/MM/YYYY')) tablespace tbs_sale03,
8 partition sales_04 values less than (to_date('01/05/2012','DD/MM/YYYY')) tablespace tbs_sale04,
9 partition sales_05 values less than (to_date('01/06/2012','DD/MM/YYYY')) tablespace tbs_sale05,
10 partition sales_06 values less than (to_date('01/07/2012','DD/MM/YYYY')) tablespace tbs_sale06,
11 partition sales_07 values less than (to_date('01/08/2012','DD/MM/YYYY')) tablespace tbs_sale07,
12 partition sales_08 values less than (to_date('01/09/2012','DD/MM/YYYY')) tablespace tbs_sale08,
13 partition sales_09 values less than (to_date('01/10/2012','DD/MM/YYYY')) tablespace tbs_sale09,
14 partition sales_10 values less than (to_date('01/11/2012','DD/MM/YYYY')) tablespace tbs_sale10,
15 partition sales_11 values less than (to_date('01/12/2012','DD/MM/YYYY')) tablespace tbs_sale11,
16* partition sales_12 values less than (to_date('31/12/2012','DD/MM/YYYY')) tablespace tbs_sale12)
Table created.
SQL> select owner,partitioning_type,partition_count,status from dba_part_tables where table_name='SALE_DATE';
OWNER PARTITI PARTITION_COUNT STATUS
------------------------------ ------- --------------- --------
SALE RANGE 12 VALID
SQL> create index ind_sale_data_date on sale_data(sale_id) local
2 (
3 partition sales_01 tablespace tbs_sale01,
4 partition sales_02 tablespace tbs_sale02,
5 partition sales_03 tablespace tbs_sale03,
6 partition sales_04 tablespace tbs_sale04,
7 partition sales_05 tablespace tbs_sale05,
8 partition sales_06 tablespace tbs_sale06,
9 partition sales_07 tablespace tbs_sale07,
10 partition sales_08 tablespace tbs_sale08,
11 partition sales_09 tablespace tbs_sale09,
12 partition sales_10 tablespace tbs_sale10,
13 partition sales_11 tablespace tbs_sale11,
14* partition sales_12 tablespace tbs_sale12)
Index created.
SQL> select segment_name,partition_name,tablespace_name from user_segments where segment_name in ('SALE_DATA','IND_SALE_DATA_DATE');
SEGMENT_NAME PARTITION_NAME TABLESPACE_NAME
-------------------- ------------------------------ --------------------
SALE_DATA SALES_01 TBS_SALE01
SALE_DATA SALES_02 TBS_SALE02
SALE_DATA SALES_03 TBS_SALE03
SALE_DATA SALES_04 TBS_SALE04
SALE_DATA SALES_05 TBS_SALE05
SALE_DATA SALES_06 TBS_SALE06
SALE_DATA SALES_07 TBS_SALE07
SALE_DATA SALES_08 TBS_SALE08
SALE_DATA SALES_09 TBS_SALE09
SALE_DATA SALES_10 TBS_SALE10
SALE_DATA SALES_11 TBS_SALE11
SEGMENT_NAME PARTITION_NAME TABLESPACE_NAME
-------------------- ------------------------------ --------------------
SALE_DATA SALES_12 TBS_SALE12
IND_SALE_DATA_DATE SALES_01 TBS_SALE01
IND_SALE_DATA_DATE SALES_02 TBS_SALE02
IND_SALE_DATA_DATE SALES_03 TBS_SALE03
IND_SALE_DATA_DATE SALES_04 TBS_SALE04
IND_SALE_DATA_DATE SALES_05 TBS_SALE05
IND_SALE_DATA_DATE SALES_06 TBS_SALE06
IND_SALE_DATA_DATE SALES_07 TBS_SALE07
IND_SALE_DATA_DATE SALES_08 TBS_SALE08
IND_SALE_DATA_DATE SALES_09 TBS_SALE09
IND_SALE_DATA_DATE SALES_10 TBS_SALE10
SEGMENT_NAME PARTITION_NAME TABLESPACE_NAME
-------------------- ------------------------------ --------------------
IND_SALE_DATA_DATE SALES_11 TBS_SALE11
IND_SALE_DATA_DATE SALES_12 TBS_SALE12