ORACLE简单易懂之分区表和索引

发表于 | 分类于

分区表，分区索引

Oracle对于分区表方式其实就是将表分段存储，一般普通表格是一个段存储，而分区表会分成多个段，所以查找数据过程都是先定位根据查询条件定位分区范围，即数据在那个分区或那几个内部，然后在分区内部去查找数据，一个分区一般保证四十多万条数据就比较正常了，但是分区表并非乱建立，而其维护性也相对较为复杂一点，而索引的创建也是有点讲究的。

创建时机:

  • 2G以上的表，ORACLE推荐创建分区。
  • 分区的字段一定要是经常用以提取数据的字段，否则会在提取过程中导致遍历多个分区，这样比没有分区还要慢。
  • 分区字段要选择合适，数据较为均匀分布到各个分区，不要太多也不要太少，而且根据分区字段可以很快定位到分区范围。
  • 一般情况下，尽量然业务操作在同一个分区内部完成。

分区类型

  • range分区方式，也算是最常用的分区方式，其通过某字段或几个字段的组合的值，从小到大，按照指定的范围说明进行分区，我们在INSERT数据的时候就会存储到指定的分区中。

  • List分区方式，一般是在range基础上做的二级分区较多，是一种列举方式进行分区，一般讲某些地区、状态或指定规则的编码等进行划分。

  • Hash分区方式，它没有固定的规则，由ORACLE管理，只需要将值INSERT进去，ORACLE会自动去根据一套HASH算法去划分分区，只需要告诉ORACLE要分几个区即可。

分区可以进行两两组合，ORACLE 11G以前两两组合都必须以range作为一级分区的开头，ORACLE目前最多支持2级别分区，但这个级别已经够我们使用了。

1
2
3
4
5
6
7
8
9
10
11
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条数据，扩展分区是超过这个数额的分区，当发现扩展分区有数据的时候，可以进行将扩展分区，插入一些数据进去。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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;

为了检测哪些分区中有哪些数据分别按照分区去查询数据（应用开发中基本不会用到，因为不会把分区写死）

1
2
3
4
5
6
SELECT * FROM TABLE_PARTITION partition(TAB_PARTOTION_01);  
      COL1     COL2  
---------- ---------------  
         1     数据测试  
        23     数据测试  
    449000    数据测试

说明第一个分区有：1、23、44900这些数据，也就是插入时，ORACLE是自己去找分区的，其实分区这种子表管理自己也可以通过程序去完成，ORACLE给你提供了一套，就可以自己去完成了。其余的数据就自己查了，都是一个道理。

分区索引

一般一张表超过2G的大小，ORACLE是推荐使用分区表的，分区一般都需要创建索引，说到分区索引，就可以分为：全局索引、分区索引，即：global索引和local索引，前者为默认情况下在分区表上创建索引时的索引方式，并不对索引进行分区（索引也是表结构，索引大了也需要分区，）而全局索引可创建为分区索引，但是和local索引有所区别，前者的分区方式完全按照自定义方式去创建，和表结构完全无关，所以对于分区表的全局索引有以下两幅网上常用的图解：

  • GLOBAL不分区索引（表分区，索引不分区）：
    1
    CREATE INDEX <index_name> ON <partition_table_name>(<column_name>);

此处输入图片的描述

  • GLOBAL分区索引（表分区，索引分区）:
    1
    2
    3
    4
    5
    6
    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)  
      );

此处输入图片的描述

  • LOCAL索引:
    注：分区上的位图索引只能为LOCAL索引，不能为GLOBAL全局索引。
    1
    CREATE INDEX INX_TAB_PARTITION_COL1 ON TABLE_PARTITION(COL1) LOCAL;

此处输入图片的描述

对比索引方式：

  • LOCAL索引较为方便，而且维护代价较低，并且LOCAL索引是在分区的基础上去创建索引，在OLAP系统中应用很广泛;
    LOCAL索引是和分区的个数逐个对应的，可以说先定位分区表的分区也可以说先定位索引的分区，因为他们是一一对应的，找到对应分区后，分区内部索引数据集合。
  • GLOBAL索引是全局类型的索引，根据实际情况可以调整分区的类别，而并非按照分区结构一一定义，相对维护代价较高一些，在OLTP环境用得相对较多；
    GLOBAL不分区索引一般不太推荐，因为是用一颗大的索引树来映射一个表，这个过程，这样速度不见得比不分区快。
    GLOBAL分区索引，查找数据若通过要通过索引，是先定位了索引内部的分区，然后在这个分区索引中找到ROWID，然后回表提取数据。

常用视图

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
--查询当前用户下有哪些是分区表：  
SELECT * FROM USER_PART_TABLES;  
   
--查询当前用户下有哪些分区索引：  
SELECT * FROM USER_PART_INDEXES;  
   
--查询当前用户下分区索引的分区信息：  
SELECT * FROM USER_IND_PARTITIONS T  
WHERE T.INDEX_NAME=?  
   
--查询当前用户下分区表的分区信息：  
SELECT * FROM USER_TAB_PARTITIONS T  
WHERE T.TABLE_NAME=?;  
   
--查询某分区下的数据量：  
SELECT COUNT(*) FROM TABLE_PARTITION PARTITION(TAB_PARTOTION_01);  
   
--查询索引、表上在那些列上创建了分区：  
SELECT * FROM USER_PART_KEY_COLUMNS;  
   
--查询某用户下二级分区的信息（只有创建了二级分区才有数据）：  
SELECT * FROM USER_TAB_SUBPARTITIONS;

维护操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
--分区删除:如果是全局索引，因为全局索引的分区结构和表可以不一致，需要添加'UPDATE GLOBAL INDEXES'
ALTER TABLE TABLE_PARTITION DROP PARTITION TAB_PARTOTION_03;
ALTER TABLE TABLE_PARTITION DROP PARTITION TAB_PARTOTION_03 UPDATE GLOBAL INDEXES 
   
--分区合并：合并分区是不会删除数据的
alter table table_partition merge partitions P_01, P_02 into partition P_MERGE;
   
--分区拆分：一般分区从扩展分区拆分
alter table table_partition split partition P_MAX AT(2800000) 
    into (partition P_05,partition P_MAX); 
   
--分区新建：分区数据若不能提供范围，则插入时会报错，需要增加分区来扩大范围
ORA-14400: inserted partition key does not map to any partition  
alter table table_partition add partition P_MAX values less than(maxvalue);  
   
--分区清除数据：  
alter table table_partition truncate partition P_03; 
/**   
磁盘碎片压缩：对分区表的某分区进行磁盘压缩，当对分区内部数据进行了大量的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; 
-- 分区表重新分析以及索引重新分析
ANALYZE TABLE <table_name> COMPUTE STATISTICS;
ALTER INDEX <index_name> REBUILD PARTITION <partition_name>;
ALTER INDEX <index_name> REBUILD;