网站备案信息可以更改吗,织梦网做网站过程,松江泗泾网站建设,提高wordpress 权重关于分区表和分区索引(About Partitioned Tables and Indexes)对于10gR2而言#xff0c;基本上可以分成几类#xff1a;
? Range(范围)分区
? Hash(哈希)分区
? List(列表)分区
? 以及组合分区#xff1a;Range-Hash,R… 关于分区表和分区索引(About Partitioned Tables and Indexes)对于10gR2而言基本上可以分成几类
? Range(范围)分区
? Hash(哈希)分区
? List(列表)分区
? 以及组合分区Range-Hash,Range-List。 对于表而言(常规意义上的堆组织表)上述分区形式都可以应用(甚至可以对某个分区指定compress属性)只不过分区依赖列不能是lob,long之类数据类型每个表的分区或子分区数的总数不能超过1023个。 对于索引组织表只能够支持普通分区方式不支持组合分区常规表的限制对于索引组织表同样有效除此之外呢还有一些其实的限制比如要求索引组织表的分区依赖列必须是主键才可以等。 注本篇所有示例仅针对常规表即堆组织表! 对于索引需要区分创建的是全局索引或本地索引 l 全局索引(global index)即可以分区也可以不分区。即可以建range分区也可以建hash分区即可建于分区表又可创建于非分区表上就是说全局索引是完全独立的因此它也需要我们更多的维护操作。 l 本地索引(local index)其分区形式与表的分区完全相同依赖列相同存储属性也相同。对于本地索引其索引分区的维护自动进行就是说你add/drop/split/truncate表的分区时本地索引会自动维护其索引分区。
Oracle建议如果单个表超过2G就最好对其进行分区对于大表创建分区的好处是显而易见的这里不多论述why而将重点放在when以及how。
ORACLE对于分区表方式其实就是将表分段存储一般普通表格是一个段存 储而分区表会分成多个段所以查找数据过程都是先定位根据查询条件定位分区范围即数据在那个分区或那几个内部然后在分区内部去查找数据一个分区一 般保证四十多万条数据就比较正常了但是分区表并非乱建立而其维护性也相对较为复杂一点而索引的创建也是有点讲究的这些以下尽量阐述详细即可。
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-hashrange-list。注意顺序哟根分区只能是range分区子分区可以是hash分区或list分区。
提示11g在组合分区功能这块有所增强又推出了range-range,list-range,list-list,list-hash 这就相当于除hash外三种分区方式的笛卡尔形式都有了。为什么会没有hash做为根分区的组合分区形式呢再仔细回味一下第二点你一定能够想明 白~~。
深入学习Oracle分区表及分区索引(2)
一、如何创建 如果想对某个表做分区必须在创建表时就指定分区我们可以对一个包含分区的表中的分区做修改但不能直接将一个未分区的表修改成分区表(起码在10g是不行的当然你可能会说可以通过在线重定义的方式但是这不是直接哟这也是借助临时表间接实现的)。 创建表或索引的语法就不说了大家肯定比我还熟悉而想在建表(索引)同时指定分区也非常容易只需要把创建分区的子句放到;前就行啦同 时需要注意表的row movement属性它用来控制是否允许修改列值所造成的记录移动至其它分区存储有enable|disable两种状态默认是disable row movement当disable时如果记录要被更新至其它分区则更新语句会报错。 下面分别演示不同分区方式的表和索引的创建
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_nameT_PARTITION_RANGE;
JSSWEB select partition_name,high_value,tablespace_name from user_tab_partitions where table_nameT_PARTITION_RANGE order by partition_position; ② 创建global索引range分区 JSSWEB create index idx_parti_range_id on t_partition_range(id) 2 global partition by range(id)( 3 partition i_range_p1 values less than (10) tablespace tbspart01, 4 partition i_range_p2 values less than (40) tablespace tbspart02, 5 partition i_range_pmax values less than (maxvalue) tablespace tbspart03); 索引已创建。 由上例可以看出创建global索引的分区与创建表的分区语句格式完全相同而且其分区形式与索引所在表的分区形式没有关联关系。 注意我们这里借助上面的表t_partition_range来演示创建range分区的global索引并不表示range分区的表只能创建range分区的global索引只要你想也可以为其创建hash分区的global索引。 查询索引的分区信息可以通过user_part_indexes、user_ind_partitions两个数据字典
JSSWEB select index_name, partitioning_type, partition_count 2 From user_part_indexes 3 where index_name IDX_PARTI_RANGE_ID; ③ Local分区索引的创建最简单例如 仍然借助t_partition_range表来创建索引 --首先删除之前创建的global索引 JSSWEB drop index IDX_PARTI_RANGE_ID; 索引已删除。 JSSWEB create index IDX_PARTI_RANGE_ID on T_PARTITION_RANGE(id) local; 索引已创建。 查询相关数据字典
JSSWEB select index_name, partitioning_type, partition_count 2 From user_part_indexes 3 where index_name IDX_PARTI_RANGE_ID; JSSWEB select partition_name, high_value, tablespace_name 2 from user_ind_partitions 3 where index_name IDX_PARTI_RANGE_ID 4 order by partition_position; 可以看出local索引的分区完全继承表的分区的属性包括分区类型分区的范围值即不需指定也不能更改这就是前面说的local索引的分区维护完全依赖于其索引所在表。 不过呢分区名称以及分区所在表空间等信息是可以自定义的例如 SQL create index IDX_PART_RANGE_ID ON T_PARTITION_RANGE(id) local ( 2 partition i_range_p1 tablespace tbspart01, 3 partition i_range_p2 tablespace tbspart01, 4 partition i_range_p3 tablespace tbspart02, 5 partition i_range_pmax tablespace tbspart02 6 ); 索引已创建。
SQL select index_name, partitioning_type, partition_count 2 From user_part_indexes 3 where index_name IDX_PART_RANGE_ID;
SQL select partition_name, high_value, tablespace_name 2 from user_ind_partitions 3 where index_name IDX_PART_RANGE_ID 4 order by partition_position; 创建hash分区 语法如下[图:hash_partitioning.gif] 语法看起来比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。 ? 注意在创建global索引时如果不显式指定分区子句则默认不分区(废话)。 3、分区应用
一般一张表超过2G的大小ORACLE是推荐使用分区表的分区一般都需要 创建索引说到分区索引就可以分为全局索引、分区索引即global索引和local索引前者为默认情况下在分区表上创建索引时的索引方式并 不对索引进行分区索引也是表结构索引大了也需要分区关于索引以后专门写点而全局索引可修饰为分区索引但是和local索引有所区别前者的分区 方式完全按照自定义方式去创建和表结构完全无关所以对于分区表的全局索引有以下两幅网上常用的图解
3.1、对于分区表的不分区索引这个有点绕不过就是表分区但其索引不分区 创建语法(直接创建即可)
CREATE INDEX ON (); 3.2、对于分区表的分区索引: 创建语法为
CREATE INDEX INX_TAB_PARTITION_COL1 ON TABLE_PARTITION(COL1) GLOBAL PARTITION BY RANGE(COL1)( PARTITION IDX_P1 values less than (1000000), PARTITION IDX_P2 values less than (2000000), PARTITION IDX_P3 values less than (MAXVALUE) ); 3.3、LOCAL索引结构 创建语法为 CREATE INDEX INX_TAB_PARTITION_COL1 ON TABLE_PARTITION(COL1) LOCAL;
也可按照分区表的的分区结构给与一一定义索引的分区将得到重命名。
分区上的位图索引只能为LOCAL索引不能为GLOBAL全局索引。 3.4、对比索引方式 一般使用LOCAL索引较为方便而且维护代价较低并且LOCAL索引是在分区的基础上去创建索引类似于在一个子表内部去创建索引这样开销主要是区 分分区上很规范的管理起来在OLAP系统中应用很广泛而相对的GLOBAL索引是全局类型的索引根据实际情况可以调整分区的类别而并非按照分区 结构一一定义相对维护代价较高一些在OLTP环境用得相对较多这里所谓OLTP和OLAP也是相对的不是特殊的项目没有绝对的划分概念在应用 过程中依据实际情况而定来提高整体的运行性能。 4、常用视图
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 MODIFY PARTITION ADD SUBPARTITION 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 12; 然后将第一个分区的数据交换出去 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 TRUNCATE PARTITION ; ALTER TABLE TRUNCATE subpartition ; 6、磁盘碎片压缩 对分区表的某分区进行磁盘压缩当对分区内部数据进行了大量的UPDATE、DELETE操作后一定时间需要进行磁盘压缩否则在查询时若通过FULL SCAN扫描数据将会把空块也会扫描到对表进行磁盘压缩需要进行行迁移操作所以首先需要操作
ALTER TABLE ENABLE ROW MOVEMENT ; 对分区表的某分区压缩语法为
ALTER TABLE
modify partition shrink space; 对普通表压缩
ALTER TABLE shrink space; 对于索引也需要进行压缩索引也是表
ALTER INDEX shrink space; 7、分区表重新分析以及索引重新分析 对表进行压缩后需要对表和索引进行重新分析对表进行重新分析一般有两种方式 在ORACLE 10G以前使用 BEGIN dbms_stats.gather_table_stats(USER,UPPER()); END; ORACLE 10G后可以使用 ANALYZE TABLE COMPUTE STATISTICS; 索引重新分析将上述两种方式分别修改一下如第一种可以使用gather_index_stats而第二种修改为ANALYZE INDEX即可不过一般比较常用的是重新编译 对于分区表并进行了索引分区的情况需要对每个分区的索引进行重新编译,这里以LOCAL索引为例子其每个索引的分区和表分区结构相同默认分区名称和表分区名称相同 ALTER INDEX REBUILD PARTITION ; 对于全局索引根据全局索引锁定义的分区名称修改即可若没有分区和普通单表索引重新编译方式相同 ALTER INDEX REBUILD; 1、关联对象重新编译 上述对表、索引进行重新编译尤其对表进行了压缩后会产生行迁移这个过程可能会导致一些视图、过程对象的失效此时要将其重新编译一次。 2、扩展HASH分区中如果创建了新的分区可以将其进行重新HASH分布
ALTER TABLE COALESCA PARTITION 8、回归总结何时建分区分区类别索引如何对应SQL 1、创建时机 上述已经说明2G以上的表ORACLE推荐创建分区。 分区的方式根据实际情况而定才能提高整体性能。 分区的字段一定要是经常用以提取数据的字段否则会在提取过程中导致遍历多个分区这样比没有分区还要慢。 分区字段要选择合适数据较为均匀分布到各个分区不要太多也不要太少而且根据分区字段可以很快定位到分区范围。 一般情况下尽量然业务操作在同一个分区内部完成。 2、分区类别 分区主要有RANGE、LIST、HASH RANGE通过值的范围分区也是最常用的分区这种分区注意在一种变长数字字符串中很多人会导致认为是数字类型而按照数字区分区这样会分布十分不均匀的现象发生。 LIST是列举方式进行分区一般作为二级分区而存在当然也可以自己分区ORACLE 11G后在分区上也可以作为主分区而存在在RANGE基础上若数据需要继续分区并且在RANGE基础上数据量较为固定只是较大可以按照一定规则进一步分区。 HASH只指定分区个数分区细节由ORACLE完成增加HASH分区可以重新分布数据。 注意分区字段不能使用函数转换后在分区如将某数字字符串字段先TO_NUMER(COL_NAME)后分区。 3、索引类别 大致分GLOBAL索引和LOCAL索引钱和可以分GLOBAL不分区索引和GLOBAL分区索引。 GLOBAL不分区索引一般不太推荐因为是用一颗大的索引树来映射一个表这个过程这样速度不见得比不分区快。 GLOBAL分区索引查找数据若通过要通过索引是先定位了索引内部的分区然后在这个分区索引中找到ROWID然后回表提取数据。 LOCAL索引是和分区的个数逐个对应的可以说先定位分区表的分区也可以说先定位索引的分区因为他们是一一对应的找到对应分区后分区内部索引数据集合。 4、对应应用 分区表、索引、分区索引要利用其性能优势最基本就是要提取数据时要通过它首先将数据的范围缩小到一个即使做全盘扫描也不会太慢的情况。 所以SQL一定要有分区上的这个字段的一个WHERE条件将数据迅速定位到分区内部而且尽量定位到一个分区里面这个和创建分区的规则有关系。 建立分区本身不提要性能要用好才可提高性能在必要的RAC集群中若存在多分区提取数据适当采用并行提取可以提高提取的速度。 对于索引部分这里也只提到分区索引的创建方式以及常见索引的维护方式对于索引原理理解后会更容易认识到提取数据时的技巧。 9、实战 分区表和一般表一样可以建立索引分区表可以创建局部索引和全局索引。当分区中出现许多事务并且要保证所有分区中的数据记录的唯一性时采用全局索引。
1 局部索引分区的建立
SQL create index dinya_idx_t on dinya_test(item_id) 2 local 3 ( 4 partition idx_1 tablespace dinya_space01, 5 partition idx_2 tablespace dinya_space02, 6 partition idx_3 tablespace dinya_space03 7 ); Index created. SQL 看查询的执行计划从下面的执行计划可以看出系统已经使用了索引
SQL select * from dinya_test partition(part_01) t where t.item_id12; Execution Plan
---------------------------------------------------------- 0 SELECT STATEMENT OptimizerCHOOSE (Cost2 Card1 Bytes187) 1 0 TABLE ACCESS (BY LOCAL INDEX ROWID) OF DINYA_TEST (Cost 2 Card1 Bytes187) 2 1 INDEX (RANGE SCAN) OF DINYA_IDX_T (NON-UNIQUE) (Cost1 Card1)
Statistics
---------------------------------------------------------- 0 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 334 bytes sent via SQL*Net to client 309 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 2 rows processed SQL 2 全局索引分区的建立。
全局索引建立时global 子句允许指定索引的范围值这个范围值为索引字段的范围值
SQL create index dinya_idx_t on dinya_test(item_id) 2 global partition by range(item_id) 3 ( 4 partition idx_1 values less than (1000) tablespace dinya_space01, 5 partition idx_2 values less than (10000) tablespace dinya_space02, 6 partition idx_3 values less than (maxvalue) tablespace dinya_space03 7 ); Index created. SQL 本例中对表的item_id字段建立索引分区当然也可以不指定索引分区名直接对整个表建立索引如
SQL create index dinya_idx_t on dinya_test(item_id);
Index created. SQL 同样的对全局索引根据执行计划可以看出索引已经可以使用
SQL select * from dinya_test t where t.item_id12; Execution Plan
---------------------------------------------------------- 0 SELECT STATEMENT OptimizerCHOOSE (Cost2 Card3 Bytes561) 1 0 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF DINYA_TEST (Cost 2 Card3 Bytes561) 2 1 INDEX (RANGE SCAN) OF DINYA_IDX_T (NON-UNIQUE) (Cost1 Card3)
Statistics
---------------------------------------------------------- 5 recursive calls 0 db block gets 10 consistent gets 0 physical reads 0 redo size 420 bytes sent via SQL*Net to client 309 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 3 sorts (memory) 0 sorts (disk) 5 rows processed SQL 其实分区的管理很简单难点在于分区方式的选择和分区表上面索引的选择。
Global Index全局索引和Local Index局部索引Global partitioned index和global nonpartitioned index
选择多就越迷惑。 ----------------------------------------
