5年前面试题引发的“血案”（2）(oracle的分区表)

昨天的题3笔者是真的不会，不是卖萌。至于你信不信，我反正是信了……

题3：本地索引与全局索引的差别与适用情况。

答：那说到本地索引和全局索引，必须要从分区说起了。

oracle 引入了分区表的技术（partitioned table），分区表对于应用来说是完全透明的，其目的完全是为了管理方便和性能提升。分区分区，顾名思义简，无非就是把一个表按照某种方式分成几个区 域，以此来提高性能。为什么分成几个区域就可以提高性能了呢？也很简单，在存读的时候并不是以表为单位，而是以分区为单位，通过不对全表做扫描，过滤掉无 用数据来提高性能和响应速度。举个例子，如果时间分区，比如按照12个月来进行分区，那查询或者修改和12月有关的数据时，oracle就会忽略剩余的 11个月了。

其实可以把分区表理解成很多小表的集合，这些小表都是独立的，有自己对应的物理空间。在逻辑上来说，这些表有相同的列和约束，分区表这个大表本身也而只是一个逻辑结构，而分区主要是根据对分区键（列）来判断的。我们来看几个例子。

在oracle中分区有4个方式，简单说明一下，有帮助对分区索引的理解。

1.范围分区（range partition），不知道笔者这样翻译好不好，意思是根据值的范围来划分索引。例子如下：

CREATE TABLE sales_range
(salesman_id NUMBER(5),
salesman_name VARCHAR2(30),
sales_amount NUMBER(10),
sales_date DATE)
PARTITION BY RANGE(sales_date)
(
PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','MM/DD/YYYY')),
PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','MM/DD/YYYY')),
PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','MM/DD/YYYY')),
PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','MM/DD/YYYY'))
);（摘自 oracle 10g concepts）

很 好理解的吧，按照时间分成了4个分区。通常情况下还会有一个 partiontion sales_max values less than (maxvalue)这样的语句，要不然如果你插入的时间大于5月就会报错了，maxvalue可以理解成一个无穷大的值，这个值也包含了value。

2.列表分区（list partition），列表分区，和范围分区不同，列表的话是通过枚举的方式来分区的，那对于文本或者是没有逻辑关系的值用列表分区是合适不过了。

例子如下：

CREATE TABLE sales_list
(salesman_id NUMBER(5),
salesman_name VARCHAR2(30),
sales_state VARCHAR2(20),
sales_amount NUMBER(10),
sales_date DATE)
PARTITION BY LIST(sales_state)
(
PARTITION sales_west VALUES('California', 'Hawaii'),
PARTITION sales_east VALUES ('New York', 'Virginia', 'Florida'),
PARTITION sales_central VALUES('Texas', 'Illinois'),
PARTITION sales_other VALUES(DEFAULT)
);（）（摘自 oracle 10g concepts）

也是很简单很容易理解的，注意的是DEFAULT，如果不指定而又插入了以外的值的话是会报错的。并且List是不允许指定多列的，只允许指定单列，这个也是要注意的。

3.哈希分区（hash partition），这个翻译总不会错吧，哈哈

CREATE TABLE sales_hash
(salesman_id NUMBER(5),
salesman_name VARCHAR2(30),
sales_amount NUMBER(10),
week_no NUMBER(2))
PARTITION BY HASH(salesman_id)
PARTITIONS 4
STORE IN (ts1, ts2, ts3, ts4);（摘自 oracle 10g concepts）

那hash分区是几个分区里比较有意思的，只需要指定分区列和分区个数就可以了，由oracle自动来管理。那可以想到的，如果字段值不易于划分，或者手工等分整张表比较困难的话，那可以使用hash，相比前几种可能更加简单。

4.组合分区（composite partition）

所 谓组合分区就是前3种的组合了，简单来说就是分区以后对每个分区再分区（拗口啊……），组合分区也不能乱组合，只提供了两种，一种是范围哈希分区 （range-hash partition），一种是范围列表分区（range-list partiotion），我们还是看例子：

范围组合分区：

CREATE TABLE sales_composite
(salesman_id NUMBER(5),
salesman_name VARCHAR2(30),
sales_amount NUMBER(10),
sales_date DATE)
PARTITION BY RANGE(sales_date)
SUBPARTITION BY HASH(salesman_id)
SUBPARTITION TEMPLATE(
SUBPARTITION sp1 TABLESPACE ts1,
SUBPARTITION sp2 TABLESPACE ts2,
SUBPARTITION sp3 TABLESPACE ts3,
SUBPARTITION sp4 TABLESPACE ts4)
(PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','MM/DD/YYYY'))
PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','MM/DD/YYYY'))
PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','MM/DD/YYYY'))
PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','MM/DD/YYYY'))
PARTITION sales_may2000 VALUES LESS THAN(TO_DATE('06/01/2000','MM/DD/YYYY')));

简单来说就是每个范围分区完成后，又会被分为4个hash分区，可以理解成先按照存储的类型分成若干个抽屉（partition），每个抽屉再分成几格存放不同的型号和颜色（subpartition）。

至于range-list也是差不读的，笔者偷懒不贴了。

说了这么多……貌似还是没有说到索引么……别急，索引来了。

其实说到这里，如果大家思考了的话，本地索引和全局索引的概念大概也已经猜出来了，没错，本地索引也就是针对不同分区建立不同的索引，彼此间相互独立，而全局索引则是针对整个分区表进行索引的，也就是说和分区没有任何关系。

两者的优劣也就可以管中窥豹了。

本地索引和分区一样，彼此相互独立，那管理自然是更加容易，而且如果是需要生成报表的而集中对某个分区进行分析和读写的话，那效率和性能的表现自然是相当令人满意，速度也会快得多，所以常用于数据仓库，数据挖掘这样的系统中。

全局索引的话和分区完全没有关系，那如果建立这样的索引的话，一定是为了查询的效率和速度，在对表中的数据进行随机访问的时候，全局索引可以保证每条数据都可以差不多的效率被存取到，那自然也是应用在OLTP的系统中啦。

其实要说答案，就上面这么两段，但是笔者这么废话，把分区有关的概念和信息和大家分享了一下，笔者看来，下面的如果对分区有一定的了解的话，那本地索引和全 局索引动动脑子就可以出来了，而且理解得更为透彻，概念这东西，死记硬背不行，还是要理解。技术切忌浮躁，扎扎实实才是王道啊！（另外，如果要细说分区表 的索引的话还可以写一篇差不多长的……留待整个血案终结后的番外篇吧！）

回归正题，我们来看

题5：一个表a varchar2(1),b number(1),c char(2)，有100000条记录，创建B-Tree索引在字段a上，那么表与索引谁大？为什么？

实战题，非常精彩，这个时候依照惯例，是一定要留待血案（3）的！（别打人！勿打脸！）

（其实整篇主要还是讲的分区，索引这块笔者也准备好好花篇幅整理整理，请各位看官期待下篇）

本文出自 “oracle小白前进之路” 博客，请务必保留此出处http://oxiaobai.blog.51cto.com/3369332/626422