上篇（http://space.itpub.net/17203031/viewspace-765160）中，我们讨论了一个使用函数索引，解决倾斜列查询的问题。本质上说，这个问题的核心在于前期规划和调研阶段，对于JOB_LOG数据表没有一个清晰的认识，让数据表数据在表中活跃程度差异大，才会有当前的困扰。
下面的一个案例，可以说是需求概念对设计进行绑架的案例。
2、“视图主键列”的优化
这个题目本身就是听上去有一些矛盾。普通Oracle视图View就是存储在数据字典里面的一个SQL语句，对View的操作本质就是对这个预存的SQL执行。普通View根本没有段结构（Segment），何来主键一说。
问题是这样：数据设计中存在两个实体对象，代理人和航空公司。两者无论在业务规则，还是字段取值上都没有什么共同点。原始设计的时候，将其作为两个单独数据表进行归类。由于不能保证业务主键唯一特性，使用sequence作为主键。
--Agent数据表
SQL> create table agent (id number, agt_code varchar2(100), agt_name varchar2(100));
Table created
SQL> alter table agent add constraint pk_agent_id primary key (id);
Table altered
--AGENT数据表主键生成器
SQL> create sequence seq_agent;
Sequence created
--Airline数据表
SQL> create table air (id number, air_code varchar2(100), air_name varchar2(100));
Table created
SQL> alter table air add constraint pk_air_id primary key (id);
Table altered
--AIR数据表主键生成器
SQL> create sequence seq_air;
Sequence created
使用代码分别生成10万数据量。
SQL> declare
2 i number;
3 begin
4 for i in 1..100000 loop
5 insert into agent values(seq_agent.nextval, 'CODE'||i, 'AGENT'||i);
7 if (i mod 1001=0) then
8 commit;
9 end if;
10 end loop;
11
12 commit;
13 end;
14 /
PL/SQL procedure successfully completed
SQL> select count(*) from agent;
COUNT(*)
----------
100000
SQL>
（篇幅原因，对AIR数据表插入脚本过程省略......）
PL/SQL procedure successfully completed
SQL> select count(*) from air;
COUNT(*)
----------
100000
此时，当我们使用两个数据表的主键进行检索，执行计划比如是主键列索引。
SQL> explain plan for select * from agent where id=1000;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
Plan hash value: 960472470
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 117 | 0 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID| AGENT | 1 | 117 | 0 (0)|
|* 2 | INDEX UNIQUE SCAN | PK_AGENT_ID | 1 | | 0 (0)|
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=1000)
14 rows selected
前端使用JPA（Hibernate）访问数据，这种ORM框架的最大好处是提供给开发人员一套类对象方式操作数据库的方法，缺点在于PA会带来一些性能和兼容性问题。
AGENT和AIR看起来现在相安无事。过一段时间，需求要求在业务概念中，将两个对象统一为Participant的概念。也就是说，无论是AIRLINE还是AGENT，都是Participant，后续一些模块中还有对于Participant的统一操作。
如果从经典的ORM映射模型上看，对象实体层可以通过父类带子类的方法，满足映射模型。在数据库关系表中，一些公共字段也可以通过统一命名来实现映射。但是，这样做的缺点是要破坏原有的数据库开发规范和设计原则。
而且，如果尝试建立额外的数据表Participant，来保存公共属性，那么可能导致出现冗余不一致数据的倾向。
笔者选择了视图。View并不是一个数据表，那么JPA是怎么看待视图的呢？答案是：JPA并不在意它映射的是数据表还是视图。JPA操作的原理是：根据实体映射配置信息，将实体操作转化为一系列的SQL语句。如果SQL可以执行，并且没有从Oracle端报错，JPA就不会有问题。而对于select语句，视图和普通表没有任何区别。
下面使用union创建视图对象。
SQL> create or replace view v_parti as
2 select 'AGT'||id parti_id, 'AGENT' parti_type,
3 agt_code parti_code, agt_name parti_name,
4 rowid rwid
5 from agent
6 union all
7 select 'AIR'||id, 'AIR' parti_type,
8 air_code parti_code, air_name parti_name,
9 rowid rwid
10 from air;
View created
注意，我们构建parti_id作为视图的“主键”。在JPA中，如果作为实体类（非关联表），一定要有一个唯一主键进行实体对象确认。如果是一个数据表，一般都要求有一个数据库主键列，或者能确定的非空唯一列作为主键列配置在Java实体里面。
作为视图v_participant，是将两个数据表进行union合并操作。两个表虽然都有主键id，但是由于分别生成自两个sequence对象，是不能保证唯一特性的。
笔者选择了一个取巧的方法，就是根据不同的类型（AGT、AIR）CODE添加在id之前，保证了不同表来源的数据主键取值不可能相同。
SQL> select * from v_parti where rownum<5;
PARTI_ID PARTI_TYPE PARTI_CODE PARTI_NAME RWID
--------------- ---------- ---------- --------------- ------------------
AGT101839 AGENT CODE1829 AGENT1829 AAAWfjAALAABSwEAAA
AGT101840 AGENT CODE1830 AGENT1830 AAAWfjAALAABSwEAAB
AGT101841 AGENT CODE1831 AGENT1831 AAAWfjAALAABSwEAAC
AGT101842 AGENT CODE1832 AGENT1832 AAAWfjAALAABSwEAAD
SQL> select count(*) from v_parti;
COUNT(*)
----------
200000 --总数据量20万
而且，视图v_parti的“主键列”不会重叠。
SQL> select * from v_parti a where exists (select * from v_parti b where a.parti_id=b.parti_id and a.rwid<>b.rwid);
PARTI_ID PARTI_TYPE PARTI_CODE PARTI_NAME RWID
--------------- ---------- ---------- --------------- -----------
看似相安无事，但是之后，笔者在AWR报告中频繁看到对视图v_participant根据主键列检索性能问题的提示。
通过执行计划可以看到。
SQL> set timing on;
SQL> select * from v_parti where parti_id='AGT101842';
PARTI_ID PARTI_TYPE PARTI_CODE PARTI_NAME RWID
--------------- ---------- ---------- --------------- ------------------
AGT101842 AGENT CODE1832 AGENT1832 AAAWfjAALAABSwEAAD
Executed in 0.14 seconds --作为一个主键条件，不是容易接受～
SQL> explain plan for select * from v_parti where parti_id='AGT101842';
Explained
Executed in 0.047 seconds
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
Plan hash value: 4157795185
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2000 | 279K| 280 (2)| 00:00:04 |
| 1 | VIEW | V_PARTI | 2000 | 279K| 280 (2)| 00:00:04 |
| 2 | UNION-ALL | | | | | |
|* 3 | TABLE ACCESS FULL| AGENT | 1000 | 26000 | 140 (2)| 00:00:02 |
|* 4 | TABLE ACCESS FULL| AIR | 1000 | 24000 | 140 (2)| 00:00:02 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter('AGT'||TO_CHAR("ID")='AGT101842')
4 - filter('AIR'||TO_CHAR("ID")='AGT101842')
17 rows selected
Executed in 0.171 seconds
检索过程中，Oracle将查询条件推入到视图中，检索“AGT”||id和“AIR”||id，整个过程进行的是全表扫描FTS。
FTS执行计划最大的问题其实不在于执行时间的长短一方面因素，而在于执行时间随着数据表数据量的变化而发生变化，呈现出一种不稳定性。
如果依据这个方向进行优化，函数索引其实是一种不错的方法。我们考虑将拼接字符串操作作为一种函数类型进行索引话。补充一下，为了提高“描述度”，我们选择唯一索引。
SQL> create unique index idx_air_fid on air('AIR'||id);
Index created
Executed in 0.359 seconds
SQL> create unique index idx_agent_fid on agent('AGT'||id);
Index created
Executed in 0.187 seconds
SQL> exec dbms_stats.gather_table_stats(user,'AIR',cascade => true);
PL/SQL procedure successfully completed
Executed in 0.468 seconds
SQL> exec dbms_stats.gather_table_stats(user,'AGENT',cascade => true);
PL/SQL procedure successfully completed
Executed in 0.437 seconds
Oracle函数索引的本质，是在内部维护一个不可见列，对这个不可见列进行函数化操作。
SQL> select column_name, HIDDEN_COLUMN from dba_tab_cols where wner='NBS' and table_name='AGENT' order by column_id;
COLUMN_NAME HIDDEN_COLUMN
------------------------------ -------------
ID NO
AGT_CODE NO
AGT_NAME NO
SYS_NC00004$ YES
Executed in 0.172 seconds
SQL> select column_name, HIDDEN_COLUMN from dba_tab_cols where wner='NBS' and table_name='AIR' order by column_id;
COLUMN_NAME HIDDEN_COLUMN
------------------------------ -------------
ID NO
AIR_CODE NO
AIR_NAME NO
SYS_NC00004$ YES
Executed in 0.156 seconds
之后，检查执行计划。
SQL> set timing on;
SQL> select * from v_parti where parti_id='AGT101842';
PARTI_ID PARTI_TYPE PARTI_CODE PARTI_NAME RWID
--------------- ---------- ---------- --------------- ------------------
AGT101842 AGENT CODE1832 AGENT1832 AAAWfjAALAABSwEAAD
Executed in 0.047 seconds
SQL> explain plan for select * from v_parti where parti_id='AGT101842';
Explained
Executed in 0.062 seconds
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
Plan hash value: 2190257821
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%C
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 286 | 4
| 1 | VIEW | V_PARTI | 2 | 286 | 4
| 2 | UNION-ALL | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| AGENT | 1 | 36 | 2
|* 4 | INDEX UNIQUE SCAN | IDX_AGENT_FID | 1 | | 1
| 5 | TABLE ACCESS BY INDEX ROWID| AIR | 1 | 33 | 2
|* 6 | INDEX UNIQUE SCAN | IDX_AIR_FID | 1 | | 1
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access('AGT'||TO_CHAR("ID")='AGT101842')
6 - access('AIR'||TO_CHAR("ID")='AGT101842')
19 rows selected
Executed in 0.858 seconds
执行时间从原来的0.14s下降到现在的0.047，执行成本估算值也从280下降到4。而且，随着AGENT和AIR体积的增加，有函数索引的SQL执行效率是稳定的。
3、尾声
最后，我们反思一下这两个案例。在笔者过去的文章中，谈及函数索引的时候，都是强调“函数索引在开发阶段尽量少用或者不用，函数索引是给运维人员进行系统补救使用的”。在这两个案例中，我们是在进行开发阶段的“补救”。
在第一个案例中，我们违反了数据活跃度和数据流入流出原则。一个数据表，单向的增加或者减少都是违法数据流入和流出原则。数据表无论多大还是多小，在初始业务阶段规划的时候，就要进行合理的布局。同时，一个数据表数据要尽量具备相同业务活跃度，也就是参与业务过程几率相似。
对JOB_LOG表，如果我们根据活性将其拆分为JOB_LOG_ONLINE和JOB_LOG_HISTORY两个表。新作业和处理中的作业记录保存在JOB_LOG_ONLINE中，一旦处理结束，就“扔”到HISTORY表里面去。页面前端可以通过Union合并的方法进行处理。这样ONLINE表就能做到小体积、快速处理。
如果我们采用的这样的方案，本篇中的函数索引就没有必要了，创建的“小索引”价值就不那么高了。
在第二个案例中，是我们没有对需求业务进行明确的全局把握造成的。如果能够确定多个业务实体最终要合并为Participant进行处理，我们在选择各个表主键方面就有其他的选择。比如选择字符串类型作为主键，“PARTI_CODE”||Seq_num方法，这样在合并为视图之后，我们就可以直接利用Oracle谓词推入的性质，将PARTI_ID条件推入到各个union表中，借用原有的主键id实现功能。同样，函数索引也就不需要了。
笔者认为：全局、合理和平衡的设计，特别是在业务前期对数据模型的规划和需求补全工作，在应用系统开发过程中扮演着重要的作用和意义。值得我们关注。