EnterpriseDB中有意思的特性（一）

1.表的继承

例子：系统中需要处理雇员和经理两种数据。经理包含雇员的数据，同时包含一些额外字段。

 1 CREATE TABLE emp (
 2     empno           NUMBER(4) NOT NULL CONSTRAINT emp_pk PRIMARY KEY,
 3     ename           VARCHAR2(10),
 4     job             VARCHAR2(9),
 5     mgr             NUMBER(4),
 6     hiredate        DATE,
 7     sal             NUMBER(7,2) CONSTRAINT emp_sal_ck CHECK (sal > 0),
 8     comm            NUMBER(7,2),
 9     deptno          NUMBER(2) CONSTRAINT emp_ref_dept_fk
10                         REFERENCES dept(deptno)
11 );
12 
13 
14 
15 CREATE TABLE director (
16    director_allowance NUMBER (10)
17 ) INHERITS (emp);
18 
19 INSERT INTO director VALUES (8002,'ALEX','DIRECTOR',7839,'23-DEC-81',3000,NULL,20,1000);
20 INSERT INTO director VALUES (8008,'KENNETH','DIRECTOR',7839,'01-AUG-81',3850,NULL,30,1500);
21 INSERT INTO director VALUES (7009,'RON','DIRECTOR',7839,'17-OCT-81',4000,NULL,10,2500);
22 

查询所有收入超过1500的雇员（包括经理）：

SELECT ename, sal 
    FROM emp
    WHERE sal > 1500;

查询雇员数据（不包括经理）：
注意表名前的ONLY关键字。UPDATE、DELETE等命令都支持ONLY。

SELECT ename, sal 
    FROM ONLY emp
    WHERE sal > 1500;

2.分页(Partitioning)

 

    EnterpriseDB支持表分页的技术，把一个逻辑上的表划分为不同的物理分块。分页提供了以下的好处：

a)某些情况下可以极大程度的提升查询的效率。配合各个物理分块的约束条件的使用，可以把查询快速定位到一个或者几个物理部分上。
b)需要查询、更新一个物理分块中的大部分内容时，可以用扫描这个物理分块，而不是使用索引，从而提升效率
c)如果合理的设计物理分块，可以用分块的增加、删除来替代批量操作。
d)使用频率低的分块，可以用效率低的存储介质实现，如磁带机等。

    合理的设计分页，在表数据量很大时可以发生明显作用，甚至可以在表总数据量超过数据库服务器的物理内存时也有较高的效率。
    目前EnterpriseDB通过表继承来实现分页，提供以下两种形式：
1)范围分页
    按照某些字段的取值范围，不重叠的划分；
2)列表分页
  按照键值进行划分；
两种方式本质上没有区别。

2.1 分页的实现方式

1. 建立一个表作为所有分块表的父表。该表不包括数据、索引、约束
2. 建立各个子表，作为物理分块部分。
3. 在各个子表上增加约束。约束需要保证各个子表中没有重叠的数据
4. 建立修改规则，把对父表的修改重新定位到子表上（这一步不是必须的）
5. 保证postgresql.conf配置中的constraint_exclusion 是打开的，否则，查询不会进行优化。

2.2 例子

假设我们为一个冰激凌厂商建立数据库。他们记录每天的最高，以及各个地区的冰激凌销量。

CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
);

在应用中，经常需要查询上一周、上一个月以及上一季度的数据。为了减少数据量，应用只保留最近三年的数据，在每个月开始时删除三年以前的数据。
步骤：
1.建立父表measurement
2.建立各个月的数据表，按照时间建立不重叠的约束：

CREATE TABLE measurement_yy04mm02 (
    CHECK ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01' )
) INHERITS (measurement);
CREATE TABLE measurement_yy04mm03 (
    CHECK ( logdate >= DATE '2004-03-01' AND logdate < DATE '2004-04-01' )
) INHERITS (measurement);

CREATE TABLE measurement_yy05mm11 (
    CHECK ( logdate >= DATE '2005-11-01' AND logdate < DATE '2005-12-01' )
) INHERITS (measurement);
CREATE TABLE measurement_yy05mm12 (
    CHECK ( logdate >= DATE '2005-12-01' AND logdate < DATE '2006-01-01' )
) INHERITS (measurement);
CREATE TABLE measurement_yy06mm01 (
    CHECK ( logdate >= DATE '2006-01-01' AND logdate < DATE '2006-02-01' )
) INHERITS (measurement);

3.按照需要增加索引

CREATE INDEX measurement_yy2004mm02_logdate ON measurement_yy2004mm02 (logdate);
CREATE INDEX measurement_yy2004mm03_logdate ON measurement_yy2004mm03 (logdate);

CREATE INDEX measurement_yy2005mm11_logdate ON measurement_yy2005mm11 (logdate);
CREATE INDEX measurement_yy2005mm12_logdate ON measurement_yy2005mm12 (logdate);
CREATE INDEX measurement_yy2006mm01_logdate ON measurement_yy2006mm01 (logdate);

4.增加修改规则
如果数据只会加到最后一个月的数据中，因此增加一个修改规则，把对父表的增加操作重新定位到合适月份的数据表中：

CREATE OR REPLACE RULE measurement_current_partition AS
ON INSERT TO measurement
DO INSTEAD
    INSERT INTO measurement_yy2006mm01 VALUES ( NEW.city_id,
                                              NEW.logdate,
                                              NEW.peaktemp,
                                              NEW.unitsales );

如果可以修改原有的数据，可以使用规则的WHERE处理：

CREATE RULE measurement_insert_yy2004mm02 AS
ON INSERT TO measurement WHERE
    ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01' )
DO INSTEAD
    INSERT INTO measurement_yy2004mm02 VALUES ( NEW.city_id,
                                              NEW.logdate,
                                              NEW.peaktemp,
                                              NEW.unitsales );

CREATE RULE measurement_insert_yy2005mm12 AS
ON INSERT TO measurement WHERE
    ( logdate >= DATE '2005-12-01' AND logdate < DATE '2006-01-01' )
DO INSTEAD
    INSERT INTO measurement_yy2005mm12 VALUES ( NEW.city_id,
                                              NEW.logdate,
                                              NEW.peaktemp,
                                              NEW.unitsales );
CREATE RULE measurement_insert_yy2006mm01 AS
ON INSERT TO measurement WHERE
    ( logdate >= DATE '2006-01-01' AND logdate < DATE '2006-02-01' )
DO INSTEAD
    INSERT INTO measurement_yy2006mm01 VALUES ( NEW.city_id,
                                              NEW.logdate,
                                              NEW.peaktemp,
                                              NEW.unitsales );

注意规则中的WHERE条件和分块表的约束一致。

2.3 效率上的优势：

1) 删除一个月的数据可以使用Drop Table命令，效率比delete命令高：

 

DROP TABLE measurement_y2003m02;

2) 查询优化

  在过滤条件和分块表的约束条件匹配的情况下，查询不需要加载所有的分块，而只处理满足约束的分块表。
  不使用优化的查询计划：

SET constraint_exclusion = off;
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2006-01-01';

                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Aggregate  (cost=158.66..158.68 rows=1 width=0)
   ->  Append  (cost=0.00..151.88 rows=2715 width=0)
         ->  Seq Scan on measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2006-01-01'::date)
         ->  Seq Scan on measurement_yy2004mm02 measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2006-01-01'::date)
         ->  Seq Scan on measurement_yy2004mm03 measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2006-01-01'::date)

         ->  Seq Scan on measurement_yy2005mm12 measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2006-01-01'::date)
         ->  Seq Scan on measurement_yy2006mm01 measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2006-01-01'::date)

使用查询优化的执行计划

SET constraint_exclusion = on;
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2006-01-01';
                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Aggregate  (cost=63.47..63.48 rows=1 width=0)
   ->  Append  (cost=0.00..60.75 rows=1086 width=0)
         ->  Seq Scan on measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2006-01-01'::date)
         ->  Seq Scan on measurement_yy2006mm01 measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2006-01-01'::date)

3. 层次化查询（处理树形结构的数据）

 EnterpriseDB定义了一套完整语法，来查询记录之间有父子关系的数据：

SELECT select_list
FROM table_expression
[ WHERE  ]
[ START WITH start_expression ]
CONNECT BY { PRIOR parent_expr = child_expr | child_expr = PRIOR
parent_expr }
[ ORDER SIBLINGS BY column1 [ ASC | DESC ] [, column2 [ ASC | DESC ] ] 
[ GROUP BY  ]
[ HAVING  ]
[ other  ]

（一）父子关系的确定
CONNECT BY子句用来确定父子关系。对于每一给定记录行，用以下方式确定子记录：
1) 在当前行中求parent_expr
2) 遍历本行之外的记录中，求child_expr表达式值，如果值与第一步相同，则为当前行的子。
（二）根节点的确定
START WITH条件用来过滤根节点，满足start_expression的记录都会作为根记录。

3.1 例子

例子的数据库使用雇员、经理例子，按照上下级关系选出雇员。

SELECT ename, empno, mgr 
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr;

执行结果

ename  | empno | mgr
--------+-------+------
KING   |  7839 |
JONES  |  7566 | 7839
SCOTT  |  7788 | 7566
ADAMS  |  7876 | 7788
FORD   |  7902 | 7566
SMITH  |  7369 | 7902
BLAKE  |  7698 | 7839
ALLEN  |  7499 | 7698
WARD   |  7521 | 7698
MARTIN |  7654 | 7698
TURNER |  7844 | 7698
JAMES  |  7900 | 7698
CLARK  |  7782 | 7839
MILLER |  7934 | 7782
(14 rows)

确定节点的级别。LEVEL是一个伪列（系统内置的宏），描述记录在树形结构中的层次关系，根节点层次为1。

SELECT LEVEL, LPAD (' ', 2 * (LEVEL - 1)) || ename "employee", empno, mgr
FROM emp START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr;

 

执行 结果 

level |  employee   | empno | mgr
-------+-------------+-------+------
1 | KING        |  7839 |
2 |   JONES     |  7566 | 7839
3 |     SCOTT   |  7788 | 7566
4 |       ADAMS |  7876 | 7788
3 |     FORD    |  7902 | 7566
4 |       SMITH |  7369 | 7902
2 |   BLAKE     |  7698 | 7839
3 |     ALLEN   |  7499 | 7698
3 |     WARD    |  7521 | 7698
3 |     MARTIN  |  7654 | 7698
3 |     TURNER  |  7844 | 7698
3 |     JAMES   |  7900 | 7698
2 |   CLARK     |  7782 | 7839
3 |     MILLER  |  7934 | 7782
(14 rows)

 子的排序使用ORDER SIBLINGS BY 字句。比如，按照名字的次序排序结果：

SELECT LEVEL, LPAD (' ', 2 * (LEVEL - 1)) || ename "employee", empno, mgr
FROM emp START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr
ORDER SIBLINGS BY ename ASC;

执行结果

level |  employee   | empno | mgr
-------+-------------+-------+------
1 | KING        |  7839 |
2 |   BLAKE     |  7698 | 7839
3 |     ALLEN   |  7499 | 7698
3 |     JAMES   |  7900 | 7698
3 |     MARTIN  |  7654 | 7698
3 |     TURNER  |  7844 | 7698
3 |     WARD    |  7521 | 7698
2 |   CLARK     |  7782 | 7839
3 |     MILLER  |  7934 | 7782
2 |   JONES     |  7566 | 7839
3 |     FORD    |  7902 | 7566
4 |       SMITH |  7369 | 7902
3 |     SCOTT   |  7788 | 7566
4 |       ADAMS |  7876 | 7788
(14 rows)

配合使用WHERE来过滤记录：

SELECT LEVEL, LPAD (' ', 2 * (LEVEL - 1)) || ename "employee", empno, mgr
FROM emp WHERE mgr IN (7839, 7782, 7902, 7788)
START WITH ename IN ('BLAKE','CLARK','JONES')
CONNECT BY PRIOR empno = mgr
ORDER SIBLINGS BY ename ASC;

执行结果

 level | employee  | empno | mgr
-------+-----------+-------+------
1 | BLAKE     |  7698 | 7839
1 | CLARK     |  7782 | 7839
2 |   MILLER  |  7934 | 7782
1 | JONES     |  7566 | 7839
3 |     SMITH |  7369 | 7902
3 |     ADAMS |  7876 | 7788
(6 rows)