優(yōu)化Oracle庫(kù)表設(shè)計(jì)的若干方法(1)

2024-08-29 13:43:45

字體：大中小

供稿：網(wǎng)友

前言

絕大多數(shù)的Oracle數(shù)據(jù)庫(kù)性能問(wèn)題都是由于數(shù)據(jù)庫(kù)設(shè)計(jì)不合理造成的，只有少部分問(wèn)題根植于Database Buffer、Share Pool、Redo Log Buffer等內(nèi)存模塊配置不合理，I/O爭(zhēng)用，CPU爭(zhēng)用等DBA職責(zé)范圍上。所以除非是面對(duì)一個(gè)業(yè)已完成不可變更的系統(tǒng)，否則我們不應(yīng)過(guò)多地將關(guān)注點(diǎn)投向內(nèi)存、I/O、CPU等性能調(diào)整項(xiàng)目上，而應(yīng)關(guān)注數(shù)據(jù)庫(kù)表本身的設(shè)計(jì)是否合理，庫(kù)表設(shè)計(jì)的合理性才是程序性能的真正執(zhí)牛耳者。

合理的數(shù)據(jù)庫(kù)設(shè)計(jì)需要考慮以下的方面：

·業(yè)務(wù)數(shù)據(jù)以何種方式表達(dá)。如一個(gè)員工有多個(gè)Email，你可以在T_EMPLOYEE表中建立多個(gè)Email字段如email_1、email_2、 email_3，也可以創(chuàng)建一個(gè)T_EMAIL子表來(lái)存儲(chǔ)，甚至可以用逗號(hào)分隔開(kāi)多個(gè)Email地址存放在一個(gè)字段中。

·數(shù)據(jù)以何種方式物理存儲(chǔ)。如大表的分區(qū)，表空間的合理設(shè)計(jì)等。

·如何建立合理的數(shù)據(jù)表索引。表索引幾乎是提高數(shù)據(jù)表查詢性能最有效的方法，Oracle擁有類型豐富的數(shù)據(jù)表索引類型，如何取舍選擇顯得非凡重要。

本文我們將目光主要聚焦于數(shù)據(jù)表的索引上，同時(shí)也將提及其他兩點(diǎn)的內(nèi)容。通過(guò)對(duì)一個(gè)簡(jiǎn)單的庫(kù)表設(shè)計(jì)實(shí)例的分析引出設(shè)計(jì)中的不足，并逐一改正。考慮到手工編寫庫(kù)表的SQL腳本原始且低效，我們將用目前最流行的庫(kù)表設(shè)計(jì)工具PowerDesigner 10來(lái)講述表設(shè)計(jì)的過(guò)程，所以在本文中你還會(huì)了解到一些相關(guān)的PowerDesigner的使用技巧。

一個(gè)簡(jiǎn)單的例子

某個(gè)開(kāi)發(fā)人員著手設(shè)計(jì)一個(gè)訂單的系統(tǒng)，這個(gè)系統(tǒng)中有兩個(gè)主要的業(yè)務(wù)表，分別是訂單基本信息表和訂單條目表，這兩張表具有主從關(guān)系的表，其中T_ORDER是訂單主表，而T_ORDER_ITEM是訂單條目表。數(shù)據(jù)庫(kù)設(shè)計(jì)人員的設(shè)計(jì)成果如圖 1所示：

優(yōu)化Oracle庫(kù)表設(shè)計(jì)的若干方法(1)（圖一）

優(yōu)化Oracle庫(kù)表設(shè)計(jì)的若干方法(1)（圖一）

圖 1 訂單主從表

ORDER_ID是訂單號(hào)，為T_ORDER的主鍵，通過(guò)名為SEQ_ORDER_ID的序列產(chǎn)生鍵值，而ITEM_ID是T_ORDER_ITEM表的主鍵，通過(guò)名為SEQ_ORDER_ITEM的序列產(chǎn)生鍵值，T_ORDER_ITEM通過(guò)ORDER_ID外鍵關(guān)聯(lián)到T_ORDER表。

需求文檔指出訂單記錄將通過(guò)以下兩種方式來(lái)查詢數(shù)據(jù)：

·CLIENT + ORDER_DATE+IS_SHPPED：根據(jù)"客戶＋訂貨日期+是否發(fā)貨"條件查詢訂單及訂單條目。

·ORDER_DATE+IS_SHipPED：根據(jù)"訂貨日期+是否發(fā)貨"條件查詢訂單及訂單條目。

數(shù)據(jù)庫(kù)設(shè)計(jì)人員根據(jù)這個(gè)要求，在T_ORDER表的CLIENT、 ORDER_DATE及IS_SHPPED三字段上建立了一個(gè)復(fù)合索引IDX_ORDER_COMPOSITE；在T_ORDER_ITEM為外鍵 ORDER_ID建立IDX_ORDER_ITEM_ORDER_ID索引。

讓我們看一下該份設(shè)計(jì)的最終SQL腳本：

/*訂單表*/

create table T_ORDER (

ORDER_ID NUMBER(10) not null,

ADDRESS VARCHAR2(100),

CLIENT VARCHAR2(60),

ORDER_DATE CHAR(8),

IS_SHIPPED CHAR(1),

constraint PK_T_ORDER PRimary key (ORDER_ID)

);

create index IDX_CLIENT on T_ORDER (

　CLIENT ASC,

　ORDER_DATE ASC,

　IS_SHIPPED ASC);

/*訂單條目子表*/

create table T_ORDER_ITEM (

　ITEM_ID NUMBER(10) not null,

　ORDER_ID NUMBER(10),

　ITEM VARCHAR2(20),

　COUNT NUMBER(10),

　constraint PK_T_ORDER_ITEM primary key (ITEM_ID)

);

create index IDX_ORDER_ITEM_ORDER_ID on T_ORDER_ITEM (

　ORDER_ID ASC);

　alter table T_ORDER_ITEM add constraint FK_T_ORDER__REFERENCE_T_ORDER foreign key (ORDER_ID) references T_ORDER (ORDER_ID);

我們承認(rèn)在ER關(guān)系上，這份設(shè)計(jì)并不存在的缺陷，但卻存在以下有待優(yōu)化的地方：

·沒(méi)有將表數(shù)據(jù)和索引數(shù)據(jù)存儲(chǔ)到不同的表空間中，而不加區(qū)別地將它們存儲(chǔ)到同一表空間里。這樣，不但會(huì)造成I/O競(jìng)爭(zhēng)，也為數(shù)據(jù)庫(kù)的維護(hù)工作帶來(lái)不便。

·ORACLE會(huì)自動(dòng)為表的主鍵列創(chuàng)建一個(gè)普通B-Tree索引，但由于這兩張表的主鍵值都通過(guò)序列提供，具有嚴(yán)格的順序性（升序或降序），此時(shí)手工為其指定一個(gè)反鍵索引（reverse key index）將更加合理。

·在子表T_ORDER_ITEM外鍵列ORDER_ID上建立的IDX_ORDER_ITEM_ORDER_ID的普通B-Tree索引非常適合設(shè)置為壓縮型索引，即建立一個(gè)壓縮型的B-Tree索引。因?yàn)橐环萦唵螘?huì)對(duì)應(yīng)多個(gè)訂單條目，這就意味著T_ORDER_ITEM表存在許多同值的 ORDER_ID列值，通過(guò)將其索引指定為壓縮型的B-Tree索引，不但可以減少IDX_ORDER_ITEM_ORDER_ID所需的存儲(chǔ)空間，還將提高表操作的性能。

·企圖僅通過(guò)建立一個(gè)包含3字段IDX_ORDER_COMPOSITE復(fù)合索引滿足如前所述的兩種查詢條件方式的索引是有問(wèn)題的，事實(shí)上使用ORDER_DATE+IS_SHIPPED復(fù)合條件的查詢將利用不到IDX_ORDER_COMPOSITE索引。
優(yōu)化設(shè)計(jì)

1、將表數(shù)據(jù)和索引數(shù)據(jù)分開(kāi)表空間存儲(chǔ)

1.1 表數(shù)據(jù)和索引為何需要使用獨(dú)立的表空間

Oracle強(qiáng)烈建立，任何一個(gè)應(yīng)用程序的庫(kù)表至少需要?jiǎng)?chuàng)建兩個(gè)表空間，其中之一用于存儲(chǔ)表數(shù)據(jù)，而另一個(gè)用于存儲(chǔ)表索引數(shù)據(jù)。因?yàn)榧偃鐚⒈頂?shù)據(jù)和索引數(shù)據(jù)放在一起，表數(shù)據(jù)的I/O操作和索引的I/O操作將產(chǎn)生影響系統(tǒng)性能的I/O競(jìng)爭(zhēng)，降低系統(tǒng)的響應(yīng)效率。將表數(shù)據(jù)和索引數(shù)據(jù)存放在不同的表空間中（如一個(gè)為APP_DATA，另一個(gè)為APP_IDX），并在物理層面將這兩個(gè)表空間的數(shù)據(jù)文件放在不同的物理磁盤上，就可以避免這種競(jìng)爭(zhēng)了。

擁有獨(dú)立的表空間，就意味著可以獨(dú)立地為表數(shù)據(jù)和索引數(shù)據(jù)提供獨(dú)立的物理存儲(chǔ)參數(shù)，而不會(huì)發(fā)生相互影響，究竟表數(shù)據(jù)和索引數(shù)據(jù)擁有不同的特性，而這些特性又直接影響了物理存儲(chǔ)參數(shù)的設(shè)定。

此外，表數(shù)據(jù)和索引數(shù)據(jù)獨(dú)立存儲(chǔ)，還會(huì)帶來(lái)數(shù)據(jù)治理和維護(hù)上的方面。如你在遷移一個(gè)業(yè)務(wù)數(shù)據(jù)庫(kù)時(shí)，為了降低數(shù)據(jù)大小，可以只遷出表數(shù)據(jù)的表空間，在目標(biāo)數(shù)據(jù)庫(kù)中通過(guò)重建索引的方式就可以生成索引數(shù)據(jù)了。

1.2 表數(shù)據(jù)和索引使用不同表空間的SQL語(yǔ)法

指定表數(shù)據(jù)及索引數(shù)據(jù)存儲(chǔ)表空間語(yǔ)句最簡(jiǎn)單的形式如下。

將表數(shù)據(jù)存儲(chǔ)在APP_DATA表空間里：

create table T_ORDER ( ORDER_ID NUMBER(10) not null, …)tablespace APP_DATA;

將索引數(shù)據(jù)存儲(chǔ)在APP_IDX表空間里：

create index IDX_ORDER_ITEM_ORDER_ID on T_ORDER_ITEM ( ORDER_ID ASC)tablespace APP_IDX;

1.3 PowerDesigner中如何操作

1) 首先，必須創(chuàng)建兩個(gè)表空間。通過(guò)Model->Tablespace...在List of Tablespaces中創(chuàng)建兩個(gè)表空間：

優(yōu)化Oracle庫(kù)表設(shè)計(jì)的若干方法(1)（圖二）

優(yōu)化Oracle庫(kù)表設(shè)計(jì)的若干方法(1)（圖二）

圖 2 創(chuàng)建表空間

2) 為每張表指定表數(shù)據(jù)存儲(chǔ)的表空間。在設(shè)計(jì)區(qū)中雙擊表，打開(kāi)Table Properties設(shè)計(jì)窗口，切換到options 頁(yè)，按圖 3所示指定表數(shù)據(jù)的存儲(chǔ)表空間。

優(yōu)化Oracle庫(kù)表設(shè)計(jì)的若干方法(1)（圖三）

優(yōu)化Oracle庫(kù)表設(shè)計(jì)的若干方法(1)（圖三）

圖 3 指定表數(shù)據(jù)的存儲(chǔ)表空間

3) 為每個(gè)索引指定索引數(shù)據(jù)的存儲(chǔ)表空間。在Table Properties中切換到Indexes頁(yè)，在這里列出了表的所有索引，雙擊需設(shè)置表空間的索引，在彈出的Index Properties窗口中切換到Options頁(yè)，按如下方式指定索引的存儲(chǔ)表空間。前言

絕大多數(shù)的Oracle數(shù)據(jù)庫(kù)性能問(wèn)題都是由于數(shù)據(jù)庫(kù)設(shè)計(jì)不合理造成的，只有少部分問(wèn)題根植于Database Buffer、Share Pool、Redo Log Buffer等內(nèi)存模塊配置不合理，I/O爭(zhēng)用，CPU爭(zhēng)用等DBA職責(zé)范圍上。所以除非是面對(duì)一個(gè)業(yè)已完成不可變更的系統(tǒng)，否則我們不應(yīng)過(guò)多地將關(guān)注點(diǎn)投向內(nèi)存、I/O、CPU等性能調(diào)整項(xiàng)目上，而應(yīng)關(guān)注數(shù)據(jù)庫(kù)表本身的設(shè)計(jì)是否合理，庫(kù)表設(shè)計(jì)的合理性才是程序性能的真正執(zhí)牛耳者。

合理的數(shù)據(jù)庫(kù)設(shè)計(jì)需要考慮以下的方面：

·業(yè)務(wù)數(shù)據(jù)以何種方式表達(dá)。如一個(gè)員工有多個(gè)Email，你可以在T_EMPLOYEE表中建立多個(gè)Email字段如email_1、email_2、 email_3，也可以創(chuàng)建一個(gè)T_EMAIL子表來(lái)存儲(chǔ)，甚至可以用逗號(hào)分隔開(kāi)多個(gè)Email地址存放在一個(gè)字段中。

·數(shù)據(jù)以何種方式物理存儲(chǔ)。如大表的分區(qū)，表空間的合理設(shè)計(jì)等。

·如何建立合理的數(shù)據(jù)表索引。表索引幾乎是提高數(shù)據(jù)表查詢性能最有效的方法，Oracle擁有類型豐富的數(shù)據(jù)表索引類型，如何取舍選擇顯得非凡重要。

本文我們將目光主要聚焦于數(shù)據(jù)表的索引上，同時(shí)也將提及其他兩點(diǎn)的內(nèi)容。通過(guò)對(duì)一個(gè)簡(jiǎn)單的庫(kù)表設(shè)計(jì)實(shí)例的分析引出設(shè)計(jì)中的不足，并逐一改正?？紤]到手工編寫庫(kù)表的SQL腳本原始且低效，我們將用目前最流行的庫(kù)表設(shè)計(jì)工具PowerDesigner 10來(lái)講述表設(shè)計(jì)的過(guò)程，所以在本文中你還會(huì)了解到一些相關(guān)的PowerDesigner的使用技巧。

一個(gè)簡(jiǎn)單的例子

某個(gè)開(kāi)發(fā)人員著手設(shè)計(jì)一個(gè)訂單的系統(tǒng)，這個(gè)系統(tǒng)中有兩個(gè)主要的業(yè)務(wù)表，分別是訂單基本信息表和訂單條目表，這兩張表具有主從關(guān)系的表，其中T_ORDER是訂單主表，而T_ORDER_ITEM是訂單條目表。數(shù)據(jù)庫(kù)設(shè)計(jì)人員的設(shè)計(jì)成果如圖 1所示：

優(yōu)化Oracle庫(kù)表設(shè)計(jì)的若干方法(1)（圖一）

圖 1 訂單主從表

ORDER_ID是訂單號(hào)，為T_ORDER的主鍵，通過(guò)名為SEQ_ORDER_ID的序列產(chǎn)生鍵值，而ITEM_ID是T_ORDER_ITEM表的主鍵，通過(guò)名為SEQ_ORDER_ITEM的序列產(chǎn)生鍵值，T_ORDER_ITEM通過(guò)ORDER_ID外鍵關(guān)聯(lián)到T_ORDER表。

需求文檔指出訂單記錄將通過(guò)以下兩種方式來(lái)查詢數(shù)據(jù)：

·CLIENT + ORDER_DATE+IS_SHPPED：根據(jù)"客戶＋訂貨日期+是否發(fā)貨"條件查詢訂單及訂單條目。

·ORDER_DATE+IS_SHIPPED：根據(jù)"訂貨日期+是否發(fā)貨"條件查詢訂單及訂單條目。

數(shù)據(jù)庫(kù)設(shè)計(jì)人員根據(jù)這個(gè)要求，在T_ORDER表的CLIENT、 ORDER_DATE及IS_SHPPED三字段上建立了一個(gè)復(fù)合索引IDX_ORDER_COMPOSITE；在T_ORDER_ITEM為外鍵 ORDER_ID建立IDX_ORDER_ITEM_ORDER_ID索引。

讓我們看一下該份設(shè)計(jì)的最終SQL腳本：

/*訂單表*/

create table T_ORDER (

ORDER_ID NUMBER(10) not null,

ADDRESS VARCHAR2(100),

CLIENT VARCHAR2(60),

ORDER_DATE CHAR(8),

IS_SHIPPED CHAR(1),

constraint PK_T_ORDER primary key (ORDER_ID)

);

create index IDX_CLIENT on T_ORDER (

　CLIENT ASC,

　ORDER_DATE ASC,

　IS_SHIPPED ASC);

/*訂單條目子表*/

create table T_ORDER_ITEM (

　ITEM_ID NUMBER(10) not null,

　ORDER_ID NUMBER(10),

　ITEM VARCHAR2(20),

　COUNT NUMBER(10),

　constraint PK_T_ORDER_ITEM primary key (ITEM_ID)

);

create index IDX_ORDER_ITEM_ORDER_ID on T_ORDER_ITEM (

　ORDER_ID ASC);

　alter table T_ORDER_ITEM add constraint FK_T_ORDER__REFERENCE_T_ORDER foreign key (ORDER_ID) references T_ORDER (ORDER_ID);

我們承認(rèn)在ER關(guān)系上，這份設(shè)計(jì)并不存在的缺陷，但卻存在以下有待優(yōu)化的地方：

·沒(méi)有將表數(shù)據(jù)和索引數(shù)據(jù)存儲(chǔ)到不同的表空間中，而不加區(qū)別地將它們存儲(chǔ)到同一表空間里。這樣，不但會(huì)造成I/O競(jìng)爭(zhēng)，也為數(shù)據(jù)庫(kù)的維護(hù)工作帶來(lái)不便。

·ORACLE會(huì)自動(dòng)為表的主鍵列創(chuàng)建一個(gè)普通B-Tree索引，但由于這兩張表的主鍵值都通過(guò)序列提供，具有嚴(yán)格的順序性（升序或降序），此時(shí)手工為其指定一個(gè)反鍵索引（reverse key index）將更加合理。

·在子表T_ORDER_ITEM外鍵列ORDER_ID上建立的IDX_ORDER_ITEM_ORDER_ID的普通B-Tree索引非常適合設(shè)置為壓縮型索引，即建立一個(gè)壓縮型的B-Tree索引。因?yàn)橐环萦唵螘?huì)對(duì)應(yīng)多個(gè)訂單條目，這就意味著T_ORDER_ITEM表存在許多同值的 ORDER_ID列值，通過(guò)將其索引指定為壓縮型的B-Tree索引，不但可以減少IDX_ORDER_ITEM_ORDER_ID所需的存儲(chǔ)空間，還將提高表操作的性能。

·企圖僅通過(guò)建立一個(gè)包含3字段IDX_ORDER_COMPOSITE復(fù)合索引滿足如前所述的兩種查詢條件方式的索引是有問(wèn)題的，事實(shí)上使用ORDER_DATE+IS_SHIPPED復(fù)合條件的查詢將利用不到IDX_ORDER_COMPOSITE索引。 優(yōu)化設(shè)計(jì)

1、將表數(shù)據(jù)和索引數(shù)據(jù)分開(kāi)表空間存儲(chǔ)

1.1 表數(shù)據(jù)和索引為何需要使用獨(dú)立的表空間

Oracle強(qiáng)烈建立，任何一個(gè)應(yīng)用程序的庫(kù)表至少需要?jiǎng)?chuàng)建兩個(gè)表空間，其中之一用于存儲(chǔ)表數(shù)據(jù)，而另一個(gè)用于存儲(chǔ)表索引數(shù)據(jù)。因?yàn)榧偃鐚⒈頂?shù)據(jù)和索引數(shù)據(jù)放在一起，表數(shù)據(jù)的I/O操作和索引的I/O操作將產(chǎn)生影響系統(tǒng)性能的I/O競(jìng)爭(zhēng)，降低系統(tǒng)的響應(yīng)效率。將表數(shù)據(jù)和索引數(shù)據(jù)存放在不同的表空間中（如一個(gè)為APP_DATA，另一個(gè)為APP_IDX），并在物理層面將這兩個(gè)表空間的數(shù)據(jù)文件放在不同的物理磁盤上，就可以避免這種競(jìng)爭(zhēng)了。

擁有獨(dú)立的表空間，就意味著可以獨(dú)立地為表數(shù)據(jù)和索引數(shù)據(jù)提供獨(dú)立的物理存儲(chǔ)參數(shù)，而不會(huì)發(fā)生相互影響，究竟表數(shù)據(jù)和索引數(shù)據(jù)擁有不同的特性，而這些特性又直接影響了物理存儲(chǔ)參數(shù)的設(shè)定。

此外，表數(shù)據(jù)和索引數(shù)據(jù)獨(dú)立存儲(chǔ)，還會(huì)帶來(lái)數(shù)據(jù)治理和維護(hù)上的方面。如你在遷移一個(gè)業(yè)務(wù)數(shù)據(jù)庫(kù)時(shí)，為了降低數(shù)據(jù)大小，可以只遷出表數(shù)據(jù)的表空間，在目標(biāo)數(shù)據(jù)庫(kù)中通過(guò)重建索引的方式就可以生成索引數(shù)據(jù)了。

1.2 表數(shù)據(jù)和索引使用不同表空間的SQL語(yǔ)法

指定表數(shù)據(jù)及索引數(shù)據(jù)存儲(chǔ)表空間語(yǔ)句最簡(jiǎn)單的形式如下。

將表數(shù)據(jù)存儲(chǔ)在APP_DATA表空間里：

create table T_ORDER ( ORDER_ID NUMBER(10) not null, …)tablespace APP_DATA;

將索引數(shù)據(jù)存儲(chǔ)在APP_IDX表空間里：

create index IDX_ORDER_ITEM_ORDER_ID on T_ORDER_ITEM ( ORDER_ID ASC)tablespace APP_IDX;

1.3 PowerDesigner中如何操作

1) 首先，必須創(chuàng)建兩個(gè)表空間。通過(guò)Model->Tablespace...在List of Tablespaces中創(chuàng)建兩個(gè)表空間：

優(yōu)化Oracle庫(kù)表設(shè)計(jì)的若干方法(1)（圖二）

圖 3 指定表數(shù)據(jù)的存儲(chǔ)表空間

3) 為每個(gè)索引指定索引數(shù)據(jù)的存儲(chǔ)表空間。在Table Properties中切換到Indexes頁(yè)，在這里列出了表的所有索引，雙擊需設(shè)置表空間的索引，在彈出的Index Properties窗口中切換到Options頁(yè)，按如下方式指定索引的存儲(chǔ)表空間。前言

絕大多數(shù)的Oracle數(shù)據(jù)庫(kù)性能問(wèn)題都是由于數(shù)據(jù)庫(kù)設(shè)計(jì)不合理造成的，只有少部分問(wèn)題根植于Database Buffer、Share Pool、Redo Log Buffer等內(nèi)存模塊配置不合理，I/O爭(zhēng)用，CPU爭(zhēng)用等DBA職責(zé)范圍上。所以除非是面對(duì)一個(gè)業(yè)已完成不可變更的系統(tǒng)，否則我們不應(yīng)過(guò)多地將關(guān)注點(diǎn)投向內(nèi)存、I/O、CPU等性能調(diào)整項(xiàng)目上，而應(yīng)關(guān)注數(shù)據(jù)庫(kù)表本身的設(shè)計(jì)是否合理，庫(kù)表設(shè)計(jì)的合理性才是程序性能的真正執(zhí)牛耳者。

合理的數(shù)據(jù)庫(kù)設(shè)計(jì)需要考慮以下的方面：

·業(yè)務(wù)數(shù)據(jù)以何種方式表達(dá)。如一個(gè)員工有多個(gè)Email，你可以在T_EMPLOYEE表中建立多個(gè)Email字段如email_1、email_2、 email_3，也可以創(chuàng)建一個(gè)T_EMAIL子表來(lái)存儲(chǔ)，甚至可以用逗號(hào)分隔開(kāi)多個(gè)Email地址存放在一個(gè)字段中。

·數(shù)據(jù)以何種方式物理存儲(chǔ)。如大表的分區(qū)，表空間的合理設(shè)計(jì)等。

·如何建立合理的數(shù)據(jù)表索引。表索引幾乎是提高數(shù)據(jù)表查詢性能最有效的方法，Oracle擁有類型豐富的數(shù)據(jù)表索引類型，如何取舍選擇顯得非凡重要。

本文我們將目光主要聚焦于數(shù)據(jù)表的索引上，同時(shí)也將提及其他兩點(diǎn)的內(nèi)容。通過(guò)對(duì)一個(gè)簡(jiǎn)單的庫(kù)表設(shè)計(jì)實(shí)例的分析引出設(shè)計(jì)中的不足，并逐一改正。考慮到手工編寫庫(kù)表的SQL腳本原始且低效，我們將用目前最流行的庫(kù)表設(shè)計(jì)工具PowerDesigner 10來(lái)講述表設(shè)計(jì)的過(guò)程，所以在本文中你還會(huì)了解到一些相關(guān)的PowerDesigner的使用技巧。

一個(gè)簡(jiǎn)單的例子

某個(gè)開(kāi)發(fā)人員著手設(shè)計(jì)一個(gè)訂單的系統(tǒng)，這個(gè)系統(tǒng)中有兩個(gè)主要的業(yè)務(wù)表，分別是訂單基本信息表和訂單條目表，這兩張表具有主從關(guān)系的表，其中T_ORDER是訂單主表，而T_ORDER_ITEM是訂單條目表。數(shù)據(jù)庫(kù)設(shè)計(jì)人員的設(shè)計(jì)成果如圖 1所示：

優(yōu)化Oracle庫(kù)表設(shè)計(jì)的若干方法(1)（圖一）

圖 1 訂單主從表

ORDER_ID是訂單號(hào)，為T_ORDER的主鍵，通過(guò)名為SEQ_ORDER_ID的序列產(chǎn)生鍵值，而ITEM_ID是T_ORDER_ITEM表的主鍵，通過(guò)名為SEQ_ORDER_ITEM的序列產(chǎn)生鍵值，T_ORDER_ITEM通過(guò)ORDER_ID外鍵關(guān)聯(lián)到T_ORDER表。

需求文檔指出訂單記錄將通過(guò)以下兩種方式來(lái)查詢數(shù)據(jù)：

·CLIENT + ORDER_DATE+IS_SHPPED：根據(jù)"客戶＋訂貨日期+是否發(fā)貨"條件查詢訂單及訂單條目。

·ORDER_DATE+IS_SHIPPED：根據(jù)"訂貨日期+是否發(fā)貨"條件查詢訂單及訂單條目。

數(shù)據(jù)庫(kù)設(shè)計(jì)人員根據(jù)這個(gè)要求，在T_ORDER表的CLIENT、 ORDER_DATE及IS_SHPPED三字段上建立了一個(gè)復(fù)合索引IDX_ORDER_COMPOSITE；在T_ORDER_ITEM為外鍵 ORDER_ID建立IDX_ORDER_ITEM_ORDER_ID索引。

讓我們看一下該份設(shè)計(jì)的最終SQL腳本：

/*訂單表*/

create table T_ORDER (

ORDER_ID NUMBER(10) not null,

ADDRESS VARCHAR2(100),

CLIENT VARCHAR2(60),

ORDER_DATE CHAR(8),

IS_SHIPPED CHAR(1),

constraint PK_T_ORDER primary key (ORDER_ID)

);

create index IDX_CLIENT on T_ORDER (

　CLIENT ASC,

　ORDER_DATE ASC,

　IS_SHIPPED ASC);

/*訂單條目子表*/

create table T_ORDER_ITEM (

　ITEM_ID NUMBER(10) not null,

　ORDER_ID NUMBER(10),

　ITEM VARCHAR2(20),

　COUNT NUMBER(10),

　constraint PK_T_ORDER_ITEM primary key (ITEM_ID)

);

create index IDX_ORDER_ITEM_ORDER_ID on T_ORDER_ITEM (

　ORDER_ID ASC);

　alter table T_ORDER_ITEM add constraint FK_T_ORDER__REFERENCE_T_ORDER foreign key (ORDER_ID) references T_ORDER (ORDER_ID);

我們承認(rèn)在ER關(guān)系上，這份設(shè)計(jì)并不存在的缺陷，但卻存在以下有待優(yōu)化的地方：

·沒(méi)有將表數(shù)據(jù)和索引數(shù)據(jù)存儲(chǔ)到不同的表空間中，而不加區(qū)別地將它們存儲(chǔ)到同一表空間里。這樣，不但會(huì)造成I/O競(jìng)爭(zhēng)，也為數(shù)據(jù)庫(kù)的維護(hù)工作帶來(lái)不便。

·ORACLE會(huì)自動(dòng)為表的主鍵列創(chuàng)建一個(gè)普通B-Tree索引，但由于這兩張表的主鍵值都通過(guò)序列提供，具有嚴(yán)格的順序性（升序或降序），此時(shí)手工為其指定一個(gè)反鍵索引（reverse key index）將更加合理。

·在子表T_ORDER_ITEM外鍵列ORDER_ID上建立的IDX_ORDER_ITEM_ORDER_ID的普通B-Tree索引非常適合設(shè)置為壓縮型索引，即建立一個(gè)壓縮型的B-Tree索引。因?yàn)橐环萦唵螘?huì)對(duì)應(yīng)多個(gè)訂單條目，這就意味著T_ORDER_ITEM表存在許多同值的 ORDER_ID列值，通過(guò)將其索引指定為壓縮型的B-Tree索引，不但可以減少IDX_ORDER_ITEM_ORDER_ID所需的存儲(chǔ)空間，還將提高表操作的性能。

·企圖僅通過(guò)建立一個(gè)包含3字段IDX_ORDER_COMPOSITE復(fù)合索引滿足如前所述的兩種查詢條件方式的索引是有問(wèn)題的，事實(shí)上使用ORDER_DATE+IS_SHIPPED復(fù)合條件的查詢將利用不到IDX_ORDER_COMPOSITE索引。 優(yōu)化設(shè)計(jì)

1、將表數(shù)據(jù)和索引數(shù)據(jù)分開(kāi)表空間存儲(chǔ)

1.1 表數(shù)據(jù)和索引為何需要使用獨(dú)立的表空間

Oracle強(qiáng)烈建立，任何一個(gè)應(yīng)用程序的庫(kù)表至少需要?jiǎng)?chuàng)建兩個(gè)表空間，其中之一用于存儲(chǔ)表數(shù)據(jù)，而另一個(gè)用于存儲(chǔ)表索引數(shù)據(jù)。因?yàn)榧偃鐚⒈頂?shù)據(jù)和索引數(shù)據(jù)放在一起，表數(shù)據(jù)的I/O操作和索引的I/O操作將產(chǎn)生影響系統(tǒng)性能的I/O競(jìng)爭(zhēng)，降低系統(tǒng)的響應(yīng)效率。將表數(shù)據(jù)和索引數(shù)據(jù)存放在不同的表空間中（如一個(gè)為APP_DATA，另一個(gè)為APP_IDX），并在物理層面將這兩個(gè)表空間的數(shù)據(jù)文件放在不同的物理磁盤上，就可以避免這種競(jìng)爭(zhēng)了。

擁有獨(dú)立的表空間，就意味著可以獨(dú)立地為表數(shù)據(jù)和索引數(shù)據(jù)提供獨(dú)立的物理存儲(chǔ)參數(shù)，而不會(huì)發(fā)生相互影響，究竟表數(shù)據(jù)和索引數(shù)據(jù)擁有不同的特性，而這些特性又直接影響了物理存儲(chǔ)參數(shù)的設(shè)定。

此外，表數(shù)據(jù)和索引數(shù)據(jù)獨(dú)立存儲(chǔ)，還會(huì)帶來(lái)數(shù)據(jù)治理和維護(hù)上的方面。如你在遷移一個(gè)業(yè)務(wù)數(shù)據(jù)庫(kù)時(shí)，為了降低數(shù)據(jù)大小，可以只遷出表數(shù)據(jù)的表空間，在目標(biāo)數(shù)據(jù)庫(kù)中通過(guò)重建索引的方式就可以生成索引數(shù)據(jù)了。

1.2 表數(shù)據(jù)和索引使用不同表空間的SQL語(yǔ)法

指定表數(shù)據(jù)及索引數(shù)據(jù)存儲(chǔ)表空間語(yǔ)句最簡(jiǎn)單的形式如下。

將表數(shù)據(jù)存儲(chǔ)在APP_DATA表空間里：

create table T_ORDER ( ORDER_ID NUMBER(10) not null, …)tablespace APP_DATA;

將索引數(shù)據(jù)存儲(chǔ)在APP_IDX表空間里：

create index IDX_ORDER_ITEM_ORDER_ID on T_ORDER_ITEM ( ORDER_ID ASC)tablespace APP_IDX;

1.3 PowerDesigner中如何操作

1) 首先，必須創(chuàng)建兩個(gè)表空間。通過(guò)Model->Tablespace...在List of Tablespaces中創(chuàng)建兩個(gè)表空間：

優(yōu)化Oracle庫(kù)表設(shè)計(jì)的若干方法(1)（圖二）

上一篇：Oracle9i初始化參數(shù)中文說(shuō)明(15)

下一篇：保持Oracle數(shù)據(jù)庫(kù)的優(yōu)良性能