1、ORACLE設計規范1、數據庫模型設計方法規范1.1、 數據建模原則性規范原則對于涉及數據庫的項目，需要構建數據庫邏輯模型圖，邏輯模型圖是項目組成員之間在數據庫層面溝通交互的依據，必 須規范畫圖(表，主鍵，外鍵，關系)衡量對于表的個數在20個以上的模型，需要數據組參與設計，并需DBA作最終審核方法 對于OLTP系統，采用范式化思想進行模型設計， 對于OLAP 系統，采用面向問題及多級顆粒度的思想進行模型設計實施采用主流的模型設計軟件工具PowerDesigner, ERStudio,ERWin1.2、 實體型之間關系認定規范原則所有實體型間的業務邏輯關系，除了語義上保留其原有的業務關系外，本質

2、上都要轉化成關系數據庫的三種關系(1:1)(1:N) (N:M)衡量對于3個及以上實體型之間的 多元關系”，需要數據組參與設計方法比如實體型A和實體型B之間的關系，可以通過問兩個問題來確定他們之間的關系：一個 A可以對應幾個B? 一個B可 以對應幾個A?(1) 一個A對應一個B,相反一個B對應一個A,那么A對B就是1:1關系；(2) 一個A對應多個B,相反一個B對應一個A,那么A對B就是1:N關系；(3) 一個A對應多個B,相反一個B對應對個A,那么A 對B就是N:M關系；實施 (1) 1:1關系選取任何一個表的主鍵到另一個表中作為外鍵來體現；(2) 1: N關系將1表的主鍵在N表中以外鍵形式

3、存在來體 現；(2) N:M關系采用 關系表”來體現，該關系表的主鍵是由相 關實體表的主鍵組成的符合主鍵，各實體表主鍵不但組成了 該關系表的主鍵，同時也被看作外鍵在該關系表中存在；(4)對于三個以上表之間的 多元關系”常需要和反范式化冗 余字段結合起來設計，以保證查詢速度；1.3、 范式化1NF的規范原則 OLTP系統的模型，需要符合第三范式衡量對于表在20個以上的模型，需要數據組參與設計方法 范式化要求：INF:列是訪問的最小單位，具有原子性，不可再被分割；實施 依據具體情況對相應屬性進行拆分或者合并。范式化1NF常見現象：現象一：同一個屬性值的不同細度把握，比如，常見的“姓 名”這個屬性，

4、設計一：“姓名”是一個列，設計二：“姓” 是一個列，“名”是一個列，兩個列的值組合起來才表達一 個“姓名”語義。兩種設計方法，在不同的系統中都有應用， 這主要是依據需求的細度來確定，靈活把握；現象二：把多個屬性值錯誤的作為一個屬性值存儲，比如： 常見的OA系統要存儲員工的各種屬性，包括技能信息，技 能范圍：Oracle, JAVA, .NET,C#,Perl,UNIX 等等，一種常見 的錯誤設計是：設計一張員工表，其中有一個技能屬性字段， 然后某員工所掌握的多種技能用逗號（，）間隔，然后將這 個字符串存儲到這個員工表的技能屬性字段中。這里的錯誤 在于將多個屬性值作為一個屬性值存儲在一個字段中，

5、不能 滿足直接遍歷員工對某個技能掌握情況，而且如果再要求說 明員工對個技能的掌握程度（精通，熟悉，一般等等），則 再增加字段，里面的對應關系將很容易錯亂，這是嚴重違反 1NF的情況。正確的設計應該是：兩個實體表：一張是員工 表，一張是技能字典表，一個員工可以掌握多個技能，也就是（1:N）關系，相反一個技能可以被多個員工掌握， 也是（1:N） 關系，雙向都是（1：N）關系，那么綜合起來員工和技能之間 就是“多對多關系（N:M） ”，依據前述規范，應該設計一 張“關系表”來存儲“多對多關系”，主鍵為復合主鍵（員 工主鍵+技能主鍵），該關系有一個屬性“技能掌握程度”。1.4、 范式化2NF的規范原則

6、 OLTP系統的模型，需要符合第三范式衡量對于表在20個以上的模型，需要數據組參與設計方法范式化要求：2NF:滿足1NF,不存在非主鍵屬性對主鍵屬性的部分依賴；實施 范式化2NF常見現象：實體表中一般不會出現違反 2NF的情況，因為都是“一個” 主鍵列，而關系表是兩個以上列的“復合”主鍵，故而關系 表容易出現違反2NF的情況。主要是該關系表非主鍵外的屬 性，本該屬于相關的某個實體表的，卻放到了該關系表中， 這使得該屬性不能通過該關系表的復合主鍵唯一確定，DML操作會發生錯誤；如果違反了 2NF,那么應該把這個屬性從 關系表中拆分，也許會單獨形成一個表，絕大部分情況下是 將該屬性歸并到某個相關的

7、實體表中；違反2NF的例子：學生考試情況中，有兩個實體表：學生表 和學科表，學生與學科之間的考試關系就是N:M的關系，就 要創建一張關系表存儲該多對多的考試關系，表的主鍵為學生編號和學科編號，屬性為考試分數；那么“任課老師”該 放在那里呢？如果放到考試關系表中，那么安排任課老師， 必須先進行考試，這顯然不符合實際，也就是任課老師不該 依賴于學生編號和學科編號，只是依賴于學科編號，也就是 說任課教師信息應該放在學科表中；1.5、 范式化3NF的規范原則 OLTP系統的模型，需要符合第三范式衡量對于表在20個以上的模型，需要數據組參與設計方法 范式化3NF要求：3NF:滿足2NF,不存在非主鍵屬性

8、對主鍵屬性的傳遞依賴；實施 范式化3NF常見現象：違反3NF的情況，絕大多數是在含有外鍵的表中；比如 A表 中的外鍵字段Bkey是B的主鍵，那么依賴于 Bkey的屬性應 當屬于B表的屬性，而不是A表，如果放入A表，則這些對 A表的主鍵Akey的依賴，首先是依賴于 A (BKey),而后通 過A(BKey)對A (AKey)的依賴，傳遞依賴于 A(Akey);三種 關系(1:1,1:N, N:M)都含有外鍵，都很可能發生違反 3NF 的情況。違反3NF的后果：會導致那些問題屬性插入異常， 或者被誤刪。違反3NF的例子: 教師和學科之間，存在著上課關系，假設一個教師上一門課而且一門課只有一個教師上

9、，那么該關系為1:1關系，將教師表的主鍵教師編號在學科表中以外鍵形式存在就表達了該 1:1關系，那么教師的 聯系電話”屬性該放哪里呢？如果看到教師編號”出現在了學科表中，就將聯系電話放入學科表中， 那么聯系電話首先是對表中的教師編號依賴，再依據教師編 號對學科的依賴，達到了學科編號的依賴，那么聯系電話對 學科編號的依賴就是傳遞依賴，違反了 3NF,應該將其從學 科表中拆出來放入教師表中，不然的話，會發生操作異常， 比如，假設一個教師已經存在但是還沒有為其分配科目，那 么他的電話就無法存入庫中。1.6、 反范式化冗余字段使用規范原則OLTP系統中在完成范式化工作之后，對某些表，可以適當反范式化增

10、加冗余字段以提高數據訪問性能；在OLAP中采用的是面向問題的設計思想，應該大量使用反范式化冗余信 息衡量 當SQL關連查詢涉及到4張表時可考慮采用冗余字段方法常用在兩個地方：（1）關系表中的冗余：在關系表中增加相關實體表的相關屬性，以達到關連查詢時減少表的關聯數量 的目的（2）層次關系中的冗余：在多層次的子父表關系中，將父表的屬性存儲在 子表”或者 孫子表”或者 重孫表”中； 反范式化冗余字段實例：(1)關系表中的冗余：比如在考試關系中，原本在學科表中 的學分信息，可以冗余添加到考試關系表中，這樣，每個學 生得了多少學分，就可以直接從考試表得到，而無需關聯學 科表來得到；(2)多層關系中的冗余

11、：假設為之范疇從大到小有國家表， 省份表，城市表，城區表，社區表，它們之間的層次關系是 通過上一級的主鍵在下一級中以外鍵形式存在來體現的，但 是，如果需要問：某個設計屬于哪個國家？這樣就要關連查 詢所有的5張表，性能會很差，這時可以將國家編號以外鍵 形式放入到社區表中做冗余，這樣直接關聯國家表和社區表 即可得到答案。一般的，每間隔一級增加一個冗余外鍵，比 如將國家編號放入城市表中，將城市編號放入社區表中實施 如何保證冗余字段數據的正確性(一致性)是反范式化的關 鍵，需要對冗余字段詳細添加注釋，說明冗余了什么，以及 該字段的維護方法，常用維護方法如下：(1)如果在程序開發前設計的冗余字段，可以在

12、正常的業務邏輯程序中一并處理(2)如果是程序完成之后增加的冗余字 段，可以使用觸發器維護(3)對于OLAP中大量存在冗余字 段，可能需要使用單獨的處理任務進行維護1.7、 數據庫對象命名基本規范第一：長度規范：凡是需要命名的對象其標識符均不能超過30個字符，也即：Oracle中的表名、字段名，函數名，過程名，觸發 器名，序列名，視圖名的長度均不能超過 30個字符；第二：構成規范：數據庫各種名稱必須以字母開頭，但嚴禁使用SYS開頭；名稱只能含有字母，數字和下劃線“_"三類字符，“_" 用于間隔名稱中的各語義字段；不要使用DUAL作表名；第三：大小寫規范：構成Oracle數據庫

13、中的各種名稱（表明，字 段名，過程名，視圖名等等）的所有字符，必須使用大寫，也就 是不能在腳本中，對任何名稱添加雙引號來設定字符的大小 寫形式，只要不采用“"限制， Oracle自動會將各名稱轉化成大 寫。2、表的設計規范2.1、 表的主鍵規范遵循如下三點原則：第一：有無原則：除臨時表和外部表，以及流水表， 日志表外，其他表都要建立主鍵；第二：構成原則：主鍵不能使用含有 實際語義的列，應該增加一個 xx_id字段做主鍵，類型為number,取值 來自序列sequence第三：創建原則：對于 500萬以上的表，請數據組 參與設計實施，采用先建唯一索引再添加主鍵約束的方式來創建主鍵；2.

14、2、 表的主鍵列規范對于實體表，主鍵就是一列，就是沒有任何語義的自增的 NUMBER歹L 對于關系表，主鍵就是相關實體表主鍵形成的復合主鍵，是多列；2.3、 使用注釋的規范原則 每個表，每個字段都要有注釋，說明其含義，對于冗余字段還 要特別說明其維護方法，外鍵字段說明參照與那個表衡量原則上誰設計誰注釋方法 查詢字典表user_tab_comment矯口 user_col_comments可知道表 和字段的注釋信息實施對表添加注釋：SQL>comment on table <table name> is 'xx'對字段添加注釋：SQL>comment on

15、 column<table name>.<col name> is 'xx;2.4、 一個表所含字段總長度的規范原則 一個表中的所有字段，應當能存儲在一個數據塊中（BLOCK）, 也即：表的單行字段總長度 <db_block（減去pctfree）衡量 對不含有大對象數據類型字段的表，字段數大于 50個的，請 數據組參與設計方法查詢字典表USER_TAB_COLUMNS中的字段 DATA_LENGTH得到表中所有字段的總長度，再依據 db_block和表的pctfree參數可以判斷是否一個數據行可以存儲 在一個數據塊（BLOCK）中實施 如果所有字段的總長度

16、超出了一個數據塊，那么需要將該表拆分成兩個（甚至多個）表，拆分的依據是字段的頻繁使用程度， 也就是頻繁使用的字段在一個表中，很少被使用的字段放在另一個表中，他們之間使用相同的主鍵值，用主外鍵關聯。這點 就是“一個表所含字段訪問頻繁度的規范”2.5、 一個表所含字段訪問頻繁度的規范原則個表中的各字段的訪問頻繁度應該基本一致衡量如果一個表的字段數超過50個，請數據組參與審核方法如果一個表的字段數過多超過 50個，并且依據業務邏輯確定該表中一些字段頻繁被訪問， 另一些字段則很少被訪問，則該表需要做拆分處理，這在 OLAP系統中比較常見；目的：這樣可以避免讀取頻繁信息時多讀取很少被訪問的信息，可以提高

17、IO性能，減少內存耗費；實施將訪問頻繁度相差太遠的字段拆分到兩個表中，一個表存頻繁訪問的字段，另一個表存很少被訪問的字段2.6、一個表所含數據量的規范原則一個非分區表中的數據量不要超過500萬衡量一個非分區表中的數據量超過 500萬，請數據組參與設計成分區表，如果該表數據量超過 5000萬，請DBA參與設計方法在系統上線前，通過對業務分析，判斷一個表的數據量；在系統上線后，可以通過exp的日志，Top性能SQL, count(1)來發現數據量大的表實施將這些表進行分區，具體方法請參看分區表的設計規范2.7、 大對象字段(BLOB, CLOB)使用規范原則 存儲圖片，視頻，音頻，文件，500字節

18、以上文本等占用太多 空間的字段(大對象字段)，不能和其他字段存儲在一個表中衡量 含有大對象(BLOB, CLOB)字段的表設計和存儲請數據組參 與設計方法 方法一：數據庫存儲，可以重新建一個表專門存儲該大對象字 段，該表基本為兩個字段，一個為大對象編號ID為主鍵，一個為大對象內容本身，并將該主鍵在原表中作外鍵關聯， 該大 對象表存儲在單獨的表空間中；方法二：操作系統存儲，將這 些文件存儲在操作系統空間中，大對象字段存儲該文件的全路徑名比較：如果該大對象字段常被修改，那么采用方法一；如果該大對象信息為靜態， 加載后基本不變，那么可以采用方 法二，它有一個致命缺點就是信息存儲在數據庫外部， 不安全

19、, 容易丟失。2.8、 增量同步表的設計規范字典信息表和需要使用增量同步的表必須增加如下屬性:屬性名取值說明StatusChar(1)Y/N : Y為激活N為作廢，默認為Y標識該行是否使用。用于軟刪除，軟刪除 需將主鍵和唯一約 束列添加隨機數后 綴。Create_timeDate默認為sysdate創建時間Update_timeDate默認為sysdate最后修改時間2.9、 表的表空間使用規范原則 依據表的DML頻度而使用不同的表空間衡量 表空間的規劃由建庫人員完成方法 為了減少空間碎片問題，（1）將很少被DML （增刪改）的靜 態表，放在一組表空間中；（2）將只發生INSERT的表放到 一

20、組表空間中，（3）而將常發生兩種以上 DML操作的動態表 放在另一組表空間中，這三組表空間不能相交實施 在上線前，依據需求分析確定動態表和靜態表，將他們做最分離；上線運行之后，依據數據庫性能分析得到的信息來區分動 態表和靜態表2.10、 索引的表空間使用規范原則 表和索引原則上應該使用不同的表空間存儲，并且不同 DML 頻度的表的索引，放在不同的表空間中衡量 表空間的規劃有數據組或者 DBA實施方法 絕大多數情況下，動態表的索引必然是動態的， 靜態表的索引 必然是靜態的，依據對表的分析來確定索引的動靜性實施 將動態表的索引放在一組表空間中， 靜態表的索引放在另一個 表空間中，兩組不相交。而且和

21、表所在的表空間也不相交。3、設計分區表的規范3.1、 RANGE分區的規范原則 大數據量的表需進行分區化衡量當表的數據量超過500萬，請數據組參與設計成分區表，當表的數據量超過5000萬，請DBA參與設計方法SQL常依據某列的范圍訪問表，則對表使用 RNAGE分區，常見情況是SQL根據時間范圍進行查新，則使用時間字段作為分區關鍵字進行 RANGE分區；將對表的多種訪問結合考慮來確定分區的細度：1 .大多數SQL操作的分區關鍵字值的范圍；2 .數據維護的需要，比如以月為單位刪除歷史數據；3 .數據訪問的性能，以操作范圍確定的分區數據量還是過大，比如大于500萬，則還需要進行細分；4 .一個分區的

22、數據量要小于 500萬，這是一個硬性的尺度，但從技術上來看，每個分區10萬數據量的情況比每個分區 20萬數據量的情況要快很多，所以需要靈活掌握；實施 1.當各個分區中的數據能均等劃分時性能最好，如果相差太大，則考慮采用其它分區，或者將大數據量的分區再進行HASH子分區；2各分區采用各自的表空間存儲， 使用user_tab_partitions字典來查看確定每個分區的表空間位置；5 .分區表的索引采用本地索引，因為常會根據分區關鍵字（比如時間）進行分區維護（比如刪除1年前的數據，也就是刪除1年前的分區），分區維護時全局索引會失效，而本地索引不會失效，這能保證訪問表時索引正常可用;3.2、 LIS

23、T分區的規范原則 大數據量的表需進行分區化衡量當表的數據量超過500萬，請數據組參與設計成分區表，當表的數據量超過5000萬，請DBA參與設計方法SQL常居于某列的散列值訪問表，則對表使用LIST分區，LIST分區不支持多列分區關鍵字；常見情況針對某個地區或者某 個業務進行數據訪問，那么就使用地區編號或者業務編號作 為分區關鍵字；將對表的多種訪問結合考慮來確定分區的細度：1 .一般使用一個分區關鍵字的值來劃定一個分區；2 .可以把分區關鍵字的值相對應數據比較少的幾個分區合并 作一個分區；3 .如果一個分區關鍵字值所對應的數據量過大，比如大于500萬，則應該對表采用 RANGE分區，對該值的分區

24、再采用 HASH子分區；也就是說，一個可以采用 LIST分區的表，肯 定可以轉化成RANGE分區（可帶子分區），反之不然；4 .一個分區的數據量要小于 500萬，這是一個硬性的尺度，但 從技術上來看，每個分區10萬數據量分區方法比每個分區 20 萬數據量的分區方法要快很多，所以需要靈活掌握；實施1.各分區采用各自的表空間存儲，使用user_tab_partitions字典來確定每個分區的表空間；5 .分區表的索引采用本地索引3.3、 HASH分區的規范原則大數據量的表需進行分區化衡量當表的數據量超過500萬，請數據組參與設計成分區表，當表的數據量超過5000萬，請DBA參與設計方法SQL訪問表

25、不按照某列的范圍進行，也不按某列離散值進行，而且對該表的數據不會依據某列的值范圍或者離散值進行定期維護，那么使用 HASH分區；HASH分區是不知道應該選擇何種分區時的選擇；HASH分區的各分區都可能存有各種 情況的數據，故而不能用于依據分區清理數據的情況； 對確定分區細度的考慮：1 .依據分區的數據量規劃和表的最大數據量來確定分區數；2 .一個分區的數據量要小于 500萬，這是一個硬性的尺度，但從技術上來看，每個分區10萬數據量分區方法比每個分區 20 萬數據量的分區方法要快很多，所以需要靈活掌握；實施1.各分區采用各自的表空間存儲，使用user_tab_partitions字典來確定每個分

26、區的表空間；3 .對于HASH分區表，大多數情況下依然要求采用本地索引，但是如果分區過細，也可以采用全局索引，因為根據HASH分區表的特征（各分區無業務區分，都有數據），該表很少會發生分區維護的工作；3.4、 RANGE-LIST分區的規范原則大數據量的表需進行分區化衡量 當表的數據量超過500萬，請數據組參與設計成分區表，當表的數據量超過5000萬，請DBA參與設計方法 SQL訪問表時，既依據某列值的范圍，又依據其他列的離散 值或者范圍，這種情況下采用 RANGE-LIST復合分區，常用 于語表中的數據需要依據一個時間字段做周期性刪除等維護，并且正常業務SQL訪問既依據時間字段，又依據其他字

27、 段的散列值進行訪問的情況；比如：電信增值業務計費表，既有時間又有業務屬性列，統 計的時候，會選擇時間范圍和業務屬性，所以可以以時間列 為分區關鍵字建立 RANGE分區，以業務屬性列為關鍵字建 立LIST子分區；分區劃分的方法：1 .就按照大多數范圍訪問的范圍值來劃定RANGE分區的范圍，依據單個LIST子分區關鍵字的值來劃分子分區；2 .如果LIST子分區中數據量較小而且又常被一起訪問的子分區可以合并成一個子分區；3 .如果LIST子分區中一個子分區關鍵字值對應的子分區數據 量還是很大，超過500,影響性能，那么可以通過細分 RANGE 分區來達到減少LIST子分區數據量的目的，這點和 LI

28、ST分 區在該情況下的處理方法（轉化成 RANGE-HASH ）不同； 實施1.各子分區應該盡量分散到不同的表空間中存儲，使用user_tab_subpartition殍典來確定每個子分區的表空間；4 .RANGE-LIST大多數情況采用本地索引，因為常根據 RANGE分區關鍵字的來進行分區維護；3.5、 RANGE-HASH分區的規范原則大數據量的表需進行分區化衡量當表的數據量超過500萬，請數據組參與設計成分區表，當表的數據量超過5000萬，請DBA參與設計方法SQL訪問表時，主要依據某個列的范圍進行訪問，即訪問特征符合RANGE分區的要求，或者數據維護特征符合 RANGE 分區的要求，但

29、是以SQL或者維護的數據范圍來劃定分區， 分區數據量又很大，對性能有影響，需再進行子分區，由于 分區中的數據都會被訪問到，所以子分區采用HASH方法，整個表就是 RANGE-HASH 分區； 劃定分區的方法：再依據性能情況來確定 HASH子分區的數據量;實施1.各子分區應該盡量分散到不同的表空間中存儲，使用user_tab_subpartition殍典來確定每個子分區的表空間；2.RANGE-HASH大多數情況采用本地索引，因為常根據RANGE分區關鍵字的來進行分區維護；4、索引的設計規范4.1、 主鍵索引的規范原則對數據量表應該先在主鍵列建唯一索引，再建主鍵約束衡量 分區表的主鍵必須采用該方

30、法方法 主鍵上隱含索引，drop或disable主鍵時，索引會丟失，為保證性能不變，為了對主鍵約束和相應索引有更多的控制，對大表(分區表)的索引采用如下方式建立：(1)在準備建主鍵的列上建立唯一索引( UNIQUE INDEX ):CREATE UNIQUE INDEX Index_Name ONTable_Name(Column_Name) TABLESPACE TBS_INDEX;(2)再加上主鍵約束：ALTER TABLE Table_Name ADD (PRIMARY KEY(Column_Name) USING INDEX TABLESPACETBS_INDEX );Oracle會在

31、指定的列上加上主鍵約束，并且使用該索引實施分區表的主鍵默認索引是全局索引，所以主鍵索引的分區方法：先建立分區化的唯一索引，再建主鍵約束，4.2、 唯一約束索引的規范原則針對大數據量表應該先在唯一約束列上建立普通索引，再添加唯一性約束衡量 分區表的唯一約束必須采用該方法方法 刪除或禁用唯一性約束通常同時使相關聯的唯一索引失效，因而降低了數據庫性能。要避免這樣問題，可以采取下面的步驟：(a)在唯一性約束的列上創建非唯一性索引(普通索引)；(b)添加唯一性約束；4.3、 外鍵列索引的規范原則 無論表的大小，外鍵列都要建立索引原因 其一是為了子父表關聯查詢的性能考慮， 其二是為了避免父子表修改而發生死

32、鎖實施 普通表的外鍵列建立普通索引即可，如果表是分區表，則依據表的情況建立本地索引或者全局索引4.4、 復合索引的規范原則復合索引只有在該種復合常被和該表相關的大多數SQL使用時才建立衡量 復合索引的列數不能超過 3個，否則該索引很少會被使用實施(1)復合索引的第一列，可以通過不使用該種復合的SQL來確定。假設一些SQL的WHERE中復合使用列為 ABC,而其他 一些SQL的WHERE中常使用的是C歹那么該復合索引可 以按照CAB的順序建立，這樣上述兩種SQL都能使用該索弓 (2)對于不能把握好的復合索引，請在選擇性大的列上分別建立 單列索引；(3)切忌不能將表相關的所有 SQL中WHERE涉

33、及到的列復合 起來建立復合索引；4.5、 函數索引的規范原則由于使用形式需和創建形式一致，盡量避免使用函數索引衡量如果想要使用函數索引，請盡量進行轉化方法 由于函數索引在使用時，使用形式必須和創建形式一致，故應該盡量避免使用函數索引，盡量采用如下方法轉化SQL以避免函數索引的使用：原本在WHERE中列上添加函數的，取函數的反意義函數添加到“現一側的常數項上，這樣只需要在列上建立普通索引即可，比如常見的日期轉化函數：TO_CHAR（CREATE_TIME尸'2010-07-07 采用 TO_DATE（）轉化為 CREATE_TIME=TO_DATE（ '2010-07-07 :

34、yyyy-mm-dd，）4.6、 位圖索引的規范原則 靜態表中的低基數列可以使用位圖索引衡量 在事務型數據庫（OLTP）中禁止使用位圖（bitmap）索引，在 報表型數據庫（OLAP）中的靜態表，可以適當使用；方法 對于常發生DML操作的表，不能建立位圖索引，請建立普通的索引即可，否則該表的相關操作很容易造成鎖等待，使系統性能大受影響；其次，索引列需要低基數，只有幾個數值，比如性別列（男，女，保密）和學歷列（大專，本科，研究生，博士生）4.7、 反向索引的規范原則 列值順序增加的列，其上的 WHERE運算是 或者=而不是范 圍（between and或者 and ）檢索時，可以采用反向函數衡量

35、一般創建反向索引的列為 NUMBER類型，值由SEQUENCE 生成方法實施4.8、 分區索引的規范原則 對分區表的索引，需要做分區維護的，必須使用局部索引衡量 一般情況下，HASH分區表可以采用全局索引，其他分區，包括RANGE-HASH也應該采用本地索引，主要是由于HASH分區表不常進行分區維護；方法實施4.9、 索引重建的規范原則 重建索引使用 ALTER INDEX REBUILD 方式，禁止采用DROP INDEX & CREATE INDEX 方式；衡量 分區表等大數據量表的索引必須采用 ALTER INDEXREBUILD方式重建方法 ALTER INDEX IDX_NA

36、ME REBUILD TABLESPACE TBSP_NAME實施 可以在現網上直接實施5、SQL訪問規范5.1、 避免 SELECT *程序中不能出現SELECT* ,即使是選擇全部選擇項，也需要全部 指明，這主要出于如下原因：第一：使用*相對比較慢，因為Oracle 需要遍歷更多的內部字典信息；第二：為避免以后相關表增加字段造成程序錯誤，比如 INSERT INTO SELECT和SELECT INTO 語 句會報錯；5.2、 避免笛卡爾運算多表關聯查詢不能出現笛卡爾積，如果在報表中為集聚表(或稱中 間表)生成多個維度組成的復合主鍵需要使用迪克爾積的， 必須請 數據組確認性能。5.3、 使

37、用CTAS備份在進行DML操作(INSERT, UPDATE , DELETE )之前，必須對 數據進行備份，使用如下語句：方法一：表數據全部備份：CREATETABLE TAB_NAME_BAK AS SELECT * FROM TAB_NAME;方法二：部分備份：對大表僅備份將要修改的數據：CREATE TABLE TAB_NAME_BAKAS SELECT * FROM TAB_NAME WHERE 選擇出被操作數據的條件；5.4、 INSERT時需寫全列名代碼中INSERT語句必須寫出全部列名，以保證表增加字段后語 句執行不受影響：如：INSERT INTO TAB(COL1,COL2

38、)VALUES(COL1_VAL,COL2_VAL) ;再如：INSERT INTO TAB(COL1,COL2)SELECT COL1_VAL,COL2_VAL FROM TAB_BB ;不能將 COL1,COL2 和 COL1_VAL,COL2_VAL 省略；5.5、 大數據量的DMLDML操作涉及到大數據量時，請分解為多次執行；對于UPDATE和DELETE每次涉及數據量在1萬條左右，并且每 次執行完就提交；對于 INSERT INTO SELECT 如果采用提示(/*+ append parallel */)可以處理百萬級別的數據量。5.6、 完成事務及時commit對于一個完成了的事

39、務，請用 commit顯示提交，這是避免鎖爭用的鎖 等待的需要，特別是對 DML操作頻繁的表；5.7、 java的變量綁定使用“變量綁定”來處理一條 SQL帶不同常量多次執行的情況，動態綁定可以大大優化 SQL的執行效率，還可以優化 Oracle的內 存使用。在Java中，結合使用setXXX系列方法，可以為不同數據類型的 綁定變量進行賦值，從而大大優化了SQL語句的性能。JAVA情況下的動態綁定示例如下：String v_id = 'xxxxx'String v_sql = 'select name from tb_a where id = ?'stmt =

40、con.prepareStatement( v_sql );stmt.setString(1, v_id ); /的綁定變量賦值stmt.executeQuery();5.8、 perl的變量綁定使用“變量綁定”來處理一條 SQL帶不同常量多次執行的情況，動態綁定可以大大優化 SQL的執行效率，還可以優化 Oracle的內 存使用。PERL綁定變量實例如下：$modsql = qqinsert into tmp_tai_rtkpi_mark (tab_name, kpi_id, ne_id, timepoint, cacu_time, start_time, stop_time, down_b

41、ase, up_base, ajast_flag, inuse_flag, cal_data)values(?,?,?,?,?,?,?,?,?,?,?,?);"/n/n");if ( !$dbh->prepare($modsql) ) writeToLog( "start SQL prepare Error!/n” . DBI:errstr .$sth_msg_in_DB = $dbh->prepare($modsql)$kpiid_tab$kpi_id);$kpi_id );-1 )；-1 )；$current_time );$start_time

42、);$end_time );$temp_min );$temp_max );| die( "start SQL prepare Error!/n" . $DBI:errstr . "/n");$sth_msg_in_DB->bind_param( 1,$sth_msg_in_DB->bind_param( 2,$sth_msg_in_DB->bind_param( 3,$sth_msg_in_DB->bind_param( 4,$sth_msg_in_DB->bind_param( 5,$sth_msg_in_DB->

43、bind_param( 6,$sth_msg_in_DB->bind_param( 7,$sth_msg_in_DB->bind_param( 8,$sth_msg_in_DB->bind_param( 9, $sth_msg_in_DB->bind_param( 10, 0 );$sth_msg_in_DB->bind_param( 11,1);$sth_msg_in_DB->bind_param( 12, -1 );$sth_msg_in_DB->execute() | die( "SQL Execute Error!/n” . $DBI

44、:errstr . "/n");5.9、 避免重復訪問：使用 group避免重復訪問（一）：同源單組單查詢如下語句要避免:WHERE CLASS= A UNION ALLWHERE CLASS= B' UNION ALLWHERE CLASS= C'SELECT CLASS,sum(COL) FROM TAB_TESTSELECT CLASS,sum(COL) FROM TAB_TESTSELECT CLASS,sum(COL) FROM TAB_TEST 改寫成：SELECT CLASS,sum(COL) FROM TAB_TEST GROUP BY CL

45、ASS5.10、 避免重復訪問：豎向顯示變橫向現實避免重復訪問（二）：豎向顯示變橫向顯示問題語句：SELECTA .C1 AC1 ,A.C2AC2 ,A.C3AC3 ,B.C1BC1 ,B.C2BC2,B.C3BC3,C.C1CC1,C.C2CC2,C.C3CC3FROM(SELECT'123'X,'SYNONYM' C1, sumC2,count C3FROMTAB WHERE TABTYPE = 'SYNONYM' )A,(SELECT '123' X,'TABLE' C1, sum(2)C2,count C3

46、FROMTAB WHERE TABTYPE = 'TABLE' )B, (SELECT'123'X,'VIEW C1, sum(2)C2,count(1)C3FROMTAB WHERE TABTYPE = 'VIEW )C ;SELECTMAX (DECODE (TABTYPE ,'SYNONYM' ,'SYNONYM' ,NULL ) AC1 , MAX (DECODE (TABTYPE ,'SYNONYM' ,sum(2),0)AC2, MAX (DECODE (TABTYPE ,'SY

47、NONYM' ,count,0)AC3, MAX (DECODE (TABTYPE ,'TABLE' ,'TABLE' ,NULL ) BC1, MAX (DECODE (TABTYPE ,'TABLE' ,sum(2),0)BC2, MAX (DECODE (TABTYPE ,'TABLE' ,count(1),0)BC3, MAX (DECODE (TABTYPE ,'VIEW ,'VIEW ,NULL ) CC1, MAX (DECODE (TABTYPE ,'VIEW ,sum(2),0)C

48、C2, MAX (DECODE (TABTYPE ,'VIEW ,count(1),0)CC3FROMTABWHERETABTYPE IN ('TABLE' ,'SYNONYM' ,'VIEW ) GROUPBY TABTYPE ;5.11、 避免重復訪問：用表更新表避免重復訪問(三)：一個表同時更新另一個表的多個字段問題SQL:使用TB_SOURCE表更新表TB_TARGET的多個字段UPDATE TB_TARGET A SETA.COL1 = (select B.COL1 from TB_SOURCE B where B.id = A.id),A.COL2 = (select B.COL2 from TB_SOURCE B where B.id = A.id),A.COL3 = (select B.COL3 from TB_SOURCE B where B.id = A.id),A.CO