网站维护等,网站建设询价报告,网络游戏代理,宁德古田建设局网站一段听说CSDN.COM里面很多好东西#xff0c;同事建议看看合适自己也可以写一写#xff0c;呵呵#xff0c;今天第一次开通博客#xff0c;随便写点东西#xff0c;就以第一印象分区表简单写第一个吧。 ORACLE对于分区表方式其实就是将表分段存储#xff0c;一般普通表格是… 一段听说CSDN.COM里面很多好东西同事建议看看合适自己也可以写一写呵呵今天第一次开通博客随便写点东西就以第一印象分区表简单写第一个吧。 ORACLE对于分区表方式其实就是将表分段存储一般普通表格是一个段存储而分区表会分成多个段所以查找数据过程都是先定位根据查询条件定位分区范围即数据在那个分区或那几个内部然后在分区内部去查找数据一个分区一般保证四十多万条数据就比较正常了但是分区表并非乱建立而其维护性也相对较为复杂一点而索引的创建也是有点讲究的这些以下尽量阐述详细即可。 1、类型说明 range分区方式也算是最常用的分区方式其通过某字段或几个字段的组合的值从小到大按照指定的范围说明进行分区我们在INSERT数据的时候就会存储到指定的分区中。 List分区方式一般是在range基础上做的二级分区较多是一种列举方式进行分区一般讲某些地区、状态或指定规则的编码等进行划分。 Hash分区方式它没有固定的规则由ORACLE管理只需要将值INSERT进去ORACLE会自动去根据一套HASH算法去划分分区只需要告诉ORACLE要分几个区即可。 分区可以进行两两组合ORACLE 11G以前两两组合都必须以range作为一级分区的开头ORACLE目前最多支持2级别分区但这个级别已经够我们使用了。 我这只以最简单的分区方式创建分区来说明问题就拿range分区来说明问题吧基本创建语句如下 [sql] view plaincopyprint? CREATETABLE TABLE_PARTITION( COL1 NUMBER, COL2 VARCHAR2(10) ) partition by range(COL1)( partition TAB_PARTOTION_01 values less than (450000), partition TAB_PARTOTION_02 values less than (900000), partition TAB_PARTOTION_03 values less than (1350000), partition TAB_PARTOTION_04 values less than (1800000), partition TAB_PARTOTION_OTHER values less THAN (MAXVALUE) ); CREATE TABLE TABLE_PARTITION(COL1 NUMBER,COL2 VARCHAR2(10)
)
partition by range(COL1)(partition TAB_PARTOTION_01 values less than (450000),partition TAB_PARTOTION_02 values less than (900000),partition TAB_PARTOTION_03 values less than (1350000),partition TAB_PARTOTION_04 values less than (1800000),partition TAB_PARTOTION_OTHER values less THAN (MAXVALUE)
); 这个分区表创建了四个定长分区理想情况下存储450000条数据扩展分区是超过这个数额的分区当发现扩展分区有数据的时候可以进行将扩展分区做SPLIT操作这个后面说明这里先说一下一些常用的分区表查询功能我们先插入一些数据进去。 [sql] view plaincopyprint? INSERTINTO TABLE_PARTITION(COL1,COL2) VALUES(1,数据测试); INSERTINTO TABLE_PARTITION(COL1,COL2) VALUES(23,数据测试); INSERTINTO TABLE_PARTITION(COL1,COL2) VALUES(449000,数据测试); INSERTINTO TABLE_PARTITION(COL1,COL2) VALUES(450000,数据测试); INSERTINTO TABLE_PARTITION(COL1,COL2) VALUES(1350000,数据测试); INSERTINTO TABLE_PARTITION(COL1,COL2) VALUES(900000,数据测试); INSERTINTO TABLE_PARTITION(COL1,COL2) VALUES(1800000-1,数据测试); COMMIT; INSERT INTO TABLE_PARTITION(COL1,COL2)
VALUES(1,数据测试);
INSERT INTO TABLE_PARTITION(COL1,COL2)
VALUES(23,数据测试);
INSERT INTO TABLE_PARTITION(COL1,COL2)
VALUES(449000,数据测试);INSERT INTO TABLE_PARTITION(COL1,COL2)
VALUES(450000,数据测试);INSERT INTO TABLE_PARTITION(COL1,COL2)
VALUES(1350000,数据测试);INSERT INTO TABLE_PARTITION(COL1,COL2)
VALUES(900000,数据测试); INSERT INTO TABLE_PARTITION(COL1,COL2)
VALUES(1800000-1,数据测试);
COMMIT; 为了检测哪些分区中有哪些数据分别按照分区去查询数据应用开发中基本不会用到因为不会把分区写死 [sql] view plaincopyprint? SQL SELECT * FROM TABLE_PARTITION partition(TAB_PARTOTION_01); COL1 COL2 ---------- --------------- 1 数据测试 23 数据测试 449000 数据测试 SQL SELECT * FROM TABLE_PARTITION partition(TAB_PARTOTION_01);COL1 COL2
---------- ---------------1 数据测试23 数据测试449000 数据测试 说明第一个分区有1、23、44900这些数据也就是插入时ORACLE是自己去找分区的其实分区这种子表管理自己也可以通过程序去完成ORACLE给你提供了一套就可以自己去完成了。其余的数据就自己查了都是一个道理。 2、分区应用 一般一张表超过2G的大小ORACLE是推荐使用分区表的分区一般都需要创建索引说到分区索引就可以分为全局索引、分区索引即global索引和local索引前者为默认情况下在分区表上创建索引时的索引方式并不对索引进行分区索引也是表结构索引大了也需要分区关于索引以后专门写点而全局索引可修饰为分区索引但是和local索引有所区别前者的分区方式完全按照自定义方式去创建和表结构完全无关所以对于分区表的全局索引有以下两幅网上常用的图解 2.1、对于分区表的不分区索引这个有点绕不过就是表分区但其索引不分区 创建语法(直接创建即可) CREATE INDEX index_name ON partition_table_name(column_name); 2.2、对于分区表的分区索引: 创建语法为 [sql] view plaincopyprint? CREATEINDEX 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) ); 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)); 2.3、LOCAL索引结构 创建语法为 [sql] view plaincopyprint? CREATEINDEX INX_TAB_PARTITION_COL1 ON TABLE_PARTITION(COL1) LOCAL; CREATE INDEX INX_TAB_PARTITION_COL1 ON TABLE_PARTITION(COL1) LOCAL; 也可按照分区表的的分区结构给与一一定义索引的分区将得到重命名。 分区上的位图索引只能为LOCAL索引不能为GLOBAL全局索引。 2.4、对比索引方式 一般使用LOCAL索引较为方便而且维护代价较低并且LOCAL索引是在分区的基础上去创建索引类似于在一个子表内部去创建索引这样开销主要是区分分区上很规范的管理起来在OLAP系统中应用很广泛而相对的GLOBAL索引是全局类型的索引根据实际情况可以调整分区的类别而并非按照分区结构一一定义相对维护代价较高一些在OLTP环境用得相对较多这里所谓OLTP和OLAP也是相对的不是特殊的项目没有绝对的划分概念在应用过程中依据实际情况而定来提高整体的运行性能。 3、常用视图 [sql] view plaincopyprint? 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、查询某分区下的数据量 SELECTCOUNT(*) FROM TABLE_PARTITION PARTITION(TAB_PARTOTION_01); 6、查询索引、表上在那些列上创建了分区 SELECT * FROM USER_PART_KEY_COLUMNS; 7、查询某用户下二级分区的信息只有创建了二级分区才有数据 SELECT * FROM USER_TAB_SUBPARTITIONS; 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; 4、维护操作 4.1、删除分区 [sql] view plaincopyprint? ALTERTABLE TABLE_PARTITION DROP PARTITION TAB_PARTOTION_03; 如果是全局索引因为全局索引的分区结构和表可以不一致若不一致的情况下会导致整个全局索引失效在删除分区的时候语句修改为 ALTERTABLE TABLE_PARTITION DROP PARTITION TAB_PARTOTION_03 UPDATEGLOBAL INDEXES ALTER TABLE TABLE_PARTITION DROP PARTITION TAB_PARTOTION_03;
如果是全局索引因为全局索引的分区结构和表可以不一致若不一致的情况下会导致整个全局索引失效在删除分区的时候语句修改为
ALTER TABLE TABLE_PARTITION DROP PARTITION TAB_PARTOTION_03 UPDATE GLOBAL INDEXES 4.2、分区合并从中间删除掉一个分区或者两个分区需要合并后减少分区数量 合并分区和删除中间的RANGE有点像但是合并分区是不会删除数据的对于LIST、HASH分区也是和RANGE分区不一样的其语法为 [sql] view plaincopyprint? ALTERTABLE TABLE_PARTITION MERGE PARTITIONS TAB_PARTOTION_01,TAB_PARTOTION_02 INTO PARTITION MERGED_PARTITION; ALTER TABLE TABLE_PARTITION MERGE PARTITIONS TAB_PARTOTION_01,TAB_PARTOTION_02 INTO PARTITION MERGED_PARTITION; 4.3、分隔分区一般分区从扩展分区从分隔 [sql] view plaincopyprint? ALTERTABLE TABLE_PARTITION SPLIT PARTITION TAB_PARTOTION_OTHERE AT(2500000) INTO (PARTITION TAB_PARTOTION_05,PARTITION TAB_PARTOTION_OTHERE); ALTER TABLE TABLE_PARTITION SPLIT PARTITION TAB_PARTOTION_OTHERE AT(2500000)
INTO (PARTITION TAB_PARTOTION_05,PARTITION TAB_PARTOTION_OTHERE); 4.4、创建新的分区分区数据若不能提供范围则插入时会报错需要增加分区来扩大范围 一般有扩展分区的是都是用分隔的方式若上述创建表时没有创建TAB_PARTOTION_OTHER分区时在插入数据较大时按照上述建立规则超过1800000就应该创建新的分区来存储就可以创建新的分区如 为了试验我们将扩展分区先删除掉再创建新的分区因为ORACLE要求分区的数据不允许重叠即按照分区字段同样的数据不能同时存储在不同的分区中 [sql] view plaincopyprint? ALTERTABLE TABLE_PARTITION DROP PARTITION TAB_PARTOTION_OTHER; ALTERTABLE TABLE_PARTITION ADD PARTITION TAB_PARTOTION_06 VALUES LESS THAN(2500000); 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分区将创建方式修改为对应的方式即可 [sql] view plaincopyprint? ALTERTABLE table_name MODIFY PARTITION partition_name ADD SUBPARTITION user_define_subpartition_name VALUES LESS THAN(....); ALTER TABLE table_name MODIFY PARTITION partition_name ADD SUBPARTITION user_define_subpartition_name VALUES LESS THAN(....); 4.5、修改分区名称修改相关的属性信息 [sql] view plaincopyprint? ALTERTABLE TABLE_PARTITION RENAME PARTITION MERGED_PARTITION TO MERGED_PARTITION02; ALTER TABLE TABLE_PARTITION RENAME PARTITION MERGED_PARTITION TO MERGED_PARTITION02; 4.6、交换分区快速交换数据其实是交换段名称指针 首先创建一个交换表和原表结构相同如果有数据必须符合所交换对应分区的条件 [sql] view plaincopyprint? CREATETABLE TABLE_PARTITION_2 ASSELECT * FROM TABLE_PARTITION WHERE 12; CREATE TABLE TABLE_PARTITION_2AS SELECT * FROM TABLE_PARTITION WHERE 12; 然后将第一个分区的数据交换出去 [sql] view plaincopyprint? ALTERTABLE TABLE_PARTITION EXCHANGE PARTITION TAB_PARTOTION_01 WITHTABLE TABLE_PARTITION_2 INCLUDING INDEXES; ALTER TABLE TABLE_PARTITION EXCHANGE PARTITION TAB_PARTOTION_01 WITH TABLE TABLE_PARTITION_2 INCLUDING INDEXES; 此时会发现第一个分区的数据和表TABLE_PARTITION_2做了瞬间交换比TRUNCATE还要快因为这个过程没有进行数据转存只是段名称的修改过程和实际的数据量没有关系。 如果是子分区也可以与外部的表进行交换只需要将关键字修改为SUBPARTITION 即可。 4.7、清空分区数据 [sql] view plaincopyprint? ALTERTABLE table_name TRUNCATE PARTITION partition_name; ALTERTABLE table_name TRUNCATE subpartition subpartition_name; ALTER TABLE table_name TRUNCATE PARTITION partition_name;ALTER TABLE table_name TRUNCATE subpartition subpartition_name; 9、磁盘碎片压缩 对分区表的某分区进行磁盘压缩当对分区内部数据进行了大量的UPDATE、DELETE操作后一定时间需要进行磁盘压缩否则在查询时若通过FULL SCAN扫描数据将会把空块也会扫描到对表进行磁盘压缩需要进行行迁移操作所以首先需要操作 [sql] view plaincopyprint? ALTERTABLE table_name ENABLE ROW MOVEMENT ; 对分区表的某分区压缩语法为 ALTERTABLE table_name modify partition partition_name shrink space; 对普通表压缩 ALTERTABLE table_name shrink space; 对于索引也需要进行压缩索引也是表 ALTERINDEX index_name shrink space; ALTER TABLE table_name ENABLE ROW MOVEMENT ;对分区表的某分区压缩语法为
ALTER TABLE table_name
modify partition partition_name shrink space;对普通表压缩
ALTER TABLE table_name shrink space;对于索引也需要进行压缩索引也是表
ALTER INDEX index_name shrink space; 10、分区表重新分析以及索引重新分析 对表进行压缩后需要对表和索引进行重新分析对表进行重新分析一般有两种方式 在ORACLE 10G以前使用 [sql] view plaincopyprint? BEGIN dbms_stats.gather_table_stats(USER,UPPER(table_name)); END; ORACLE 10G后可以使用 ANALYZE TABLE table_name COMPUTE STATISTICS; BEGINdbms_stats.gather_table_stats(USER,UPPER(table_name));END;ORACLE 10G后可以使用ANALYZE TABLE table_name COMPUTE STATISTICS; 索引重新分析将上述两种方式分别修改一下如第一种可以使用gather_index_stats而第二种修改为ANALYZE INDEX即可不过一般比较常用的是重新编译 对于分区表并进行了索引分区的情况需要对每个分区的索引进行重新编译,这里以LOCAL索引为例子其每个索引的分区和表分区结构相同默认分区名称和表分区名称相同 [sql] view plaincopyprint? ALTERINDEX index_name REBUILD PARTITION partition_name; 对于全局索引根据全局索引锁定义的分区名称修改即可若没有分区和普通单表索引重新编译方式相同 ALTERINDEX index_name REBUILD; ALTER INDEX index_name REBUILD PARTITION partition_name;对于全局索引根据全局索引锁定义的分区名称修改即可若没有分区和普通单表索引重新编译方式相同ALTER INDEX index_name REBUILD; 11、关联对象重新编译 上述对表、索引进行重新编译尤其对表进行了压缩后会产生行迁移这个过程可能会导致一些视图、过程对象的失效此时要将其重新编译一次。 12、扩展HASH分区中如果创建了新的分区可以将其进行重新HASH分布 [sql] view plaincopyprint? ALTERTABLE table_name COALESCA PARTITION ALTER TABLE table_name COALESCA PARTITION 5、回归总结何时建分区分区类别索引如何对应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集群中若存在多分区提取数据适当采用并行提取可以提高提取的速度。 对于索引部分这里也只提到分区索引的创建方式以及常见索引的维护方式对于索引原理理解后会更容易认识到提取数据时的技巧。 转载于:https://www.cnblogs.com/weixun/p/3233444.html
