oralce索引和分区索引的使用

32324

oracle分区表和分区索引的本质就是将数据分段存储，包括表和索引（索引从本质上来讲也是表），表分区会将表分成多个段分别存储。由此数据查询过程改变为先根据查询条件定位分区表，然后从该表中查询数据，从而提高性能。这些操作对用户是透明的，用户只需要使用普通的存取操作即可。

1. 分区表

分区表一般有如下几种：

range分区方式：

最常用的分区方式，通过某个字段或者某几个字段组合的值，从小到大，按照指定的范围进行分区，在insert数据时就会把数据插入到指定分区中去。

List分区方式：

通常作为二级分区，是一种列举方式的分区，一般某些地区，规则等可以枚举的数据可以采用此种方法分区。

Hash分区方式：

由oracle自身管理的分区方式，用于只需要insert即可。

2. 分区索引

一般一张表超过2G，就推荐使用分区表，分区一般都需要创建索引。索引也就是目录，通过索引能够快速定位到记录。索引可以分为全局索引、分区索引；也即global索引和local索引。global为默认的索引创建方式，并不对索引进行分区（索引也可以分区）。

a. 分区表的不分区索引：

CREATE INDEX <index_name> ON <partition_table_name>(<column_name>);

b. 分区表的分区索引：

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)
  )

c. Local索引

CREATE INDEX INX_TAB_PARTITION_COL1 ON TABLE_PARTITION(COL1) LOCAL;

d. 对比使用方式

一般使用local索引比较方便，管理简单，可以自动维护。

3. 常用SQL

a、查询当前用户下有哪些是分区表：

SELECT * FROM USER_PART_TABLES;

b、查询当前用户下有哪些分区索引：

SELECT * FROM USER_PART_INDEXES;

c、查询当前用户下分区索引的分区信息：

SELECT * FROM USER_IND_PARTITIONS T WHERE T.INDEX_NAME=?

d、查询当前用户下分区表的分区信息：

SELECT * FROM USER_TAB_PARTITIONS T WHERE T.TABLE_NAME=?;

e、查询某分区下的数据量：

SELECT COUNT(*) FROM TABLE_PARTITION PARTITION(TAB_PARTOTION_01);

f、查询索引、表上在那些列上创建了分区：

SELECT * FROM USER_PART_KEY_COLUMNS;

g、查询某用户下二级分区的信息（只有创建了二级分区才有数据）：

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、修改分区名称（修改相关的属性信息）:
ALTER 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>;

4.8、磁盘碎片压缩
   对分区表的某分区进行磁盘压缩，当对分区内部数据进行了大量的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;

4.9、分区表重新分析以及索引重新分析
  对表进行压缩后，需要对表和索引进行重新分析，对表进行重新分析，一般有两种方式：
  在ORACLE 10G以前，使用：
  BEGIN
     dbms_stats.gather_table_stats(USER,UPPER('<table_name>'));
  END;
  ORACLE 10G后，可以使用：
  ANALYZE TABLE <table_name> COMPUTE STATISTICS;
  索引重新分析，将上述两种方式分别修改一下，如第一种可以使用：gather_index_stats，而第二种修改为：ANALYZE INDEX即可，不过一般比较常用的是重新编译：
  对于分区表并进行了索引分区的情况，需要对每个分区的索引进行重新编译,这里以LOCAL索引为例子（其每个索引的分区和表分区结构相同，默认分区名称和表分区名称相同）：
  ALTER INDEX <index_name> REBUILD PARTITION <partition_name>;
  对于全局索引，根据全局索引锁定义的分区名称修改即可，若没有分区，和普通单表索引重新编译方式相同：
  ALTER INDEX <index_name> REBUILD;

4.10、关联对象重新编译,
  上述对表、索引进行重新编译，尤其对表进行了压缩后会产生行迁移，这个过程可能会导致一些视图、过程对象的失效，此时要将其重新编译一次。

4.11、扩展：HASH分区中，如果创建了新的分区，可以将其进行重新HASH分布：
ALTER TABLE <table_name> COALESCA PARTITION%

5. 总结

全局索引是独立的，需要更多人工维护；对于本地索引，引起依赖于本地分区，其可以自动维护，也就是说当add/drop/split/truncate时，Local索引可以自动维护。

一般来说一张表最好不要存储超过40W条数据

如果想要对某张表做分区，必须是在创建表时即创建分区，不能将一个未分区的表修改成分区表。