创建分区表和分区索引

环境：LE01数据库

1.设置非标准块

alter system set db_16k_cache_size=80M;      设置非标准块16K缓冲区，用于存放非标准块

show parameter db_16k_cache_size

 

2、创建4个表空间，一个分区对应一个表空间

create user le01 identified by le01;

grant resource , connect ,dba to le01;

conn le01/le01

drop table t2_part;

drop index idx_t2_part;

drop tablespace part1 including contents and datafiles;

drop tablespace part2 including contents and datafiles;

drop tablespace part3 including contents and datafiles;

drop tablespace part4 including contents and datafiles;

create tablespace part1 datafile '/u01/app/oracle/oradata/LEO1/part1_01.dbf' size 30M

extent management local     

blocksize 16k;

create tablespace part2 datafile '/u01/app/oracle/oradata/LEO1/part2_01.dbf' size 30M

extent management local     

blocksize 16k;

create tablespace part3 datafile '/u01/app/oracle/oradata/LEO1/part3_01.dbf' size 30M

extent management local     

blocksize 16k;

create tablespace part4 datafile '/u01/app/oracle/oradata/LEO1/part4_01.dbf' size 30M

extent management local     

blocksize 16k;

select * from v$tablespace;

 

3、构造分区表数据

conn tsport/tsport

drop table t2 purge;

create table t2 (itemid number(10),name varchar2(10),itemdate date);

create index idx_t2 on t2(itemid);

insert into t2 values (1,'apple1',to_date('2000-02-01','yyyy-mm-dd'));

insert into t2 values (2,'apple2',to_date('2000-03-01','yyyy-mm-dd'));

insert into t2 values (3,'apple3',to_date('2002-04-01','yyyy-mm-dd'));

insert into t2 values (4,'apple4',to_date('2002-05-01','yyyy-mm-dd'));

insert into t2 values (5,'apple5',to_date('2002-06-01','yyyy-mm-dd'));

insert into t2 values (6,'apple6',to_date('2010-07-01','yyyy-mm-dd'));

insert into t2 values (7,'apple7',to_date('2010-08-01','yyyy-mm-dd'));

insert into t2 values (8,'apple8',to_date('2012-09-01','yyyy-mm-dd'));

insert into t2 values (9,'apple9',to_date('2012-10-01','yyyy-mm-dd'));

insert into t2 values (10,'apple10',to_date('2013-11-01','yyyy-mm-dd'));

commit;

select * from t2;                                插入10条记录，显示出来

4、LE01用户下创建分区表

conn le01/le01

CREATE TABLE t2_part

   PARTITION BY RANGE (itemdate)

     ( PARTITION p1 VALUES LESS THAN (to_date('2002-01-01','yyyy-mm-dd'))

        TABLESPACE part1,         

       PARTITION p2 VALUES LESS THAN (to_date('2010-01-01','yyyy-mm-dd'))

        TABLESPACE part2,        

       PARTITION p3 VALUES LESS THAN (to_date('2012-01-01','yyyy-mm-dd'))

        TABLESPACE part3,        

       PARTITION p4 VALUES LESS THAN (to_date('2013-01-01','yyyy-mm-dd'))

        TABLESPACE part4,         

       PARTITION other VALUES LESS THAN (maxvalue)

        TABLESPACE part4)        

   as select * from tsport.t2;     

查看分区表数据

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

select * from t2_part;

查看某一个分区

select * from  t2_part partition (p1);

 

5、创建hash全局分区索引

hash分区索引：均匀打散后存放数据，拿一列作hash打散，均匀分布在4个分区上，每个分区在不同表空间上的记录数都差不多，并发读并发写

conn le01/le01

注：把全局分区索引(按照name列做hash打散)均匀分成4份，每份都保存在LEOINDEX表空间里

create tablespace LEOINDEX datafile '/u01/app/oracle/oradata/LEO1/LEOINDEX.dbf' size 30M

extent management local     

blocksize 16k;

 

create unique index idx_t2_part on t2_part (name,itemid)global partition by hash (name) partitions 4 tablespace LEOINDEX parallel 4;

select index_name,index_type,table_name from user_indexes where table_name='T2_PART';

截断一个分区，需要保证全局索引可用

（1）默认情况下，增加、删除分区>全局索引失效

（2）truncate  操作会影响全局索引   delete  操作不会影响全局索引

alter table t2_part truncate partition p1 update global indexes;

select * from t2_part partition (p2);

【update global indexes 这个关键字可以在操作之后重建全局索引】

检查全局索引是否有效

select index_name,status,partitioned from dba_indexes where table_name='T2_PART';

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29330852/viewspace-1078332/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29330852/viewspace-1078332/