Oracle9i 資料庫設計指引全集 (2)

2.2 命名

2.2.1 語言

命名應該使用英文單詞，避免使用拼音，特别不應該使用拼音簡寫。命名不允許使用中文或者特殊字元。

英文單詞使用用對象本身意義相對或相近的單詞。選擇最簡單或最通用的單詞。不能使用毫不相幹的單詞來命名

當一個單詞不能表達對象含義時，用詞組組合，如果組合太長時，采用用簡或縮寫，縮寫要基本能表達原單詞的意義。

當出現對象名重名時，是不同類型對象時，加類型字首或字尾以示差別。

2.2.2 大小寫

名稱一律大寫，以友善不同資料庫移植，以及避免程式調用問題。

2.2.3 單詞分隔

命名的各單詞之間可以使用下劃線進行分隔。

2.2.4 保留字

命名不允許使用 SQL 保留字。

2.2.5 命名長度

表名、字段名、視圖名長度應限制在 20 個字元内 ( 含字首 ) 。

2.2.6 字段名稱

同一個字段名在一個資料庫中隻能代表一個意思。比如 telephone 在一個表中代表 “ 電話号碼 ” 的意思，在另外一個表中就不能代表 “ 手機号碼 ” 的意思。

不同的表用于相同内容的字段應該采用同樣的名稱，字段類型定義。

2.3 資料類型

2.3.1 字元型

固定長度的字串類型采用 char ，長度不固定的字串類型采用 varchar 。避免在長度不固定的情況下采用 char 類型。如果在資料遷移等出現以上情況，則必須使用 trim() 函數截去字串後的空格。

2.3.2 數字型

數字型字段盡量采用 number 類型。

2.3.3 日期和時間

2.3.3.1 系統時間

由資料庫産生的系統時間首選資料庫的日期型，如 DATE 類型。

2.3.3.2 外部時間

由資料導入或外部應用程式産生的日期時間類型采用 varchar 類型，資料格式采用： YYYYMMDDHH24MISS 。

2.3.3.3 大字段

如無特别需要，避免使用大字段 (blob ， clob ， long ， text ， image 等 ) 。

2.3.3.4 唯一鍵

對于數字型唯一鍵值，盡可能用系列 sequence 産生。

2.4 設計

2.4.1 範式

如無性能上的必須原因，應該使用關系資料庫理論，達到較高的範式，避免資料備援 , 但是如果在資料量上與性能上無特别要求，考慮到實作的友善性可以有适當的資料備援，但基本上要達到 3NF. 如非确實必要，避免一個字段中存儲多個标志的做法。如 11101 表示 5 個标志的一種取值。這往往是增加複雜度，降低性能的地方。

2.4.2 表設計

2.4.2.1 邏輯段設計原則

2.4.2.1.1 Tablespace

每個表在建立時候，必須指定所在的表空間，不要采用預設表空間以防止表建立在系統表空間上導緻性能問題。對于事務比較繁忙的資料表，必須存放在該表的專用表空間中。

2.4.2.1.2 Pctused

預設 pctused 導緻資料庫實體空間使用率非常低 40% 左右；對于 update 比較少或 update 不導緻行增大的表， pctused 可設定在 60—85 之間；對于 update 能夠導緻行增大的表， update 設定在 40—70 之間

2.4.2.1.3 Initrans

對于需要并行查詢或者在 RAC 資料庫中需要并行處理的表， initrans 設定為 2 的倍數，否則，不設該值。

2.4.2.1.4 Storage

2.4.2.1.4.1 Initial

盡量減少表資料段的 extents 數量， initial 的大小盡量接近資料段的大小 64K ， 128K ， … ， 1M ， 2M ， 4M ， 8M ， 16M ， … ，等按 2 的倍數進行圓整。例如表或分區資料段大小為 28M ，則 initial 取 32M 。

2.4.2.1.4.2 Next

表或分區擴充 extents 的大小，按上述方法進行圓整。當表或分區資料段無法按 Initial 接近值進行圓整的情況下，其大小可以按 Initial+Next 進行圓整。此時，必須設定 Minextents=2 。例如：表或分區資料段大小為 150M ，則 Initial=128M ； Next=32M ， Minextents=2 。

2.4.2.1.4.3 Minextents

該參數表示表建立時候 Extents 的初始數量，一般取 1—2 。

2.4.2.1.4.4 Pctincrease

表示每個擴充 Extents 的增長率，設定 pctincrease=0 能夠獲得較好的存儲性能。

2.4.2.2 特殊表設計原則

2.4.2.2.1 分區表

對于資料量比較大的表，根據表資料的屬性進行分區，以得到較好的性能。如果表按某些字段進行增長，則采用按字段值範圍進行範圍分區；如果表按某個字段的幾個關鍵值進行分布，則采用清單分區；對于靜态表，則采用 hash 分區或清單分區；在範圍分區中，如果資料按某關鍵字段均衡分布，則采用子分區的複合分區方法。

2.4.2.2.2 聚蔟表

如果某幾個靜态表關系比較密切，則可以采用聚蔟表的方法。

2.4.2.3 完整性設計原則

2.4.2.3.1 主鍵限制

關聯表的父表要求有主健，主健字段或組合字段必須滿足非空屬性和唯一性要求。對于資料量比較大的父表，要求指定索引段。

2.4.2.3.2 外鍵關聯

對于關聯兩個表的字段，一般應該分别建立主鍵、外鍵。實際是否建立外鍵，根據對資料完整性的要求決定。為了提高性能，對于資料量比較大的标要求對外健建立索引。對于有要求級聯删除屬性的外鍵，必須指定 on delete cascade 。

2.4.2.3.3 NULL 值

對于字段能否 null ，應該在 sql 建表腳本中明确指明，不應使用預設。由于 NULL 值在參加任何運算中，結果均為 NULL 。是以在應用程式中必須利用 nvl() 函數把可能為 NULL 值得字段或變量轉換為非 NULL 的預設值。例如： NVL （ sale,0 ）。

2.4.2.3.4 Check 條件

對于字段有檢查性限制，要求指定 check 規則。

2.4.2.3.5 觸發器

觸發器是一種特殊的存儲過程，通過資料表的 DML 操作而觸發執行，起作用是為確定資料的完整性和一緻性不被破壞而建立，實作資料的完整限制。

觸發器的 before 或 after 事務屬性的選擇時候，對表操作的事務屬性必須與應用程式事務屬性保持一緻，以避免死鎖發生。在大型導入表中，盡量避免使用觸發器。

2.4.2.4 注釋

表、字段等應該有中文名稱注釋，以及需要說明的内容。

2.4.3 索引設計

對于查詢中需要作為查詢條件的字段，可以考慮建立索引。最終根據性能的需要決定是否建立索引。對于複合索引，索引字段順序比較關鍵，把查詢頻率比較高的字段排在索引組合的最前面。在分區表中，盡量采用 local 分區索引以友善分區維護。

除非時分區 local 索引，否則在建立索引段時候必須指定指定索引段的 tablespace 、 storage 屬性，具體參考 4.4.2.1 内容。

2.4.4 視圖設計

視圖是虛拟的資料庫表，在使用時要遵循以下原則：

從一個或多個庫表中查詢部分資料項；

為簡化查詢，将複雜的檢索或字查詢通過視圖實作；

提高資料的安全性，隻将需要檢視的資料資訊顯示給權限有限的人員；

視圖中如果嵌套使用視圖，級數不得超過 3 級；

由于視圖中隻能固定條件或沒有條件，是以對于資料量較大或随時間的推移逐漸增多的庫表，不宜使用視圖；可以采用實體化視圖代替。

除特殊需要，避免類似 Select * from [TableName] 而沒有檢索條件的視圖；

視圖中盡量避免出現資料排序的 SQL 語句。

2.4.5 包設計

存儲過程、函數、外部遊标必須在指定的資料包對象 PACKAGE 中實作。存儲過程、函數的建立如同其它語言形式的程式設計過程，适合采用子產品化設計方法；當具體算法改變時，隻需要修改需要存儲過程即可，不需要修改其它語言的源程式。當和資料庫頻繁交換資料是通過存儲過程可以提高運作速度，由于隻有被授權的使用者才能執行存儲過程，是以存儲過程有利于提高系統的安全性。

存儲過程、函數必須檢索資料庫表記錄或資料庫其他對象，甚至修改（執行 Insert 、 Delete 、 Update 、 Drop 、 Create 等操作）資料庫資訊。如果某項功能不需要和資料庫打交道，則不得通過資料庫存儲過程或函數的方式實作。在函數中避免采用 DML 或 DDL 語句。

在資料包采用存儲過程、函數重載的方法，簡化資料包設計，提高代碼效率。存儲過程、函數必須有相應的出錯處理功能。

2.4.6 安全性設計

4.4.6.1 管理預設使用者

在生産環境中，必須嚴格管理 sys 和 system 使用者，必須修改其預設密碼，禁止用該使用者建立資料庫應用對象。删除或鎖定資料庫測試使用者 scott 。

2.4.6.2 資料庫級使用者權限設計

必須按照應用需求，設計不同的使用者通路權限。包括應用系統管理使用者，普通使用者等，按照業務需求建立不同的應用角色。

使用者通路另外的使用者對象時，應該通過建立同義詞對象 synonym 進行通路。

2.4.6.3 角色與權限

确定每個角色對資料庫表的操作權限，如建立、檢索、更新、删除等。每個角色擁有剛好能夠完成任務的權限，不多也不少。在應用時再為使用者配置設定角色，則每個使用者的權限等于他所兼角色的權限之和。

2.4.6.4 應用級使用者設計

應用級的使用者帳号密碼不能與資料庫相同，防止使用者直接操作資料庫。使用者隻能用帳号登陸到應用軟體，通過應用軟體通路資料庫，而沒有其它途徑操作資料庫。

2.4.6.5 使用者密碼管理

使用者帳号的密碼必須進行加密處理，確定在任何地方的查詢都不會出現密碼的明文。

2.5 SQL 編寫

2.5.1 字元類型資料

SQL 中的字元類型資料應該統一使用單引号。特别對純數字的字串，必須用單引号，否則會導緻内部轉換而引起性能問題或索引失效問題。利用 trim(),lower() 等函數格式化比對條件。

2.5.2 複雜 sql

對于非常複雜的 sql( 特别是有多層嵌套，帶子句或相關查詢的 ) ，應該先考慮是否設計不當引起的。對于一些複雜 SQL 可以考慮使用程式實作。

USER_TAB_COMMENTS 資料字典

Comment on 可加注解

2.5.3 高效性

2.5.3.1 避免 In 子句

使用 In 或 not In 子句時，特别是當子句中有多個值時，且查詢資料表資料較多時，速度會明顯下降。可以采用連接配接查詢或外連接配接查詢來提高性能。

Char 比 varchar 查詢時高詢

在進行查詢及建立索引時， char 比 varchar 的效率要高，當然 varchar 在存儲上比 char 要好

2.5.3.2 避免嵌套的 Select 子句

這個實際上是 In 子句的特例。

2.5.3.3 避免使用 Select * 語句

如果不是必要取出所有資料，不要用 * 來代替，應給出字段清單，注：不含 select count(*) 。

2.5.3.4 避免不必要的排序

不必要的資料排序大大的降低系統性能。

2.5.4 健壯性

2.5.4.1 Insert 語句

使用 Insert 語句一定要給出要插入值的字段清單，這樣即使更改了表結構加了字段也不會影響現有系統的運作。

2.5.4.2 Count(*) 、 Count(*) 、 count(distinct id) 的差別

Select count(*) from testtab

得到表 testtab 的記錄數

select count(id) from testtab

得到表 testtab id 字段非空記錄數

select count(distinct id) from testtab

得到表 testtab id 字段值非相同記錄數

2.5.4.3 Not null 為字段類型性質的限制

本限制功能在後期無文法使期失效，可使用修改字段類型方式

alter table modify 字段名 類型 not null

alter table modify 字段名 類型

2.5.4.4 外鍵值可用 null 的問題

外鍵列如沒有明确說明 not null ，可插入 null 記錄（而 null 是在外部表的記錄中沒有的），如無可插 null 記錄的想法，要對外鍵字段加 not null 限制。

2.5.4.5 序列 sequence 跳号的問題

sequence 因復原，系統崩潰（使用 cache 内的值将認為已用），多表引用都将使其跳号，是以不能用于為連續序号 utl_row.cast_to_row

2.5.4.6 unicn/ intersect/ minus 使用 ordey by 的注意事項

以上語句進行連表操作，而表同表的字段順序的類型相同但字段标題名可不同，使用 ordey by 時後面如果是字段名，要求所有的表的字段标題名相同，否則用字段的順序号

　　select id,name,year from user1

　　union

　　select no,name,to_number(null) year from user2

　　order by 1,name,year

2.5.5 安全性

2.5.5.1 Where 條件

無論在使用 Select, 還是使用破壞力極大的 Update 和 Delete 語句時，一定要檢查 Where 條件判斷的完整性，不要在運作時出現資料的重大丢失。如果不确定，最好先用 Select 語句帶上相同條件來果一下結果集，來檢驗條件是否正确。

2.5.6 完整性

有依賴關系的表，例如主外鍵關系表，在删除父表時必須級聯删除其子表相應資料，或則按照某種業務規則轉移該資料。 9I 中表中字段縮小及變類型，字段為空或表空， varchar 和 char 長度不變可任意改，字段名和表名可字段可用 ALTER TABLE table SET UNUSED (column) 設定為不可用，注意無指令再設為可用

3 備份恢複設計原則

3.1 資料庫 exp/imp 備份恢複

Oracle 資料庫的 Exp 、 Imp 提供了資料快速的備份和恢複手段，提供了資料庫級、使用者級和表級的資料備份恢複方式。這種方法一般作為資料庫輔助備份手段。

3.1.1 資料庫級備份原則

在資料庫的資料量比較小，或資料庫初始建立的情況下采用。不适合 7*24 的線上生産環境資料庫備份。

3.1.2 使用者級備份原則

在使用者對象表資料容量比較小、或則使用者對象初始建立的情況下使用。

3.1.3 表級備份原則

主要在以下場合采用的備份方式：

參數表備份

靜态表備份

分區表的分區備份。

3.2 資料庫冷備份原則

資料庫冷備份必須符合以下原則：

資料庫容量比較小。

資料庫允許關閉的情況。

3.3 Rman 備份恢複原則

這種方式适用于 7*24 環境下的聯機熱備份情形。

3.3.1 Catalog 資料庫

單獨建立備份恢複用的資料庫執行個體，盡可能與生産環境的資料庫分開，確定 catalog 與生産資料庫的網絡連接配接良好。在 9I 系統使用良好的備份政策以可，支援完全使用控制檔案儲存 catalog 資訊，備份政策如下：

　　backup spfile format '/data/backup/%d_SPFILE_%T_%s_%p.bak';

　　sql "alter system archive log current";

　　backup archivelog all format '/data/backup/%d_ARC_%T_%s_%p.bak' delete all input;

　　backup current controlfile format '/data/backup/%d_CTL_%T_%s_%p.bak';

　　在spfile、控制檔案、資料庫全丢的情況下可通過下面的方式恢複

　　RMAN> connect target

　　connected to target database (not started)

　　RMAN> startup

　　RMAN> restore spfile from '/data/backup/COMMDB_SPFILE_20030411_9_1.bak';

　　SQL> startup

　　ORA-00205: error in identifying controlfile, check alert log for more info

　　RMAN> restore controlfile from 'd:/DB92_CTL_20031113_9_1.BAK';

　　Mout database:

　　RMAN> recover database;

　　RMAN> alter database open resetlogs;

注意：對資料庫設定控制檔案儲存備份資訊為 365 天，具體語句如下。

　　alter system set control_file_record_keep_time=365 SCOPE=BOTH;

3.3.2 Archive Log

設定 Archive Log 的位置，确儲存儲媒體有足夠的空間來保留指定時間内 archive log 的總量。建設定期對 RMAN 進行全備份，删除備援歸檔日志檔案。

3.3.3 全備份政策

對于小容量資料庫，可以采用全備份政策。對于大容量資料庫，必須制定全備份政策方案，備份時對 archive log 進行轉儲，同時冷備份 catalog 資料庫。

3.3.4 增量備份政策

對于大容量資料庫，必須制定增量備份、累積備份和全備份的周期，備份時對 archive log 進行轉儲，同時冷備份 catalog 資料庫。

3.3.5 恢複原則

采用 Rman 腳本進行資料庫恢複。資料庫恢複有以下幾種：

3.3.5.1 局部恢複

主要用于恢複表空間、資料檔案，一般不影響資料庫其他操作。

3.3.5.2 完全恢複

資料庫恢複到故障點，由 catalog 目前資料庫決定。

3.3.5.3 不完全恢複

恢複到資料庫的某一時間點或備份點。

恢複 catalog 資料庫。

恢複資料庫 control file 。

恢複到資料庫某一時間點。

重設日志序列。

3.4 備用資料庫原則

資料庫系統在以下情況下可以考慮采用備用資料庫 data guard 原則：

資料庫容量适中。

資料庫嚴格要求 7*24 不間斷，或間斷時間要求控制在最小範圍内。

資料庫要求有異地備份備援。

3.5 一些小經驗

使用 oemc 的 oms 時，首選項要求是節點和資料庫分别加入系統使用者（如： administrator ）和資料庫 DBA 使用者 (system) 。節點的系統使用者必須有批處理作業登入的權限

agent 不能啟動， lisnter 修改後都要手動删除 oracle/ora9/network/agent 中的 *.q 檔案

oracle/admin/my9i/bdump 中是使用者的出錯日志

改變表的空間的方式 alter table hr.ssss move TABLESPACE example （要重建索引） ; 或用 imp 導入時，設定導入使用者隻有某一表空間的使用權，無 RESOURCE 角色和 UNLIMITED TABLESPACE 權限

aleter system set log_checkpoint_to_alter=true ，後可報警檔案發現 checkpoint 的起動和結束時間。