開發成功的oracle數據庫

前言 1.基于數據庫（或依賴于數據庫）構建的應用是否成功，這取決于如何使用數據庫。另外，從我的經驗看，所有應用的構建都圍繞 著數據庫。如果一個應用未在任何地方持久地存儲數據，很難想象這個應用真的有用。 2.應用總是在“來來去去”，而數據不同，它們

前言

1.基于數據庫（或依賴于數據庫）構建的應用是否成功，這取決于如何使用數據庫。另外，從我的經驗看，所有應用的構建都圍繞
著數據庫。如果一個應用未在任何地方持久地存儲數據，很難想象這個應用真的有用。
2.應用總是在“來來去去”，而數據不同，它們會永遠存在。從長遠來講，我們的目標并不是構建應用，而應該是如何使用這些應用
底層的數據。
3. 開發小組的核心必須有一些精通數據庫的開發人員，他們要負責確保數據庫邏輯是可靠的，系統能夠順利構建。如果已成事實（應
用已經部署）之后再去調優，這通常表明，在開發期間你沒有認真考慮這些問題。

正文

了解數據庫

1.數據庫的體系結構，數據庫如何工作，以及有怎樣的表現。
2.并發控制是什么，并發控制對你意味著什么。
3.性能、可擴縮性和安全性都是開發時就應該考慮的需求，必須適當地做出設計，不要指望能碰巧滿足這些需求。
4.數據庫的特性如何實現。某個特定數據庫特性的實際實現方式可能與你想象的不一樣。你必須根據數據庫實際上如何工作（而不
是認為它應該如何工作）來進行設計。
5.數據庫已經提供了哪些特性，為什么使用數據庫已提供的特性要優于自行構建自己的特性。
6.為什么粗略地了解SQL還不夠，還需要更深入地學習SQL。
7.DBA和開發人員都在為同一個目標努力，他們不是敵對的兩個陣營，不是想在每個回合中比試誰更聰明。

如果可能，盡量利用一條SQL語句完成工作。

1. 如果無法用一條SQL語句完成，就通過PL/SQL實現（不過，盡可能少用PL/SQL！）。
2.如果在PL/SQL中也無法做到（因為它缺少一些特性，如列出目錄中的文件），可以試試使用Java存儲過程來實現。不過，有了Oracle9i 及以上版本后，如今需要這樣做的可能性極小。
3.如果用Java 還辦不到，那就在C 外部過程中實現。如果速度要求很高，或者要使用采用C 編寫的一個第三方API，就常常使用這種做法。
4.如果在C外部例程中還無法實現，你就該好好想想有沒有必要做這個工作了。

SQL優化--基本概念

數據庫訪問表數據的方式

Oracle雖然可以通過各種執行方式存取數據，但是在最后訪問數據表時只有兩種方式。

?全表掃描

全表掃描就是順序地訪問表中每條記錄。Oracle將數據保存在數據塊(database block)中，通過一次讀入多個數據塊的方式優化全表掃描。

數據塊是數據庫存取數據的最小I/O單位，只有將數據塊讀到內存中才能查找數據。

?通過ROWID訪問表

ROWID是Oracle數據庫的一個偽列，唯一標識數據表中的數據行，數據一旦插入數據庫，該行的ROWID將不能再被改變。

ROWID是訪問數據表的最快方法，通過ROWID，Oracle可以直接定位到數據塊上。

索引除了包含索引列值外還存儲對應行的ROWID，所以，提供了快速訪問ROWID的方法，因此，基于索引的查詢性能很高。

索引的代價

索引雖然是提高數據查詢最有效的方法，但是無效的索引會造成數據庫空間的浪費，甚至大大降低查詢性能。

1.索引需要磁盤空間存儲

2.執行數據修改操作（INSERT、UPDATE、DELETE）產生索引維護

3.在數據處理時需額外的回退空間

4.索引和數據不在一個數據塊上，使用索引會增加系統I/O

建議一張表的索引不要超過3個，但是對于穩定表可以多建立索引提高查詢速度。

索引的分類

?索引按功能分類

–Primary key(主關鍵字)

–Foreign key(外鍵)

–Unique Index(唯一索引)

–Index(一般索引)

?索引按存儲方法分類

–B-樹索引

–位圖索引

–HASH索引

–索引編排表

–反轉鍵索引

–分區索引

–本地和全局索引

?索引按對象分類

–單列索引（表單個字段的索引）

–多列索引（表多個字段的索引）

?Primary key(主關鍵字)

主關鍵字是保證在一個表中的數據唯一，在創建主關鍵字時，數據庫自動在主關鍵字上創建唯一索引。一個表只能創建一個主關鍵字。

?Foreign key(外鍵)

外鍵是表和表之間建立主從關系，又叫父子關系，外鍵只能關聯到主表的主鍵或唯一索引上（因為關系型數據庫不支持多對多關系）。外鍵并不自動創建索引。

對于一對多的兩個表，外鍵建立在多的表上。必須滿足“有子必有父”的關系，即插入數據必須先插入主表數據后才能插入子表數據，刪除主表數據前必須刪除子表數據。

?Unique Index(唯一索引)

創建唯一索引的字段或組合字段在數據上必須唯一。

?Index(一般索引)

一般索引沒有數據約束限制，目的是為了加快查詢速度。

不能對字段或組合字段重復創建索引，組合字段重復是指字段和字段順序完全相同。

?B-樹索引

B-樹索引是最常用的索引，其存儲結構類似書的索引結構，有分支和葉兩種類型的存儲數據塊，分支塊相當于書的大目錄，葉塊相當于索引到的具體的書頁。一般索引及唯一約束索引都使用B-樹索引。

?位圖索引

位圖索引儲存主要用來節省空間，減少數據塊的訪問，它采用位圖偏移方式來與表的行ID號對應，采用位圖索引一般是重復值多的表字段。位圖索引在OLTP（數據事務處理）中用得比較少，因為OLTP會對表進行大量的刪除、修改、新建操作。在OLAP（數據分析處理）中應用位圖有優勢，因為OLAP中大部分是對數據庫的查詢操作，而且一般采用數據倉庫技術，所以大量數據采用位圖索引節省空間比較明顯。

注：B-樹索引中包含ROWID，Oracle可以在行級別上鎖定索引。位圖索引被存儲為壓縮的索引值，是一個范圍內的ROWID，因此ORACLE必須針對一個給定值鎖定所有范圍內的ROWID，極易造成死鎖。

單列索引

以單個字段建立的索引

多列索引

已多個字段組合建立的索引

函數索引（對字段進行函數運算的索引）

在索引中使用函數或者表達式，這些函數可以是Oracle的函數，也可以是用戶自己的PL/SQL函數等 ，函數索引是大小寫敏感的。

在什么情況下應該建立索引

–表主關鍵字

數據庫自動建立索引

–字段唯一性約束

–主從表關聯

因為在查詢中經常會和其他表關聯查詢

–經常查詢字段或組合查詢字段

–查詢中排序或分組的字段

索引是排序的，所以，排序字段或分組字段如果通過索引去訪問將大大提高查詢速度

在什么情況下不應該建立索引

–表記錄很少的表

數據庫使用索引，必須先訪問索引表，再通過索引表訪問數據表，一般索引與數據表不在同一個數據塊，Oracle至少要讀取數據塊兩次。如果表數據很少，Oracle會將所有的數據一次讀出，處理速度顯然會比用索引快。

–經常更新的表（不穩定表）

數據更新會造成索引的維護，影響數據更新時間。

–數據重復且分布均勻字段

對于大數據表，如果一個字段只有少量的值，并且分布平均，建立該字段的索引一般不會提高數據庫的查詢速度。

SQL優化--索引概念

通過Developer看索引使用

Developer提供了Explain plan Window圖形窗口，可以方便地看到SQL的執行計劃。

啟動Explain plan Window有兩種方法，第一種在SQL Window下輸入sql，按F5，第二種，使用菜單new/Explain plan Window。

?窗體介紹

窗體分為兩大部分，上半部分是sql區，可以在此輸入要分析的sql，下半部分是SQL的執行計劃。Optimizer goal可以選擇優化器，其中：

–First_Rows:基于CBO的優化器，側重于返回一條結果記錄，大多數使用Single Block IO（類似于Index Scan）

–All_Rows: 基于CBO的優化器，側重于返回所有結果記錄，大多數使用Mutil Block IO（類似于Full Table Scan）

–Rule: 基于RBO的優化器

–Choose: 根據表是否做過分析來選擇使用RBO還是CBO。如果有一個或以上的表做過表分析，則使用CBO；如果都沒有做過表分析，則使用RBO。

用導航按鍵查看執行步驟，執行步驟是從上到下，從內到外。

優化器

目前Oracle的優化器共有三種：

–Rule基于規則-- Rule Based Optimizer

–Cost基于成本-- Cost Based Optimizer

–Choose選擇性。

數據庫在默認安裝情況下，Oracle使用Choose優化器。

Rule（基于規則--Rule Based Optimizer）

From子句從外側向內側的順序檢索表，Where子句從下向上解析條件。

根據From規則，在多表查詢時，將返回結果集最少的表作為基礎表，寫在From最外側（并不是最小的表，應該是查詢限定最強的表，一般情況下是查詢主體表），然后根據表之間的限定依賴關系依次從外側寫到內側。Oracle執行時，首先掃描最外側表（驅動表），并對結果集進行排序，然后掃描內側表（被探測表），將第二個表檢索出的結果集（用Row Source2表示）與第一個表中相應結果集（用Row Source1表示）進行合并后再次向內側表掃描合并，直到全部表被檢索合并，返回結果集。

根據Where規則，先寫表連接，表連接從內側向外側的順序寫，可以過濾掉最大數量記錄的條件必須寫在WHERE子句的末尾（結合From規則，末尾的查詢條件應該是最外側的表）。寫查詢條件時應該按照使用的索引字段順序從上寫到下。

當查詢表有N個，表關聯應該是N-1個。

Cost（基于成本--Cost Based Optimizer）

CBO是依賴表的統計信息來選擇最低成本的執行計劃， 這些統計使用ANALYZE命令完成，在使用CBO之前必須將Table分析好。

CBO最多只能排列2000種表的排列關系，也就是說當超過6個以上的表（2的7次方大于2000）進行關聯時，CBO就不能把所有的排列關系都計算到，會遺漏一些，所以這個時候生成的執行計劃據可能不是最優的。

對于數據變化比較大的表，你必須經常運行analyze 命令,以增加數據庫中的對象統計信息(object statistics)的準確性，以提高系統效率。

語法：

analyze table table_name estimate statistics sample x percent;

--抽樣估算法，使用x%抽樣率對table_name表分析

analyze table table_name compute statistics;

--完全計算法

對表作完全計算所花的時間相當于做全表掃描，抽樣估算法由于采用抽樣，比完全計算法的生成統計速度要快，如果不是要求要有精確數據的話，盡量采用抽樣分析法。建議對表分析采用抽樣估算，對索引分析可以采用完全計算。

Choose（選擇性）

根據表是否做過分析來選擇使用RBO還是CBO。如果有一個以上的表做過表分析，則使用CBO；如果都沒有做過表分析，則使用RBO。

索引的掃描分類

?索引唯一掃描(Index unique scan)

?索引范圍掃描(Index range scan)

?索引全掃描(Index full scan)

?索引快速掃描(Index fast full scan)

?索引跳躍掃描(Index skip scan)

索引唯一掃描(Index unique scan)

通過唯一索引返回單行數據的查詢方法稱為索引唯一掃描。如果存在UNIQUE 或PRIMARY KEY 約束（它保證了語句只存取單行）的話，Oracle經常實現唯一性掃描。

如EMP表創建了Id主關鍵字，如果使用Id作為條件查詢，Oracle使用Index Unique Scan。

Select * from emp where id = 2343;

索引范圍掃描(index range scan)

使用一個索引返回多行數據的查詢方法稱為索引范圍掃描。使用索引范圍掃描的情況有：

–在唯一索引列上使用了range操作符(> < <> >= <= between)

–在組合索引上，只使用部分字段進行查詢，導致查詢出多行

–對非唯一索引列上進行的任何查詢。

索引全掃描(index full scan)

全索引掃描只發生在CBO模式下。當CBO根據統計數值判斷全索引掃描比全表掃描更有效時，才進行全索引掃描，而且此時查詢出的數據都必須從索引中可以直接得到。

如select id from emp order by id;

因為查詢字段屬于索引字段同時需要索引字段排序，Oracle使用Index full scan。

索引快速掃描(index fast full scan)

掃描索引中的所有的數據塊，與 index full scan很類似，但是一個顯著的區別就是它不對查詢出的數據進行排序，即數據不是以排序順序被返回。在這種存取方法中，可以使用多塊讀功能，也可以使用并行讀入，以便獲得最大吞吐量與縮短執行時間。

如select id from emp;

索引跳躍掃描(Index skip scan)

當查詢條件沒有使用組合查詢的前導列，優化器可以使用跳躍掃描，跳躍掃描比全掃描性能高。

表的關聯

在ORACLE中表的關聯有三種：

–Sort merger join（排序合并關聯）

–Nested Loops（嵌套循環）

–Hash join（哈希關聯）

Sort Merger join（排序合并關聯）

多出現于大表和大表以索引的方式連接。

執行過程：

1.掃描第一個表返回結果集，然后按照關聯列排序；

2.掃描第二個表返回結果集，然后按照關聯列排序；

3.兩邊已排序的行被放在一起執行合并操作。

此連接方式建立在排序的基礎上，而排序操作消耗的系統資源很大，所以，這種方式對于結果集已經排序的連接比較有效。

Nested Loops（鑲嵌循環）

多出現于小表和大表關聯

執行過程：

1.掃描第一個表（驅動表）Row source1；

2.循環Row source1；

3.探索第二個表（被探查表）Row rource2。

4.重復2～3直到循環完Row source。

此連接方式的關鍵是驅動表的返回數據集要少，同時被探查表的匹配要有索引支持。另外，此連接是最快得到第一個匹配行的方式，所以，可以實現快速的響應時間，因為此連接不必等待所有的連接操作處理完才返回數據。

如果驅動表的數量比較大，查詢性能不如全表掃描.

Hash join（哈希關聯）

只能用于相等連接，且只能在CBO優化器模式下，多出現于小表和大表關聯

執行過程：

1.掃描第一個表（驅動表）返回數據集，構建Hash表；

2.讀取第二個表的一條數據，和內存中的數據進行匹配；

此連接方式對于CPU的消耗比較大，并取決于內存的大小，因為Oracle在內存中建立Hash表。

SQL優化--小結

有關SQL的優化涉及的范圍很廣，相關的知識需要在實踐中不斷總結和積累。學習完本節需要掌握的知識：

–Oracle的優化器

–索引的不同分類

–表關聯的方式

–如果使用Explain Plan Window查看執行計劃

–寫DML時應該用不同的sql看性能優劣再確定sql

在PC機上正確創建索引并正確使用索引的情況下，上千萬條數據的單表查詢應該在0.1秒左右，如果使用多表關聯查詢不應該超過0.6秒，但是對于分組查詢可能超過1秒。如果超過你寫的查詢超過這些參考值，說明你寫的SQL還有優化的可能。

數據庫的高伸縮性--并行控制

死鎖：

當兩個事務需要一組有沖突的鎖，而不能將事務繼續下去的話，就出現死鎖 。

查詢死鎖

select b.username,b.sid,b.serial#,logon_time

from v$locked_objecta,v$session b

where a.session_id = b.sid order by b.logon_time;

也可以在Developer中可以使用tools\Sessions...中的Lock查看鎖定情況

殺死死鎖

SQL>alter system kill session 'sid,serial#';

--如果有ora-00031錯誤，則在后面加immediate;

SQL>alter system kill session 'sid,serial#' immediate;

總結

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com