ace tstrans read write;
select tablespace_name,status from dba_tablespaces;
二 创建分区表和分区索引
官方文档：
Administrator’s Guide ?> 17 Managing Partitioned Tables and Indexs
Data Warehousing Guide -> 5 Partitioning in Data Warehouses
场合：数据量很大，要求检索范围小，效率高
优点：DBA管理灵活性高，基于分区删除、插入
缺点：跨分区检索效率很低,但可创建一个全局索引global来改善性能
全局索引global：默认删除分区，则全局索引失效，一个分区表只有一个全局索引
本地索引local：一个分区一个索引，有几个分区就有几个索引
要求：我们创建一个分区表，共有4个分区，每个分区独立使用一个表空间
使用非标准块，块大小16k
1.设置非标准块
ocm01
alter system set db_16k_cache_size=80M; 设置非标准块16K缓冲区，用于存放非标准块
show parameter db_16k_cache_size
作用：用于减少物理I/O读写次数，原本读100次可以完成的数据，现在读50次就完成了
create user ocm01 identified by ocm01;
grant dba to ocm01;
2.创建4个表空间，一个分区对应一个表空间
conn ocm01/ocm02
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/oracle/oradata/ocmdb/disk1/part1_01.dbf' size 50M
extent management local
blocksize 16k;
create tablespace part2 datafile '/u01/oracle/oradata/ocmdb/disk2/part2_01.dbf' size 50M
extent management local
blocksize 16k;
create tablespace part3 datafile '/u01/oracle/oradata/ocmdb/disk3/part3_01.dbf' size 50M
extent management local
blocksize 16k;
create tablespace part4 datafile '/u01/oracle/oradata/ocmdb/disk4/part4_01.dbf' size 50M
extent management local
blocksize 16k;
select * from v$tablespace;
2.构造分区表数据
conn tstrans/tstrans
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条记录，显示出来
3.ocm01用户下创建分区表
conn ocm01/ocm01
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 tstrans.t2;
查看分区表数据
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select * from t2_part;
查看某一个分区
select * from t2_part partition (p1);
4.创建hash全局分区索引
hash分区索引：均匀打散后存放数据，拿一列作hash打散，均匀分布在4个分区上，每个分区在不同表空间上的记录数都差不多，并发读并发写
官方文档：SQL Reference -> CREATE TABLE and CREATE INDEX
conn ocm01/ocm01
注：把全局分区索引(按照name列做has |