运维工程师常用的Oracle经典语句——7.Oracle分区表以及分区索引的创建

1. Oracle是否可以添加分区表？
   如果是分区表可以添加分区，如果不是分区表，可以把表转换成分区表，再增加分区
2. Oracle如何创建分区表？

#创建Range标准表分区
CREATE TABLE t_partition_range 
   (
    ORDER_ID      NUMBER(7) NOT NULL,
    ORDER_DATE    DATE,
    OTAL_AMOUNT NUMBER,
    CUSTOTMER_ID NUMBER(7),
    PAID           CHAR(1)
    )
    PARTITION BY RANGE (ORDER_DATE)
   (
     PARTITION p1 VALUES LESS THAN (TO_DATE('2014-10-01', 'yyyy-mm-dd')) ,
     PARTITION p2 VALUES LESS THAN (TO_DATE('2015-10-01', 'yyyy-mm-dd')) ,
     PARTITION p3 VALUES LESS THAN (TO_DATE('2016-10-01', 'yyyy-mm-dd')) ,
     PARTITION p4 VALUES LESS THAN (maxvalue)
   );

#增加Range表分区
alter table t_partition_range  add partition p5 values less than (TO_DATE('2017-10-01', 'yyyy-mm-dd'))；
#修改Range表分区
alter table t_partition_range  modifypartition p5 values less than (TO_DATE('2017-08-01', 'yyyy-mm-dd'))；
#删除Range表分区
alter table t_partition_range  drop partition p_5;

3. Oracle如何创建分区索引

#如果不加GLOBAL ，默认为GLOBAL 
CREATE INDEX INX_TAB_PARTITION_ORDER_DATE ON t_partition_range (ORDER_DATE)
  GLOBAL PARTITION BY RANGE(ORDER_DATE)(
     PARTITION p1 VALUES LESS THAN (TO_DATE('2014-10-01', 'yyyy-mm-dd')) ,
     PARTITION p2 VALUES LESS THAN (TO_DATE('2015-10-01', 'yyyy-mm-dd')) ,
     PARTITION p3 VALUES LESS THAN (TO_DATE('2016-10-01', 'yyyy-mm-dd')) ,
     PARTITION p4 VALUES LESS THAN (maxvalue)
  );

4.查看Oracle数据库中的所有分区表以及分区字段

 -- 查询数据库中不同用户的分区表的数目
select owner,count(1) from dba_tables where partitioned='YES' group By owner;
 -- 查询数据库中用户的分区表
select * from dba_tables where partitioned='YES'  and owner='数据库用户名' ; 
 --查询数据库中 该用户下的对应表的分区字段
select * from dba_part_key_columns where name='表名' and owner ='数据库用户名'; 
 --查看该数据库中 所有用户的 所有分区表的和对应分区字段
SELECT * FROM all_PART_KEY_COLUMNS;
SELECT * FROM all_PART_KEY_COLUMNS t where  t.owner='数据库用户名'  and  t.name  in（select table_name from dba_tables where partitioned='YES'  and owner='数据库用户名' ）; 
 --查询数据库中，该用户下对应的分区表的表名 和分区表所对应的分区字段

Oracle分区表以及分区索引理论：
1）创建时机

• 2G以上的表，ORACLE推荐创建分区
• 分区的字段一定要是经常用以提取数据的字段，否则会在提取过程中导致遍历多个分区，这样比没有分区还要慢
• 分区字段要选择合适，数据较为均匀分布到各个分区，不要太多也不要太少，而且根据分区字段可以很快定位到分区范围
• 分区的方式根据实际情况而定，才能提高整体性能

2）分区类别：分区主要有RANG、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集群中，若存在多分区提取数据，适当采用并行提取可以提高提取的速度。 对于索引部分，这里也只提到分区索引的创建方式以及常见索引的维护方式，对于索引原理理解后会更容易认识到提取数据时的技巧