Oracle分区表详解

/*****************************************************
创建分区表


在大型的企业应用或企业级的数据库应用中，要处理的数
据量通常可以达到几十到几百GB，有的甚至可以到TB级。
虽然存储介质和数据处理技术的发展也很快，但是仍然不
能满足用户的需求，为了使用户的大量的数据在读写操作
和查询中速度更快，Oracle提供了对表和索引进行分区的
技术，以改善大型应用系统的性能 


使用分区的优点


增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用；
维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可；
均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能；
改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度 


分区的方法


范围分区
HASH分区（散列分区）
列表分区
复合分区（范围＋HASH）（范围＋列表）




*****************************************************/














CREATE TABLESPACE TEST1 DATAFILE 'C:\TEMP\TEST1.DBF' SIZE 10M;
CREATE TABLESPACE TEST2 DATAFILE 'C:\TEMP\TEST2.DBF' SIZE 10M;
CREATE TABLESPACE TEST3 DATAFILE 'C:\TEMP\TEST3.DBF' SIZE 10M; 




/*范围分区*/
Create table material_test(
    transaction_id number primary key,
    Item_id number(8) not null,
    Item_description varchar2(300),
    Transaction_date date not null
)
Partition by range(transaction_id)(
    partition part_01 values less than(30000000) tablespace test1,
    partition part_02 values less than(60000000) tablespace test2,
    partition part_03 values less than(maxvalue) tablespace test3
);


Create table material_test1
(
    transaction_id number primary key,
    Item_id number(8) not null,
    Item_description varchar2(300),
    Transaction_date date not null
)
Partition by range(transaction_date)(
    partition part_01 values less than(to_date('2006-01-01','yyyy-mm-dd')) tablespace test1,
    partition part_02 values less than(to_date('2010-01-01','yyyy-mm-dd')) tablespace test2,
    partition part_03 values less than(maxvalue) tablespace test3
);


/*HASH分区（散列分区）*/
Create table material_test2
(
    transaction_id number primary key,
    Item_id number(8) not null,
    Item_description varchar2(300),
    Transaction_date date not null
)
Partition by hash(transaction_id)(
    partition part_01 tablespace test1,
    partition part_02 tablespace test2,
    partition part_03 tablespace test3
);




/*列表分区*/
Create table material_test3
(
    transaction_id number primary key,
    Item_id number(8) not null,
    Item_description varchar2(300),
    Transaction_date date not null,
    city varchar2(100)
)
Partition by list(city)
(
    partition part_01 values('北京') tablespace test1,
    partition part_02 values('上海') tablespace test2,
    partition part_03 values(default) tablespace test3
);


/*复合分区
将物料交易的记录按时间分区，然后每个分区中的数据分3个子分区，将数据散列的存储在3个指定的表空间中。
*/
Create table material_test4
(
    transaction_id number primary key,
    Item_id number(8) not null,
    Item_description varchar2(300),
    Transaction_date date not null
)
Partition by range(transaction_date) 
    subpartition by hash(transaction_id)
    subpartitions 3 store in (test1,test2,test3)
    (
         partition part_01 values less than(to_date('2006-01-01','yyyy-mm-dd')),
         partition part_02 values less than(to_date('2010-01-01','yyyy-mm-dd')),
         partition part_03 values less than(maxvalue)
    );


/*分区表操作 */
insert into material_test1 values(1,12,'BOOKS',sysdate); 
insert into material_test1 values(2,12, 'BOOKS',sysdate+30); 
insert into material_test1 values(3,12, 'BOOKS',to_date('2006-05-30','yyyy-mm-dd')); 
insert into material_test1 values(4,12, 'BOOKS',to_date('2007-06-23','yyyy-mm-dd')); 
insert into material_test1 values(5,12, 'BOOKS',to_date('2011-02-26','yyyy-mm-dd')); 
insert into material_test1 values(6,12, 'BOOKS',to_date('2011-04-30','yyyy-mm-dd')); 
commit;


select * from material_test1 partition(part_01); 
select * from material_test1 partition(part_02); 
select * from material_test1 partition(part_03);
select * from material_test1;


update material_test1 partition(part_01) t 
set 
    t.item_description='DESK'
where 
    t.transaction_id=1;  
commit; 
/*
这里将第一个分区中的交易ID=1的记录中的item_description字段更新为“DESK”，
可以看到已经成功更新了一条记录。但是当更新的时候指定了分区，而根据查询的
记录不在该分区中时，将不会更新数据
*/


delete from 
    material_test1  partition(part_02) t 
where 
    t.transaction_id=4;
commit; 
/*
上面例子删除了第二个分区part_02中的交易记录ID为4的一条记录，和更新数据
相同，如果指定了分区，而条件中的数据又不在该分区中时，将不会删除任何数据。
*/


/*********************************************
分区表索引的使用 


分区表和一般表一样可以建立索引，分区表可以创建局部索引和全局索引。
当分区中出现许多事务并且要保证所有分区中的数据记录的唯一性时采用
全局索引。 
*********************************************/


/*局部索引分区的建立 */
create index material_test1 on material_test1(item_id) 
 local 
 ( 
 partition idx_1 tablespace test1, 
 partition idx_2 tablespace test2, 
 partition idx_3 tablespace test3 
 ); 
commit;


/*默认索引分区的建立*/
drop index material_test1;
create index material_test1 on material_test1(item_id);




/***************************************************
分区表的管理
***************************************************/


/*增加分区*/
alter table 
    material_test1
add partition part_04 
        values less than(to_date('2012-01-01','yyyy-mm-dd'))
    tablespace test3;
--无法执行


/*合并分区*/
alter table 
    material_test
merge partitions 
    part_01,
    part_02
into partition 
    part_02;


/*
注意：不能合并到前一个分区，因为前一个分区指定的数据少
*/


alter table material_test
drop partition part_01;