Oracle表分区学习笔记

Oracle表分区
自从oracle8i 开始可以把一个表分割为多个小的部分,这样可以对oracle的性能优化带来很大的好处~
例如：改善表的查询性能,更加容易管理表数据,备份和恢复操作更方便
在oracle 中分区表 分为好几种的(范围分区,散列分区,子分区,列表分区,索引分区)下面我们来慢慢介绍
现在我们来建立一个[范围分区]

create table range_test (range_key date,data varchar(20)) partition by range(range_key)
(
partition part_1 values less than
         (to_date('2007-02-01','yyyy-mm-dd')),
partition part_2 values less than
          (to_date('2007-03-01','yyyy-mm-dd')),      
partition part_3 values less than
         (to_date('2007-04-01','yyyy-mm-dd')),
partition part_4 values less than
          (to_date('2007-05-01','yyyy-mm-dd'))
   )

create table RangeTable(
id int primary key,
name varchar(20),
grade int
)
partition by rang(grade)
(
partition part1 values less then(50) tablespace Part1_tb,
partition part2 values less then(MAXVALUE) tablespace Part2_tb
);

如果grade的值小于50的话 就把记录放到名为part1的分区当中,part1分区将被存储在Part1_tb表空间中
其他的就放在part2中 MAXVALUE是oracle的关键字 表示最大值
[散列分区]
create table HashTable(
id int primary key,
name varchar(20),
grade int
)
/*有两种方式,1就是指定分区数目和所使用的表空间,2指定以命名的分区*/
partition by hash(grade)
partitions 10 -- 指定分区的数目
store in(Part1_tb,Part2_tb,Part3_tb) --如果指定的分区数目比表空间多,分区会以循环方式分配到表空间
/*------------------------------------*/
partition by hash(grade)--这种方式就是 指定以命名的分区
(
partition part1 tablespace Part1_tb,
partition part2 tablespace Part2_tb
);
[子分区]即是分区的分区
create table ChildTable(
id int primary key,
name varchar(20),
grade int
)
partition by rang(grade)
subpartition by hash(grade)
partitions 5
(
partition part1 values less then(30) tablespace Part1_tb,
partition part2 values less then(60) tablespace Part2_tb,
partition part3 values less then(MAXVALUE) tablespace Part3_tb
);
[列表分区]告诉oracle所有可能的值
create table ListTable(
id int primary key,
name varchar(20),
area varchar(10)
)
partition by list(area)
(
partition part1 values('guangdong','beijing') tablespace Part1_tb,
partition part2 values('shanghai','nanjing') tablespace Part2_tb
);
[索引分区]索引也可以按照和表进行分区时使用的相同的值范围来分区
create index IndexTable_index
on IndexTable(name)
local
(
partition part1 tablespace Part1_tb,
partition part2 tablespace Part2_tb
)
--local 告诉oracle表 IndexTable的每一个分区建立一个独立的索引
create index IndexTable_index
on IndexTable(name)
global;
--global为全局索引 全局索引可以包含多个分区的值 局部索引比全局索引容易管理,而全局索引比较快
注意:不能为散列分区 或者 子分区创建全局索引
查询某一个表分区索引分区：

注意： 对某个字段已做了分区了，是不允许再建立索引分区的。这一点要非常注意。

全局索引建立时global子句允许指定索引的范围值，这个范围值为索引字段的范围值：

create index idx_part_id on niegc_part(part_dec)

global partition by range(part_dec)

(

partition idx_1 values less than('1000') tablespace dw,

partition idx_2 values less than(maxvalue) tablespace dw

)

局部索引分区的建立：（注意：表必须存在分区，此分区的个数必须和分区表的分区个数一样，不然是建立不起来的）

create index idx_part_id on niegc_part(part_dec)

local

(

partition idx_1 tablespace dw1,

partition idx_2 tablespace dw2

)

五、分区维护：(只对范围分区)

（1）、增加一个分区：分区范围只能往上增，不能增加一个少于原有的分区：

alter table niegc_part add partition part_03 values less than(maxvalue)

（2）、合并分区：（合并后的分区必须指下最后一个大value的分区）

alter table niegc_part merge partitions part_02,part_03 into partition part_03

（3）、删除一个分区：

alter table niegc_part drop partition part_01

（4）分析分区表分区

exec dbms_stats.gather_table_stats('scott','rangetable','part_1')

六、总结：

需要说明的是，本文在举例说名分区表事务操作的时候，都指定了分区，因为指定了分区，系统在执行的时候则只操作该分区的记录，提高了数据处理的速度。不要指定分区直接操作数据也是可以的。在分区表上建索引及多索引的使用和非分区表一样。此外，因为在维护分区的时候可能对分区的索引会产生一定的影响，可能需要在维护之后重建索引，相关内容请google分区表索引部分的文档

Oracle分区命令集

-- Create table(创建分区表)
create table BILL_MONTHFEE_ZERO
(
SERV_ID             NUMBER(20) not null,
BILLING_CYCLE_MONTH NUMBER(6) not null,
DATE_TYPE           NUMBER(1),
ACC_NBR             VARCHAR2(80)
)
partition by range (BILLING_CYCLE_MONTH)
(partition p_200407 values less than (200407)
    tablespace TS_ZIKEN
      storage(initial 100k next 100k minextents 1 maxextents unlimited pctincrease 0),
   partition p_200408 values less than (200408)
    tablespace TS_ZIKEN
      storage(initial 100k next 100k minextents 1 maxextents unlimited pctincrease 0))
      ;
create index idx_bill_monthfee_zero_idx01 on bill_monthfee_zero(billing_cycle_month)
tablespace TS_ZIKEN_idx
storage(initial 100k next 100k minextents 1 maxextents unlimited pctincrease 0) nologging;
grant all on bill_monthfee_zero to dxsq_dev;

--增加分区表

alter table BILL_MONTHFEE_ZERO add Partition p_200409
values less than (200409) tablespace ts_ziken;

--删除一分区
alter table part_tbl drop Partition part_tbl_08;

--将一个分区分为两个分区
alter table bill_monthfee_zero split Partition p_200409 at (200409)
into (Partition p_200409_1 tablespace ts_ziken,
Partition p_200409_2 tablespace ts_ziken_idx);

--合并分区
ALTERTABLE bill_monthfee_zero
   MERGE PARTITIONS p_200408, p_200409 INTOPARTITION p_all

--将分区改名

altertable bill_monthfee_zero rename Partition p_200408 to p_fee_200408

--将分区改表空间

altertable bill_monthfee_zero move Partition p_200409
tablespace ts_ziken_01 nologging

--查询特定分区
select count(*) from BILL_MONTHFEE_ZERO partition (p_200407);

--添加数据
insert into bill_monthfee_zero select * from bill_monthfee_zero partition (p_200407)

--分区表的导出

userid=dxsq/teledoone@jndxsq154
buffer=102400
tables=bill_monthfee:P_200401,
file=E:"exp_para"exp_dxsq_tables.dmp
log=E:"exp_para"exp_dxsq_tables.log

技巧：

删除表中一个字段：

alter table bill_monthfee_zero set unused column date_type;

添加一个字段：alter table bill_monthfee_zero add date_type number(1);

显示分区表信息

显示当前用户可访问的所有分区表信息﹕ALL_PART_TABLES

显示当前用户所有分区表的信息﹕USER_PART_TABLES

显示表分区信息 显示数据库所有分区表的详细分区信息﹕DBA_TAB_PARTITIONS

显示当前用户可访问的所有分区表的详细分区信息﹕ALL_TAB_PARTITIONS

显示当前用户所有分区表的详细分区信息﹕USER_TAB_PARTITIONS

显示子分区信息 显示数据库所有组合分区表的子分区信息﹕DBA_TAB_SUBPARTITIONS

显示当前用户可访问的所有组合分区表的子分区信息﹕ALL_TAB_SUBPARTITIONS

显示当前用户所有组合分区表的子分区信息﹕USER_TAB_SUBPARTITIONS

显示分区列 显示数据库所有分区表的分区列信息﹕DBA_PART_KEY_COLUMNS

显示当前用户可访问的所有分区表的分区列信息﹕ALL_PART_KEY_COLUMNS

显示当前用户所有分区表的分区列信息﹕USER_PART_KEY_COLUMNS

显示子分区列 显示数据库所有分区表的子分区列信息﹕DBA_SUBPART_KEY_COLUMNS

显示当前用户可访问的所有分区表的子分区列信息﹕ALL_SUBPART_KEY_COLUMNS

显示当前用户所有分区表的子分区列信息﹕USER_SUBPART_KEY_COLUMNS

select * from table partition(part1);

insert into range_test values (to_date('2008-3-14 14:00:00','yyyy-mm-dd hh24:mi:ss'),'hua');

select * from rant_test partition(part_1);

复合分区

　　有时候我们需要根据范围分区后，每个分区内的数据再散列地分布在几个表空间中，这样我们就要使用复合分区。复合分区是先使用范围分区，然后在每个分区内再使用散列分区的一种分区方法，如将物料交易的记录按时间分区，然后每个分区中的数据分三个子分区，将数据散列地存储在三个指定的表空间中：

SQL> create table dinya_test
2 (
3 transaction_id number primary key,
4 item_id number(8) not null,
5 item_description varchar2(300),
6 transaction_date date
7 )
8 partition by range(transaction_date)subpartition by hash(transaction_id)
9 subpartitions 3 store in (dinya_space01,dinya_space02,dinya_space03)
10 (
11 partition part_01 values less than(to_date(’2006-01-01’,’yyyy-mm-dd’)),
12 partition part_02 values less than(to_date(’2010-01-01’,’yyyy-mm-dd’)),
13 partition part_03 values less than(maxvalue)
14 );
Table created.

　　该例中，先是根据交易日期进行范围分区，然后根据交易的ID将记录散列地存储在三个表空间中。

分区表的视图

desc user_part_tables

select table_name,partitoning_type,partition_count from user_part_tables;

desc user_tab_partitions

select partition_name,high_value,tablespace_name from user_tab_partitions

sql> analyze table t_new partition(p1) validate structure;
analyze table t_new partition(p1) validate structure
*
error at line 1:
ora-14508: specified validate into table not found

这里报错了，是因为不存在invalid_rows,创建一下咯

sql> @$oracle_home/rdbms/admin/utlvalid.sql

analyze table rangetable partition(part_2) validate structure cascade;

这是对命令与工具包的一些总结

1、对于分区表，建议使用DBMS_STATS，而不是使用Analyze语句。
a) 可以并行进行，对多个用户，多个Table
b) 可以得到整个分区表的数据和单个分区的数据。
c) 可以在不同级别上Compute Statistics：单个分区，子分区，全表，所有分区
d) 可以倒出统计信息
e) 可以用户自动收集统计信息

2、DBMS_STATS的缺点
a) 不能Validate Structure
b) 不能收集CHAINED ROWS, 不能收集CLUSTER TABLE的信息，这两个仍旧需要使用Analyze语句。
c) DBMS_STATS 默认不对索引进行Analyze，因为默认Cascade是False，需要手工指定为True

3、对于oracle 9里面的External Table，Analyze不能使用，只能使用DBMS_STATS来收集信息。