《Oracle效能最佳化求生指南》-第四章：資料庫邏輯設計和實體設計-學習小結-1

1、建立邏輯資料模型為第一階段，包括對應用程式需要處理和儲存的資訊進行建模，並確保所有必要的資料都能夠正確、完整且無歧義地表示。在關聯式資料庫的實現中，這通常是指構造一個標準化的實體-關係(E-R)模型。2、將邏輯資料模型映射為物理資料模型為第二階段。對關聯式資料庫來講，物理資料模型描述的是表、索引、視圖、鍵和其他一些資料庫特性。3、第三範式：實體(表)的所有資料完全依賴於主鍵。不能有重複的屬性(列)或屬性群組。不存在僅依賴部分主鍵的實體資料。不存在依賴於其他非主鍵的實體資料。用一條格言描述：”鍵，完整的鍵，除了鍵沒有其他東西。“4、無論從文檔或定義角度看，邏輯模型中精確定義屬性的資料類型、長度、精度都有優勢。由於Oracle在資料類型的實體儲存體上採用的是通用且靈活性很強的內部實現方式，因此從儲存或效能角度看，使用限制性很強的資料類型或精度並沒有優勢。5、不管如何限制資料類型，大部分情況下，Oracle內部都會使用大範圍高精度的浮點方式進行儲存。6、固定長度類型的字串都會佔用一個固定長度的儲存空間，而不管字串的真實長度是多少。採用固定的航長度可以降低片段，但是它會導致較大的平均行長，這樣就會增加全表掃描的開銷。因此，除非資料的長度確實是固定的，否則就應該優先選擇變長字串類型(VARCHAR)。7、人造鍵：是由Oracle sequence產生的一個數字類型的列。沒有任何含義，只是為了唯一地標識實體中的記錄。從來不會被更新。自然鍵：可由多列組成並可包括任何資料類型。是由實體中具有唯一性的自然屬性構成的。如果自然鍵被更新，則引用它的外鍵也需要更新，這將顯著增加IO開銷和鎖爭用。顯然，必須基於自然鍵的列進行索引查詢的需求也是很常見的，為滿足這種需求，可以在這些列上建立普通索引或唯一約束。8、邏輯階段的主要目的是確保設計能夠滿足應用的功能需求。實體設計階段的目的才是確保資料庫能夠滿足應用的效能需求。將邏輯自雷轉化為表的時候，要避免採用分開的父類表和子類表的方法，而要選擇將所有子類映射為一個表，或採用單獨的子類表而沒有上一級表的方式。9、表的類型有堆表、索引組織表、聚簇等。散列聚簇(Hash Cluster)：能夠有效最佳化大小相對固定的表的主鍵查詢，與B*樹索引相比，散列聚簇還能降低熱點塊上的閂鎖爭用。索引聚簇(Index Cluster)：共用聚簇鍵值的多個表中的記錄儲存在一起，這樣可以最佳化多表連接。雖然多表連接性增強了，但僅針對聚簇中某個表的全表掃描的效能卻降低了。巢狀表格(Nested Table)：能夠針對主表中的某一行最佳化對明細行的檢索。但是，如果要跳過主表行檢索明細行的話，效能通常會大打折扣。索引組織表：如果大部分表訪問都是通過主鍵進行查詢，並且表資料量的變動幅度較大而不適合使用散列聚簇，使用索引組織表將會更高效。對象表(Object Table)：行都被定義為一個Oracle對象資料類型，需要使用對象表的情境非常少。10、精度的意義更多在於約束資料或定義文檔，而不是為了最佳化效能。但是，如果一個高精度的數值被偶然指定給一個精度限制較弱的數字欄位，這時候設定一個精度就可能對效能有所協助。例如NUMBER(*,2)那麼不必要的精度將會被截斷，行的長度也將相應地減少。設定該數值列的精度有時可以減少行的長度。11、DATE可以精確到秒，TIMESTAMP可以亞秒級，可以配置到納秒級的精度，其預設精度則為微秒。12、字串若小於4000位元組，優先選擇VARCHAR2類型。字串若大於4000位元組，推薦使用LOB類型，CLOB儲存字元資料，BLOB儲存位元據。11g提供高效能的LOB儲存類型，稱為SecureFiles。每個表只能有一個LONG資料類型的列，和早期的Oracle版本相容。優先選擇VARCHAR2不是VARCHAR，VARCHAR將來會受到修改，以符合ANSI的標準。13、B*樹索引不能儲存NULL，因此需要全表掃描來尋找NULL值。位元影像索引和(部分列為NULL的)多列複合式索引就能儲存NULL值。採用NULL可以降低行的平均長度，從而一定程度上提高全表掃描的效能。如果該列的數值大部分是NULL，並且查詢僅需檢索非NULL的值，則該列上的索引會比較緊湊並很高效。因此，決定一列是否可以為NULL的時候，要考慮在該列上是否有使用B*樹索引查詢NULL的需求。如果有，則不能指定該列為NULL，取而代之的是定義該列為NOT NULL(非空)，並指定一個預設值。如果要查詢那些未知的值，則不能定義列為空白，相反，要定義列為非空並指定一個預設值。對於字元類型的資料，預設值可以是一個字串，例如UNKNOWN或N/A。對於數字類型，選擇合適的預設值會有難度，例如，統計包含AGE欄位，基於索引掃描或查詢AGE列時，可能需要找出年齡不確定(UNKNOWN)的記錄。如果給AGE指定一個預設值，當查詢平均年齡、最小年齡和最大年齡的時候，可能會得到不正確的結果。這種情況，使用NULL時必要的，但查詢不能快速返回那些AGE不確定的記錄，要麼進行反正常化，增加一個標記列來標記年齡是否已知，並在該標記列上建立索引，以便於查詢AGE不確定(AGEKNOWN=N)的記錄。NULL值可以儲存在位元影像BITMAP索引中，上述中或許不會僅因為這個理由選擇位元影像索引，如果該AGE列上存在位元影像索引，可以高效地檢索NULL值。14、除非表中每一個欄位都是固定長度的字串，否則Oracle無法知道某列在行實體儲存體結構中的具體位置。訪問表中靠後的列相比訪問靠前的列，需要額外消耗少量的CPU資源。因為Oracle必須順序掃描行結構以獲得某個特定的列的位置，基於這個原因，將經常需要訪問的列儲存在表的前面，會帶來一些正面的效能影響。那些值為NULL的欄位通常需要一個位元組的儲存空間，但如果該行中隨後的列的數值都為NULL，則Oracle不需要為這些NULL分配任何空間。如果將那些大部分為NULL的列儲存在表的末尾，則行的實際長度會變小，這樣有助於提高表掃描的效能。這些調整帶來的效能改進比較小，因此列的邏輯順序應使資料模型更易於理解和維護，而不是為了這些微小的最佳化而使邏輯模型中列的順序變得混亂。15、反正常化：是指在物理模型中重新引入冗餘、重複或其他非正常化結構的過程，主要意圖是為了提高效能。16、概要表如果即時摘要資料是必需的，則每當來源資料被更新時，必需同時更新彙總套件資料。通過資料庫觸發器或物化視圖來手動實現這一點，但若更新十分頻繁，則可能導致鎖爭用。如果即時匯總資訊不實必需的，則可以通過週期性作業調度更新概要表-費業務高峰期進行。使用Oracle物化視圖機制實現。但會導致不即時的不準確的匯總資訊。17、Oracle 11g的結果集緩衝。18、進行彙總操作的查詢通常會佔用大量資料庫資源，最好是使用物化視圖來維護反正常化的匯總資訊。19、通常，將邏輯子類型實現為從表會降低常見SQL操作的效能。垂直資料分割(Vertical Partitioning)：經常在全表掃描操作中訪問到的列需要保留在主表中，較少訪問的列則儲存在第二章表中。20、視圖：給使用者只有一張表的假象。通常在應用程式查詢子表的時候用以獲得最佳效能，代價是使SQL應用複雜化。21、Oracle有時會允許將一張表物理地分割成多個資料區段，同時讓使用者保留一個單一邏輯表的印象，例如：LOB資料通常會儲存在單獨的資料區段。索引組織表中，一些資料將被儲存在溢出段中。如果表很大，且預計會有頻繁表掃描，可以考慮將欄位較長且不常訪問的列遷移到一個單獨的子表中，以減少長度和提高表掃描的效能。22、優先使用資料庫觸發器來保證反正常化資料的一致性，避免通過應用代碼來維護。資料庫觸發器降低了資料不一致的風險，簡化了應用代碼，同時更加高效。23、事實表的外鍵幾乎都是由序列產生的人造數字鍵。從儲存的觀點看，如果事實表的外鍵都是有意義的字串的話，則儲存成本比較高：例如，一個產品的具體名稱佔用的空間可能是產品ID佔用空間的10倍。保持較短的行長度對事實表來說是重要的，因為事實表上會經常有全表(或分區)掃描。24、盡量避免使用雪花模式。當維度資料表不包括外鍵的時候，查詢效能通常會得到最佳化。25、通過建立層次化的維，可以提高那些需要使用較高層次聚集資料的查詢的效能，例如，查詢每月總收入將不再需要累加該月份中每天的收入記錄，從而大大提高了效能。可以考慮針對多層次的聚集資料採用多個事實表的方式，採用多個事實表是常用的資料倉儲解決方案，但Oracle還提供了一個更複雜的稱為物化視圖的方法。物化視圖本質是一個物理表，它包含了將會由視圖的定義返回的記錄。如果將普通的視圖看做儲存在資料庫中的查詢語句，物化視圖就可以被看作是資料庫中儲存的查詢結果。Oracle可以自動維護物化視圖，保證是最新的或是定期更新。26、物化視圖最佳實務：針對物化視圖所包括的每一張表，都要建立物化視圖日誌。使用CREATE DIMENSION語句來標識各維度之間的層次關係。設定參數QUERY_REWRITE_ENABLED，啟動查詢重寫功能。選擇合適的物化視圖重新整理策略。推薦使用ON DEMAND重新整理策略，不是ON COMMIT重新整理策略，適用於大量資料更新時重新整理。選擇參數QUERY_REWRITE_INTEGRITY。合理建立物化視圖。如果有疑慮，則需要比較並評估建立與不建立物化視圖對查詢和DML語句的效能影響，以衡量物化視圖帶來的額外開銷能否從查詢效能的提高中得到足夠的補償。