oracle笔记第一篇——表分区及分区索引
来源:互联网 发布:sfda数据查询 编辑:程序博客网 时间:2024/06/14 06:07
• Range(范围)分区
• Hash(哈希)分区
• List(列表)分区
• 以及组合分区:Range-Hash,Range-List。
对于索引,需要区分创建的是全局索引,或本地索引:
l 全局索引(global index):即可以分区,也可以不分区。即可以建range分区,也可以建hash分区,即可建于分区表,又可创建于非分区表上,就是说,全局索引是完全独立的,因此它也需要我们更多的维护操作。
range分区方式,也算是最常用的分区方式,其通过某字段或几个字段的组合的值,从小到大,按照指定的范围说明进行分区,我们在INSERT数据的时候就会存储到指定的分区中。
List分区方式,一般是在range基础上做的二级分区较多,是一种列举方式进行分区,一般讲某些地区、状态或指定规则的编码等进行划分。
Hash分区方式,它没有固定的规则,由ORACLE管理,只需要将值INSERT进去,ORACLE会自动去根据一套HASH算法去划分分区,只需要告诉ORACLE要分几个区即可。
WHEN
一、When使用Range分区
Range分区呢是应用范围比较广的表分区方式,它是以列的值的范围来做为分区的划分条件,将记录存放到列值所在的range分区中,比如按照 时间划分,2008年1季度的数据放到a分区,08年2季度的数据放到b分区,因此在创建的时候呢,需要你指定基于的列,以及分区的范围值,如果某些记录 暂无法预测范围,可以创建maxvalue分区,所有不在指定范围内的记录都会被存储到maxvalue所在分区中,并且支持指定多列做为依赖列,后面在 讲how的时候会详细谈到。
二、When使用Hash分区
通常呢,对于那些无法有效划分范围的表,可以使用hash分区,这样对于提高性能还是会有一定的帮助。hash分区会将表中的数据平均分配到你 指定的几个分区中,列所在分区是依据分区列的hash值自动分配,因此你并不能控制也不知道哪条记录会被放到哪个分区中,hash分区也可以支持多个依赖 列。
三、When使用List分区
List分区与range分区和hash分区都有类似之处,该分区与range分区类似的是也需要你指定列的值,但这又不同与range分区的 范围式列值---其分区值必须明确指定,也不同与hash分区---通过明确指定分区值,你能控制记录存储在哪个分区。它的分区列只能有一个,而不能像 range或者hash分区那样同时指定多个列做为分区依赖列,不过呢,它的单个分区对应值可以是多个。
你在分区时必须确定分区列可能存在的值,一旦插入的列值不在分区范围内,则插入/更新就会失败,因此通常建议使用list分区时,要创建一个default分区存储那些不在指定范围内的记录,类似range分区中的maxvalue分区。
四、When使用组合分区
如果某表按照某列分区之后,仍然较大,或者是一些其它的需求,还可以通过分区内再建子分区的方式将分区再分区,即组合分区的方式。
组合分区呢在10g中有两种:range-hash,range-list。注意顺序哟,根分区只能是range分区,子分区可以是hash分区或list分区。
下面分别演示不同分区方式的表和索引的创建:
1、创建range分区
语法如下,需要我们指定的有:
l column:分区依赖列(如果是多个,以逗号分隔);
l partition:分区名称;
l values less than:后跟分区范围值(如果依赖列有多个,范围对应值也应是多个,中间以逗号分隔);
l tablespace_clause:分区的存储属性,例如所在表空间等属性(可为空),默认继承基表所在表空间的属性。
① 创建一个标准的range分区表:
JSSWEB> create table t_partition_range (id number,name varchar2(50))
partition by range(id)(
partition t_range_p1 values less than (10) tablespace tbspart01,
partition t_range_p2 values less than (20) tablespace tbspart02,
partition t_range_p3 values less than (30) tablespace tbspart03,
partition t_range_pmax values less than (maxvalue) tablespace tbspart04
);
表已创建。
要查询创建分区的信息,可以通过查询user_part_tables,user_tab_partitions两个数据字典(索引分区、组织分区等信息也有对应的数据字典,后续示例会逐步提及)。
user_part_tables:记录分区的表的信息;
user_tab_partitions:记录表的分区的信息。
例如:
JSSWEB> select table_name,partitioning_type,partition_count
From user_part_tables where table_name='T_PARTITION_RANGE';
JSSWEB> select partition_name,high_value,tablespace_name
from user_tab_partitions where table_name='T_PARTITION_RANGE'
order by partition_position;
语法看起来比range复杂,其实使用起来比range更简单,这里需要我们指定的有:
l column:分区依赖列(支持多个,中间以逗号分隔);
l partition:指定分区,有两种方式:
n 直接指定分区名,分区所在表空间等信息
n 只指定分区数量,和可供使用的表空间。
2、创建hash分区
JSSWEB> create table t_partition_hash (id number,name varchar2(50))
2 partition by hash(id)(
3 partition t_hash_p1 tablespace tbspart01,
4 partition t_hash_p2 tablespace tbspart02,
5 partition t_hash_p3 tablespace tbspart03);
表已创建。
要实现同样效果,你还可以这样:
JSSWEB> create table t_partition_hash2 (id number,name varchar2(50))
2 partition by hash(id)
3 partitions 3 store in(tbspart01,tbspart02,tbspart03);
表已创建。
这就是上面说的,直接指定分区数量和可供使用的表空间。
提示:这里分区数量和可供使用的表空间数量之间没有直接对应关系。分区数并不一定要等于表空间数。
要查询表的分区信息,仍然是通过user_part_tables,user_tab_partitions两个数据字典,这里不再举例。
① Global索引hash分区
Hash分区索引的子句与hash分区表的创建子句完全相同,例如:
JSSWEB> create index idx_part_hash_id on t_partition_hash(id)
2 global partition by hash(id)
3 partitions 3 store in(tbspart01,tbspart02,tbspart03);
索引已创建。
查询索引的分区信息也仍是通过user_part_indexes、user_ind_partitions两个数据字典,不再举例。
② 创建Local索引
在前面学习range分区时,我们已经对Local索引的特性做了非常清晰的概述,因此这里也不再举例,如有疑问,建议再仔细复习range分区的相关示例,如果还有疑问,当面问我好了:)
综上:
Ø 对于global索引分区而言,在10g中只能支持range分区和hash分区,因此后续示例中不会再提及。
Ø 对于local索引分区而言,其分区形式完全依赖于索引所在表的分区形式,不管从创建语法还是理解难度均无技术含量,因此后续也不再提供示例。
Ø 注意,在创建索引时如果不显式指定global或local,则默认是global。
2.1、对比索引方式:
一般使用LOCAL索引较为方便,而且维护代价较低,并且LOCAL索引是在分区的基础上去创建索引,类似于在一个子表内部去创建索引,这样开销主要是区 分分区上,很规范的管理起来,在OLAP系统中应用很广泛;而相对的GLOBAL索引是全局类型的索引,根据实际情况可以调整分区的类别,而并非按照分区 结构一一定义,相对维护代价较高一些,在OLTP环境用得相对较多,这里所谓OLTP和OLAP也是相对的,不是特殊的项目,没有绝对的划分概念,在应用 过程中依据实际情况而定,来提高整体的运行性能。
3、常用视图:
1、查询当前用户下有哪些是分区表:
SELECT * FROM USER_PART_TABLES;
2、查询当前用户下有哪些分区索引:
SELECT * FROM USER_PART_INDEXES;
3、查询当前用户下分区索引的分区信息:
SELECT * FROM USER_IND_PARTITIONS T
WHERE T.INDEX_NAME=?
4、查询当前用户下分区表的分区信息:
SELECT * FROM USER_TAB_PARTITIONS T
WHERE T.TABLE_NAME=?;
5、查询某分区下的数据量:
SELECT COUNT(*) FROM TABLE_PARTITION PARTITION(TAB_PARTOTION_01);
6、查询索引、表上在那些列上创建了分区:
SELECT * FROM USER_PART_KEY_COLUMNS;
7、查询某用户下二级分区的信息(只有创建了二级分区才有数据):
SELECT * FROM USER_TAB_SUBPARTITIONS;
5、维护操作:
5.1、删除分区
ALTER TABLE TABLE_PARTITION DROP PARTITION TAB_PARTOTION_03;
如果是全局索引,因为全局索引的分区结构和表可以不一致,若不一致的情况下,会导致整个全局索引失效,在删除分区的时候,语句修改为:
ALTER TABLE TABLE_PARTITION DROP PARTITION TAB_PARTOTION_03 UPDATE GLOBAL INDEXES;
5.2、分区合并(从中间删除掉一个分区,或者两个分区需要合并后减少分区数量)
合并分区和删除中间的RANGE有点像,但是合并分区是不会删除数据的,对于LIST、HASH分区也是和RANGE分区不一样的,其语法为:
ALTER TABLE TABLE_PARTITION MERGE PARTITIONS TAB_PARTOTION_01,TAB_PARTOTION_02 INTO PARTITION MERGED_PARTITION;
5.3、分隔分区(一般分区从扩展分区从分隔)
ALTER TABLE TABLE_PARTITION SPLIT PARTITION TAB_PARTOTION_OTHERE AT(2500000)
INTO (PARTITION TAB_PARTOTION_05,PARTITION TAB_PARTOTION_OTHERE);
5.4、创建新的分区(分区数据若不能提供范围,则插入时会报错,需要增加分区来扩大范围)
一般有扩展分区的是都是用分隔的方式,若上述创建表时没有创建TAB_PARTOTION_OTHER分区时,在插入数据较大时(按照上述建立规则,超过1800000就应该创建新的分区来存储),就可以创建新的分区,如:
为了试验,我们将扩展分区先删除掉再创建新的分区(因为ORACLE要求,分区的数据不允许重叠,即按照分区字段同样的数据不能同时存储在不同的分区中):
ALTER TABLE TABLE_PARTITION DROP PARTITION TAB_PARTOTION_OTHER;
ALTER TABLE TABLE_PARTITION ADD PARTITION TAB_PARTOTION_06 VALUES LESS THAN(2500000);
在分区下创建新的子分区大致如下(RANGE分区,若为LIST或HASH分区,将创建方式修改为对应的方式即可):
ALTER TABLE <table_name> MODIFY PARTITION <partition_name> ADD SUBPARTITION <user_define_subpartition_name> VALUES LESS THAN(....);
5.5、修改分区名称(修改相关的属性信息)
ALTER TABLE TABLE_PARTITION RENAME PARTITION MERGED_PARTITION TO MERGED_PARTITION02;
5.6、交换分区(快速交换数据,其实是交换段名称指针)
首先创建一个交换表,和原表结构相同,如果有数据,必须符合所交换对应分区的条件:
CREATE TABLE TABLE_PARTITION_2
AS SELECT * FROM TABLE_PARTITION WHERE 1=2;
然后将第一个分区的数据交换出去:
ALTER TABLE TABLE_PARTITION EXCHANGE PARTITION TAB_PARTOTION_01
WITH TABLE TABLE_PARTITION_2 INCLUDING INDEXES;
此时会发现第一个分区的数据和表TABLE_PARTITION_2做了瞬间交换,比TRUNCATE还要快,因为这个过程没有进行数据转存,只是段名称的修改过程,和实际的数据量没有关系。
如果是子分区也可以与外部的表进行交换,只需要将关键字修改为:SUBPARTITION 即可。
5.7、清空分区数据
ALTER TABLE <table_name> TRUNCATE PARTITION <partition_name>;
ALTER TABLE <table_name> TRUNCATE subpartition <subpartition_name>;
6、回归总结:何时建分区,分区类别,索引,如何对应SQL
1、创建时机
上述已经说明,2G以上的表,ORACLE推荐创建分区。
分区的方式根据实际情况而定,才能提高整体性能。
分区的字段一定要是经常用以提取数据的字段,否则会在提取过程中导致遍历多个分区,这样比没有分区还要慢。
分区字段要选择合适,数据较为均匀分布到各个分区,不要太多也不要太少,而且根据分区字段可以很快定位到分区范围。
一般情况下,尽量然业务操作在同一个分区内部完成。
2、分区类别
分区主要有RANGE、LIST、HASH;
RANGE通过值的范围分区,也是最常用的分区,这种分区注意在一种变长数字字符串中,很多人会导致认为是数字类型,而按照数字区分区,这样会分布十分不均匀的现象发生。
LIST是列举方式进行分区,一般作为二级分区而存在(当然也可以自己分区,ORACLE 11G后在分区上也可以作为主分区而存在),在RANGE基础上,若数据需要继续分区,并且在RANGE基础上数据量较为固定,只是较大,可以按照一定规则进一步分区。
HASH只指定分区个数,分区细节由ORACLE完成,增加HASH分区可以重新分布数据。
注意:分区字段不能使用函数转换后在分区,如,将某数字字符串字段,先TO_NUMER(COL_NAME)后分区。
3、索引类别
GLOBAL不分区索引一般不太推荐,因为是用一颗大的索引树来映射一个表,这个过程,这样速度不见得比不分区快。
GLOBAL分区索引,查找数据若通过要通过索引,是先定位了索引内部的分区,然后在这个分区索引中找到ROWID,然后回表提取数据。
LOCAL索引是和分区的个数逐个对应的,可以说先定位分区表的分区也可以说先定位索引的分区,因为他们是一一对应的,找到对应分区后,分区内部索引数据集合。
4、对应应用
分区表、索引、分区索引,要利用其性能优势,最基本就是要提取数据时,要通过它首先将数据的范围缩小到一个即使做全盘扫描也不会太慢的情况。
所以SQL一定要有分区上的这个字段的一个WHERE条件,将数据迅速定位到分区内部,而且尽量定位到一个分区里面(这个和创建分区的规则有关系)。
建立分区本身不提要性能,要用好才可提高性能,在必要的RAC集群中,若存在多分区提取数据,适当采用并行提取可以提高提取的速度。
对于索引部分,这里也只提到分区索引的创建方式以及常见索引的维护方式,对于索引原理理解后会更容易认识到提取数据时的技巧。
- oracle笔记第一篇——表分区及分区索引
- oracle笔记整理9——性能调优之索引、表分区、索引分区
- oracle笔记整理——性能调优之索引、表分区、索引分区
- Oracle表分区、索引分区
- Oracle分区表及分区索引
- Oracle分区表及分区索引
- Oracle表分区和索引分区汇总
- Oracle表分区和索引分区汇总
- Oracle表分区和索引分区汇总
- Oracle表分区和索引分区
- Oracle表分区和索引分区汇总
- ORACLE 表分区与分区索引
- Oracle表分区和索引分区汇总
- Oracle表分区和索引分区汇总
- Oracle 表分区与索引分区
- Oracle-测试创建反向索引及表分区和索引分区
- Oracle表中分区及子分区
- 索引——分区索引
- android ble开发整理
- Android的Usb设备的监听(Dev)外设端口的判定以及耳机的插拔
- zookeeper难以理解易混淆的几点
- zookeeper的两种分布式锁的源代码点评
- zookeeper的进程锁,多线程及负载原理的理解
- oracle笔记第一篇——表分区及分区索引
- Execute框架
- css hacker
- js的面向对象使用,传参的新方式,时间格式的传递
- php 基本语法
- 深入浅出nodejs学习笔记--第四章 异步编程
- 线程池满了如何处理及spring参数设置:
- volatile 和 synchronized区别
- .NET- 可以Load元素以数字开头的XML吗?