ORACLE分区表、分区索引详解
ORACLE分区表、分区索引ORACLE对于分区表⽅式其实就是将表分段存储，⼀般普通表格是⼀个段存储，⽽分区表会分成多个段，所以查数据过程都是先定位根据查询条件定位分区范围，即数据在那个分区或那⼏个内部，然后在分区内部去查数据，⼀个分区⼀般保证四⼗多万条数据就⽐较正常了，但是分区表并⾮乱建⽴，⽽其维护性也相对较为复杂⼀点，⽽索引的创建也是有点讲究的，这些以下尽量阐述详细即可。
1、类型说明：
range分区⽅式，也算是最常⽤的分区⽅式，其通过某字段或⼏个字段的组合的值，从⼩到⼤，按照指定的范围说明进⾏分区，我们在INSERT数据的时候就会存储到指定的分区中。
List分区⽅式，⼀般是在range基础上做的⼆级分区较多，是⼀种列举⽅式进⾏分区，⼀般讲某些地区、状态或指定规则的编码等进⾏划分。Hash分区⽅式，它没有固定的规则，由ORACLE管理，只需要将值INSERT进去，ORACLE会⾃动去根据⼀套HASH算法去划分分区，只需要告诉ORACLE要分⼏个区即可。
分区可以进⾏两两组合，ORACLE 11G以前两两组合都必须以range作为⼀级分区的开头，ORACLE⽬前最多⽀持2级别分区，但这个级别已经够我们使⽤了。
我这只以最简单的分区⽅式创建分区来说明问题，就拿range分区来说明问题吧（基本创建语句如下）：
CREATE [url=]TABLE[/url] 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操作，这个后⾯说明，这⾥先说⼀下⼀些常⽤的分区表查询功能，我们先插⼊⼀些数据进去。
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> SELECT * FROM TABLE_PARTITION partition(TAB_PARTOTION_01);
COL1    COL2
---------- ---------------
1        数据测试
23      数据测试
449000    数据测试
说明第⼀个分区有：1、23、44900这些数据，也就是插⼊时，ORACLE是⾃⼰去分区的，其实分区这种⼦表[url=]管理[/url]⾃⼰也可以通过程序去完成，ORACLE给你提供了⼀套，就可以⾃⼰去完成了。其余的数据就⾃⼰查了，都是⼀个道理。
2、分区应⽤：
⼀般⼀张表超过2G的⼤⼩，ORACLE是推荐使⽤分区表的，分区⼀般都需要创建索引，说到分区索引，就可以分为：全局索引、分区索引，即：global索引和local索引，前者为默认情况下在分区表上创建索引时的索引⽅式，并不对索引进⾏分区（索引也是表结构，索引⼤了也需要分区，关于索引以后专门写点）⽽全局索引可修饰为分区索引，但是和local索引有所区别，前者的分区⽅式完全按照⾃定义⽅式去创建，和表结构完全⽆关，所以对于分区表的全局索引有以下两幅⽹上常⽤的图解：
2.1、对于分区表的不分区索引（这个有点绕，不过就是表分区，但其索引不分区）：
创建语法(直接创建即可)：
CREATE INDEX <index_name> ON <partition_table_name>(<column_name>);
2.2、对于分区表的分区索引:
创建语法为：
CREATE [url=]INDEX[/url] 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索引结构：
创建语法为：
CREATE INDEX INX_TAB_PARTITION_COL1 ON TABLE_PARTITION(COL1) LOCAL;
也可按照分区表的的分区结构给与⼀⼀定义，索引的分区将得到重命名。
分区上的位图索引只能为LOCAL索引，不能为GLOBAL全局索引。
2.4、对⽐索引⽅式：
⼀般使⽤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;
4、维护操作：
4.1、删除分区
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分区不⼀样的，其语法为：
ALTER TABLE TABLE_PARTITION MERGE PARTITIONS    TAB_PARTOTION_01,TAB_PARTOTION_02 INTO PARTITION MERGED_PARTITION;
4.3、分隔分区（⼀般分区从扩展分区从分隔）
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要求，分区的数据不允许重叠，即按照分区字段同样的数据不能同时存储在不同的分区中）：
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(....);
4.5、修改分区名称（修改相关的属性信息）:
tabletableALTER TABLE TABLE_PARTITION RENAME PARTITION MERGED_PARTITION TO MERGED_PARTITION02;
4.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 即可。
4.7、清空分区数据
ALTER TABLE <table_name> TRUNCATE PARTITION <partition_name>;
ALTER TABLE <table_name> TRUNCATE subpartition <subpartition_name>;
9、磁盘碎⽚压缩
对分区表的某分区进⾏磁盘压缩，当对分区内部数据进⾏了⼤量的UPDATE、DELETE操作后，⼀定时间需要进⾏磁盘压缩，否则在查询时，若通过FULL SCAN扫描数据，将会把空块也会扫描到，对表进
⾏磁盘压缩需要进⾏⾏迁移操作，所以⾸先需要操作：
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以前，使⽤：
BEGIN
dbms_stats.gather_table_stats(USER,UPPER('<table_name>'));
END;
ORACLE 10G后，可以使⽤：
ANALYZE TABLE <table_name> COMPUTE STATISTICS;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。