上篇（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

-------------------------------------------------------------------------------

-------------------------------------------------------------------------------

| 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

-----------------------------------------------------------------------------

-------------------------------------------------------------------------------

| 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

----------------------------------------------------------------------------

----------------------------------------------------------------------------

| 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實現功能。同樣，函式索引也就不需要了。

筆者認為：全域性、合理和平衡的設計，特別是在業務前期對資料模型的規劃和需求補全工作，在應用系統開發過程中扮演著重要的作用和意義。值得我們關注。

函式索引的兩個應用場景示例（下）

相關文章