1. 選用適合的ORACLE優化器
ORACLE的優化器共有3種:
a. RULE (基於規則)
b. COST (基於成本)
c. CHOOSE (選擇性)
設置缺省的優化器,可以通過對init.ora檔中OPTIMIZER_MODE參數的各種聲明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS . 你當然也在SQL句級或是會話(session)級對其進行覆蓋.
為了使用基於成本的優化器(CBO, Cost-Based Optimizer) , 你必須經常運行analyze 命令,以增加資料庫中的物件統計資訊(object statistics)的準確性.
如果資料庫的優化器模式設置為選擇性(CHOOSE),那麼實際的優化器模式將和是否運行過analyze命令有關. 如果table已經被analyze過, 優化器模式將自動成為CBO , 反之,資料庫將採用RULE形式的優化器.
在缺省情況下,ORACLE採用CHOOSE優化器, 為了避免那些不必要的全表掃描(full table scan) , 你必須儘量避免使用CHOOSE優化器,而直接採用基於規則或者基於成本的優化器.
2. 訪問Table的方式
ORACLE 採用兩種訪問表中記錄的方式:
a. 全表掃描
全表掃描就是順序地訪問表中每條記錄. ORACLE採用一次讀入多個資料塊(database block)的方式優化全表掃描.
b. 通過ROWID訪問表
你可以採用基於ROWID的訪問方式情況,提高訪問表的效率, , ROWID包含了表中記錄的物理位置資訊..ORACLE採用索引(INDEX)實現了資料和存放資料的物理位置(ROWID)之間的聯繫. 通常索引提供了快速訪問ROWID的方法,因此那些基於索引列的查詢就可以得到性能上的提高.
3. 共用SQL語句
為了不重複解析相同的SQL語句,在第一次解析之後, ORACLE將SQL語句存放在記憶體中.這塊位於系統全局區域SGA(system global area)的共用池(shared buffer pool)中的記憶體可以被所有的資料庫用戶共用. 因此,當你執行一個SQL語句(有時被稱為一個游標)時,如果它
和之前的執行過的語句完全相同, ORACLE就能很快獲得已經被解析的語句以及最好的
執行路徑. ORACLE的這個功能大大地提高了SQL的執行性能並節省了記憶體的使用.
可惜的是ORACLE只對簡單的表提供高速緩衝(cache buffering) ,這個功能並不適用於多表連接查詢.
資料庫管理員必須在init.ora中為這個區域設置合適的參數,當這個記憶體區域越大,就可以保留更多的語句,當然被共用的可能性也就越大了.
當你向ORACLE 提交一個SQL語句,ORACLE會首先在這塊記憶體中查找相同的語句.
這裏需要注明的是,ORACLE對兩者採取的是一種嚴格匹配,要達成共用,SQL語句必須
完全相同(包括空格,換行等).
共用的語句必須滿足三個條件:
A. 字元級的比較:
當前被執行的語句和共用池中的語句必須完全相同.
例如:
SELECT * FROM EMP;
和下列每一個都不同
SELECT * from EMP;
Select * From Emp;
SELECT * FROM EMP;
B. 兩個語句所指的物件必須完全相同:
例如:
用戶物件名如何訪問
Jack sal_limit private synonym
Work_city public synonym
Plant_detail public synonym
Jill sal_limit private synonym
Work_city public synonym
Plant_detail table owner
考慮一下下列SQL語句能否在這兩個用戶之間共用.
SQL 能否共用原因
select max(sal_cap) from sal_limit; 不能每個用戶都有一個private synonym - sal_limit , 它們是不同的物件
select count(*0 from work_city where sdesc like 'NEW%'; 能兩個用戶訪問相同的物件public synonym - work_city
select a.sdesc,b.location from work_city a , plant_detail b where a.city_id = b.city_id 不能用戶jack 通過private synonym訪問plant_detail 而jill 是表的所有者,物件不同.
C. 兩個SQL語句中必須使用相同的名字的綁定變數(bind variables)
例如：第一組的兩個SQL語句是相同的(可以共用),而第二組中的兩個語句是不同的(即使在運行時,賦於不同的綁定變數相同的值)
a.
select pin , name from people where pin = :blk1.pin;
select pin , name from people where pin = :blk1.pin;
b.
select pin , name from people where pin = :blk1.ot_ind;
select pin , name from people where pin = :blk1.ov_ind;
4. 選擇最有效率的表名順序(只在基於規則的優化器中有效)
ORACLE的解析器按照從右到左的順序處理FROM子句中的表名,因此FROM子句中寫在最後的表(基礎表driving table)將被最先處理. 在FROM子句中包含多個表的情況下,你必須選擇記錄條數最少的表作為基礎表.當ORACLE處理多個表時, 會運用排序及合併的方式連
接它們.首先,掃描第一個表(FROM子句中最後的那個表)並對記錄進行派序,然後掃描第二個表(FROM子句中最後第二個表),最後將所有從第二個表中檢索出的記錄與第一個表中合適記錄進行合併.
例如: 表TAB1 16,384 條記錄
表TAB2 1 條記錄
選擇TAB2作為基礎表(最好的方法)
select count(*) from tab1,tab2 執行時間0.96秒
選擇TAB2作為基礎表(不佳的方法)
select count(*) from tab2,tab1 執行時間26.09秒
如果有3個以上的表連接查詢, 那就需要選擇交叉表(intersection table)作為基礎表, 交叉表是指那個被其他表所引用的表.
例如: EMP表描述了LOCA TION表和CATEGORY表的交集.
SELECT *
FROM LOCATION L ,
CA TEGORY C,
EMP E
WHERE E.EMP_NO BETWEEN 1000 AND 2000
AND E.CAT_NO = C.CAT_NO
AND E.LOCN = L.LOCN
將比下列SQL更有效率
SELECT *
FROM EMP E ,
LOCATION L ,
CA TEGORY C
WHERE E.CAT_NO = C.CAT_NO
AND E.LOCN = L.LOCN
AND E.EMP_NO BETWEEN 1000 AND 2000
5. WHERE子句中的連接順序．
ORACLE採用自下而上的順序解析WHERE子句,根據這個原理,表之間的連接必須寫在其他WHERE條件之前, 那些可以過濾掉最大數量記錄的條件必須寫在WHERE子句的末尾. 例如:
(低效,執行時間156.3秒)
SELECT …
FROM EMP E
WHERE SAL > 50000
AND JOB = …MANAGER?
AND 25 < (SELECT COUNT(*) FROM EMP
WHERE MGR=E.EMPNO);
(高效,執行時間10.6秒)
SELECT …
FROM EMP E
WHERE 25 < (SELECT COUNT(*) FROM EMP
WHERE MGR=E.EMPNO)
AND SAL > 50000
AND JOB = …MANAGER?;
6. SELECT子句中避免使用… * …
當你想在SELECT子句中列出所有的COLUMN時,使用動態SQL列引用…*? 是一個方便的方法.不幸的是,這是一個非常低效的方法. 實際上,ORACLE在解析的過程中, 會將?*? 依次轉換成所有的列名, 這個工作是通過查詢資料字典完成的, 這意味著將耗費更多的時間. 7. 減少訪問資料庫的次數
當執行每條SQL語句時, ORACLE在內部執行了許多工作: 解析SQL語句, 估算索引的利用率, 綁定變數, 讀資料塊等等. 由此可見, 減少訪問資料庫的次數, 就能實際上減少ORACLE的工作量.
例如,
以下有三種方法可以檢索出雇員號等於0342或0291的職員.
方法1 (最低效)
SELECT EMP_NAME , SALARY , GRADE
FROM EMP
WHERE EMP_NO = 342;
SELECT EMP_NAME , SALARY , GRADE
FROM EMP
WHERE EMP_NO = 291;
方法2 (次低效)
DECLARE
CURSOR C1 (E_NO NUMBER) IS
SELECT EMP_NAME,SALARY,GRADE
FROM EMP
WHERE EMP_NO = E_NO;
BEGIN
OPEN C1(342);
FETCH C1 INTO …,..,.. ;
OPEN C1(291);
FETCH C1 INTO …,..,.. ;
CLOSE C1;
END;
方法3 (高效)
SELECT A.EMP_NAME , A.SALARY , A.GRADE,
B.EMP_NAME , B.SALARY , B.GRADE
FROM EMP A,EMP B
WHERE A.EMP_NO = 342
AND B.EMP_NO = 291;
注意:
在SQL*Plus , SQL*Forms和Pro*C中重新設置ARRAYSIZE參數, 可以增加每次資料庫訪問的檢索資料量,建議值為200.
8. 使用DECODE函數來減少處理時間
使用DECODE函數可以避免重複掃描相同記錄或重複連接相同的表.
例如:
SELECT COUNT(*)，SUM(SAL)
FROM EMP
WHERE DEPT_NO = 0020
AND ENAME LIKE…SMITH%?;
SELECT COUNT(*)，SUM(SAL)
FROM EMP
WHERE DEPT_NO = 0030
AND ENAME LIKE…SMITH%?;
你可以用DECODE函數高效地得到相同結果
SELECT COUNT(DECODE(DEPT_NO,0020,?X?,NULL)) D0020_COUNT,
COUNT(DECODE(DEPT_NO,0030,?X?,NULL)) D0030_COUNT,
SUM(DECODE(DEPT_NO,0020,SAL,NULL)) D0020_SAL,
SUM(DECODE(DEPT_NO,0030,SAL,NULL)) D0030_SAL
FROM EMP WHERE ENAME LIKE …SMITH%?;
類似的,DECODE函數也可以運用於GROUP BY 和ORDER BY子句中.
9. 整合簡單,無關聯的資料庫訪問
如果你有幾個簡單的資料庫查詢語句,你可以把它們整合到一個查詢中(即使它們之間沒有關係)
例如:
SELECT NAME
FROM EMP
WHERE EMP_NO = 1234;
SELECT NAME
FROM DPT
WHERE DPT_NO = 10 ;
SELECT NAME
FROM CAT
WHERE CAT_TYPE = …RD?;
上面的3個查詢可以被合併成一個:
SELECT https://www.doczj.com/doc/fb8478299.html, , https://www.doczj.com/doc/fb8478299.html, , https://www.doczj.com/doc/fb8478299.html,
FROM CAT C , DPT D , EMP E,DUAL X
WHERE NVL(…X?,X.DUMMY) = NVL(…X?,E.ROWID(+))
AND NVL(…X?,X.DUMMY) = NVL(…X?,D.ROWID(+))
AND NVL(…X?,X.DUMMY) = NVL(…X?,C.ROWID(+))
AND E.EMP_NO(+) = 1234
AND D.DEPT_NO(+) = 10
AND C.CAT_TYPE(+) = …RD?;
(譯者按: 雖然採取這種方法,效率得到提高,但是程式的可讀性大大降低,所以讀者還是要權衡之間的利弊)
10. 刪除重複記錄
最高效的刪除重複記錄方法( 因為使用了ROWID)
DELETE FROM EMP E
WHERE E.ROWID > (SELECT MIN(X.ROWID)
FROM EMP X
WHERE X.EMP_NO = E.EMP_NO);
11. 用TRUNCATE替代DELETE
當刪除表中的記錄時,在通常情況下, 回滾段(rollback segments ) 用來存放可以被恢復的資訊. 如果你沒有COMMIT事務,ORACLE會將資料恢復到刪除之前的狀態(準確地說是恢復到執行刪除命令之前的狀況)
而當運用TRUNCA TE時, 回滾段不再存放任何可被恢復的資訊.當命令運行後,資料不能被恢復.因此很少的資源被調用,執行時間也會很短.
(譯者按: TRUNCATE只在刪除全表適用,TRUNCATE是DDL不是DML)
12. 儘量多使用COMMIT
只要有可能,在程式中儘量多使用COMMIT, 這樣程式的性能得到提高,需求也會因為COMMIT所釋放的資源而減少:
COMMIT所釋放的資源:
a. 回滾段上用於恢復資料的資訊.
b. 被程式語句獲得的鎖
c. redo log buffer 中的空間
d. ORACLE為管理上述3種資源中的內部花費
(譯者按: 在使用COMMIT時必須要注意到事務的完整性,現實中效率和事務完整性往往是魚和熊掌不可得兼)
13. 計算記錄條數
和一般的觀點相反, count(*) 比count(1)稍快, 當然如果可以通過索引檢索,對索引列的計數仍舊是最快的. 例如COUNT(EMPNO)
(譯者按: 在CSDN論壇中,曾經對此有過相當熱烈的討論, 作者的觀點並不十分準確,通過實際的測詴,上述三種方法並沒有顯著的性能差別)
14. 用Where子句替換HA VING子句
避免使用HA VING子句, HA VING 只會在檢索出所有記錄之後才對結果集進行過濾. 這個處理需要排序,總計等操作. 如果能通過WHERE子句限制記錄的數目,那就能減少這方面的開銷.
例如:
低效:
SELECT REGION，A VG(LOG_SIZE)
FROM LOCATION
GROUP BY REGION
HA VING REGION REGION != …SYDNEY?
AND REGION != …PERTH?
高效
SELECT REGION，A VG(LOG_SIZE)
FROM LOCATION
WHERE REGION REGION != …SYDNEY?
AND REGION != …PERTH?
GROUP BY REGION
(譯者按: HA VING 中的條件一般用於對一些集合函數的比較,如COUNT() 等等. 除此而外,一般的條件應該寫在WHERE子句中)
15. 減少對表的查詢
在含有子查詢的SQL語句中,要特別注意減少對表的查詢.
例如:
低效
SELECT TAB_NAME
FROM TABLES
WHERE TAB_NAME = ( SELECT TAB_NAME
FROM TAB_COLUMNS
WHERE VERSION = 604)
AND DB_VER= ( SELECT DB_VER
FROM TAB_COLUMNS
WHERE VERSION = 604)
高效
SELECT TAB_NAME
FROM TABLES
WHERE (TAB_NAME,DB_VER)
= ( SELECT TAB_NAME,DB_VER)
FROM TAB_COLUMNS
WHERE VERSION = 604)
Update 多個Column 例子:
低效:
UPDATE EMP
SET EMP_CAT = (SELECT MAX(CATEGORY) FROM EMP_CA TEGORIES),
SAL_RANGE = (SELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES)
WHERE EMP_DEPT = 0020;
高效:
UPDATE EMP
SET (EMP_CA T, SAL_RANGE)
= (SELECT MAX(CATEGORY) , MAX(SAL_RANGE)
FROM EMP_CATEGORIES)
WHERE EMP_DEPT = 0020;
16. 通過內部函數提高SQL效率.
SELECT H.EMPNO,E.ENAME,H.HIST_TYPE,T.TYPE_DESC,COUNT(*)
FROM HISTORY_TYPE T,EMP E,EMP_HISTORY H
WHERE H.EMPNO = E.EMPNO
AND H.HIST_TYPE = T.HIST_TYPE
GROUP BY H.EMPNO,E.ENAME,H.HIST_TYPE,T.TYPE_DESC;
通過調用下面的函數可以提高效率.
FUNCTION LOOKUP_HIST_TYPE(TYP IN NUMBER) RETURN V ARCHAR2
AS
TDESC V ARCHAR2(30);
CURSOR C1 IS
SELECT TYPE_DESC
FROM HISTORY_TYPE
WHERE HIST_TYPE = TYP;
BEGIN
OPEN C1;
FETCH C1 INTO TDESC;
CLOSE C1;
RETURN (NVL(TDESC,???));
END;
FUNCTION LOOKUP_EMP(EMP IN NUMBER) RETURN V ARCHAR2
AS
ENAME V ARCHAR2(30);
CURSOR C1 IS
SELECT ENAME
FROM EMP
WHERE EMPNO=EMP;
BEGIN
OPEN C1;
FETCH C1 INTO ENAME;
CLOSE C1;
RETURN (NVL(ENAME,???));
END;
SELECT H.EMPNO,LOOKUP_EMP(H.EMPNO),
H.HIST_TYPE,LOOKUP_HIST_TYPE(H.HIST_TYPE),COUNT(*)
FROM EMP_HISTORY H
GROUP BY H.EMPNO , H.HIST_TYPE;
(譯者按: 經常在論壇中看到如?能不能用一個SQL寫出….? 的貼子, 殊不知複雜的SQL往往犧牲了執行效率. 能夠掌握上面的運用函數解決問題的方法在實際工作中是非常有意義的)
17. 使用表的別名(Alias)
當在SQL語句中連接多個表時, 請使用表的別名並把別名首碼於每個Column上.這樣一來,就可以減少解析的時間並減少那些由Column歧義引起的語法錯誤.
(譯者注: Column歧義指的是由於SQL中不同的表具有相同的Column名,當SQL語句中出現這個Column時,SQL解析器無法判斷這個Column的歸屬)
18. 用EXISTS替代IN
在許多基於基礎表的查詢中,為了滿足一個條件,往往需要對另一個表進行聯接.在這種情況下, 使用EXISTS(或NOT EXISTS)通常將提高查詢的效率.
低效:
SELECT *
FROM EMP (基礎表)
WHERE EMPNO > 0
AND DEPTNO IN (SELECT DEPTNO
FROM DEPT
WHERE LOC = …MELB?)
高效:
SELECT *
FROM EMP (基礎表)
WHERE EMPNO > 0
AND EXISTS (SELECT …X?
FROM DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
AND LOC = …MELB?)
(譯者按: 相對來說,用NOT EXISTS替換NOT IN 將更顯著地提高效率,下一節中將指出) 19. 用NOT EXISTS替代NOT IN
在子查詢中,NOT IN子句將執行一個內部的排序和合併. 無論在哪種情況下,NOT IN都是最低效的(因為它對子查詢中的表執行了一個全表遍曆). 為了避免使用NOT IN ,我們可以把它改寫成外連接(Outer Joins)或NOT EXISTS.
例如:
SELECT …
FROM EMP
WHERE DEPT_NO NOT IN (SELECT DEPT_NO
FROM DEPT
WHERE DEPT_CA T=?A?);
為了提高效率.改寫為:
(方法一: 高效)
SELECT ….
FROM EMP A,DEPT B
WHERE A.DEPT_NO = B.DEPT(+)
AND B.DEPT_NO IS NULL
AND B.DEPT_CAT(+) = …A?
(方法二: 最高效)
SELECT ….
FROM EMP E
WHERE NOT EXISTS (SELECT …X?
FROM DEPT D
WHERE D.DEPT_NO = E.DEPT_NO
AND DEPT_CAT = …A?);
20. 用表連接替換EXISTS
通常來說, 採用表連接的方式比EXISTS更有效率
SELECT ENAME
FROM EMP E
WHERE EXISTS (SELECT …X?
FROM DEPT
WHERE DEPT_NO = E.DEPT_NO
AND DEPT_CAT = …A?);
(更高效)
SELECT ENAME
FROM DEPT D,EMP E
WHERE E.DEPT_NO = D.DEPT_NO
AND DEPT_CA T = …A? ;
(譯者按: 在RBO的情況下,前者的執行路徑包括FILTER,後者使用NESTED LOOP)
21. 用EXISTS替換DISTINCT
當提交一個包含一對多表資訊(比如部門表和雇員表)的查詢時,避免在SELECT子句中使用DISTINCT. 一般可以考慮用EXIST替換
例如:
低效:
SELECT DISTINCT DEPT_NO,DEPT_NAME
FROM DEPT D,EMP E
WHERE D.DEPT_NO = E.DEPT_NO
高效:
SELECT DEPT_NO,DEPT_NAME
FROM DEPT D
WHERE EXISTS ( SELECT …X?
FROM EMP E
WHERE E.DEPT_NO = D.DEPT_NO);
EXISTS 使查詢更為迅速,因為RDBMS核心模組將在子查詢的條件一旦滿足後,立刻返回結果.
22. 識別?低效執行?的SQL語句
用下列SQL工具找出低效SQL:
SELECT EXECUTIONS , DISK_READS, BUFFER_GETS,
ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,
ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,
SQL_TEXT
FROM V$SQLAREA
WHERE EXECUTIONS>0
AND BUFFER_GETS > 0
AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8
ORDER BY 4 DESC;
(譯者按: 雖然目前各種關於SQL優化的圖形化工具層出不窮,但是寫出自己的SQL工具來解決問題始終是一個最好的方法)
23. 使用TKPROF 工具來查詢SQL性能狀態
SQL trace 工具收集正在執行的SQL的性能狀態資料並記錄到一個跟蹤檔中. 這個跟蹤檔提供了許多有用的資訊,例如解析次數.執行次數,CPU使用時間等.這些資料將可以用來優化
你的系統.
設置SQL TRACE在會話級別: 有效
ALTER SESSION SET SQL_TRACE TRUE
設置SQL TRACE 在整個資料庫有效仿, 你必須將SQL_TRACE參數在init.ora中設為TRUE, USER_DUMP_DEST參數說明了生成跟蹤檔的目錄
(譯者按: 這一節中,作者並沒有提到TKPROF的用法, 對SQL TRACE的用法也不夠準確, 設置SQL TRACE首先要在init.ora中設定TIMED_STATISTICS, 這樣才能得到那些重要的時間狀態. 生成的trace檔是不可讀的,所以要用TKPROF工具對其進行轉換,TKPROF有許多執行參數. 大家可以參考ORACLE手冊來瞭解具體的配置. )
24. 用EXPLAIN PLAN 分析SQL語句
EXPLAIN PLAN 是一個很好的分析SQL語句的工具,它甚至可以在不執行SQL的情況下分析語句. 通過分析,我們就可以知道ORACLE是怎麼樣連接表,使用什麼方式掃描表(索引掃描或全表掃描)以及使用到的索引名稱.
你需要按照從裏到外,從上到下的次序解讀分析的結果. EXPLAIN PLAN分析的結果是用縮進的格式排列的, 最內部的操作將被最先解讀, 如果兩個操作處於同一層中,帶有最小操作
號的將被首先執行.
NESTED LOOP是少數不按照上述規則處理的操作, 正確的執行路徑是檢查對NESTED LOOP提供資料的操作,其中操作號最小的將被最先處理.
譯者按:
通過實踐, 感到還是用SQLPLUS中的SET TRACE 功能比較方便.
舉例:
SQL> list
1 SELECT *
2 FROM dept, emp
3* WHERE emp.deptno = dept.deptno
SQL> set autotrace traceonly /*traceonly 可以不顯示執行結果*/
SQL> /
14 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STA TEMENT Optimizer=CHOOSE
1 0 NESTED LOOPS
2 1 TABLE ACCESS (FULL) OF 'EMP'
3 1 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
4 3 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
2 db block gets
30 consistent gets
0 physical reads
0 redo size
2598 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
通過以上分析,可以得出實際的執行步驟是:
1. TABLE ACCESS (FULL) OF 'EMP'
2. INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)
3. TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
4. NESTED LOOPS (JOINING 1 AND 3)
注: 目前許多第三方的工具如TOAD和ORACLE本身提供的工具如OMS的SQL Analyze 都提供了極其方便的EXPLAIN PLAN工具.也許喜歡圖形化介面的朋友們可以選用它們. 25. 用索引提高效率
索引是表的一個概念部分,用來提高檢索資料的效率. 實際上,ORACLE使用了一個複雜的自平衡B-tree結構. 通常,通過索引查詢資料比全表掃描要快. 當ORACLE找出執行查詢和Update語句的最佳路徑時, ORACLE優化器將使用索引. 同樣在聯結多個表時使用索引也可以提高效率. 另一個使用索引的好處是,它提供了主鍵(primary key)的唯一性驗證.
除了那些LONG或LONG RAW資料類型, 你可以索引幾乎所有的列. 通常, 在大型表中使用索引特別有效. 當然,你也會發現, 在掃描小表時,使用索引同樣能提高效率.
雖然使用索引能得到查詢效率的提高,但是我們也必須注意到它的代價. 索引需要空間來
存儲,也需要定期維護, 每當有記錄在表中增減或索引列被修改時, 索引本身也會被修改. 這意味著每條記錄的INSERT , DELETE , UPDATE將為此多付出4 , 5 次的磁片I/O . 因為索引需要額外的存儲空間和處理,那些不必要的索引反而會使查詢反應時間變慢.
譯者按:
定期的重構索引是有必要的.
ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>
26. 索引的操作
ORACLE對索引有兩種訪問模式.
索引唯一掃描( INDEX UNIQUE SCAN)
大多數情況下, 優化器通過WHERE子句訪問INDEX.
例如:
表LODGING有兩個索引: 建立在LODGING列上的唯一性索引LODGING_PK和建立在MANAGER列上的非唯一性索引LODGING$MANAGER.
SELECT *
FROM LODGING
WHERE LODGING = …ROSE HILL?;
在內部, 上述SQL將被分成兩步執行, 首先, LODGING_PK 索引將通過索引唯一掃描的方式被訪問, 獲得相對應的ROWID, 通過ROWID訪問表的方式執行下一步檢索.
如果被檢索返回的列包括在INDEX列中,ORACLE將不執行第二步的處理(通過ROWID 訪問表). 因為檢索資料保存在索引中, 單單訪問索引就可以完全滿足查詢結果.
下麵SQL只需要INDEX UNIQUE SCAN 操作.
SELECT LODGING
FROM LODGING
WHERE LODGING = …ROSE HILL?;
索引範圍查詢(INDEX RANGE SCAN)
適用於兩種情況:
1. 基於一個範圍的檢索
2. 基於非唯一性索引的檢索
例1:
SELECT LODGING
FROM LODGING
WHERE LODGING LIKE …M%?;
WHERE子句條件包括一系列值, ORACLE將通過索引範圍查詢的方式查詢LODGING_PK . 由於索引範圍查詢將返回一組值, 它的效率就要比索引唯一掃描
低一些.
例2:
SELECT LODGING
FROM LODGING
WHERE MANAGER = …BILL GATES?;
這個SQL的執行分兩步, LODGING$MANAGER的索引範圍查詢(得到所有符合條件記錄的ROWID) 和下一步同過ROWID訪問表得到LODGING列的值. 由於
LODGING$MANAGER是一個非唯一性的索引,資料庫不能對它執行索引唯一掃描.
由於SQL返回LODGING列,而它並不存在於LODGING$MANAGER索引中, 所以在索引範圍查詢後會執行一個通過ROWID訪問表的操作.
WHERE子句中, 如果索引列所對應的值的第一個字元由通配符(WILDCARD)開始, 索引將不被採用.
SELECT LODGING
FROM LODGING
WHERE MANAGER LIKE …％HANMAN?;
在這種情況下，ORACLE將使用全表掃描.
27. 基礎表的選擇
基礎表(Driving Table)是指被最先訪問的表(通常以全表掃描的方式被訪問). 根據優化器的不同, SQL語句中基礎表的選擇是不一樣的.
如果你使用的是CBO (COST BASED OPTIMIZER),優化器會檢查SQL語句中的每個表的物理大小,索引的狀態,然後選用花費最低的執行路徑.
如果你用RBO (RULE BASED OPTIMIZER) , 並且所有的連接條件都有索引對應, 在這種情況下, 基礎表就是FROM 子句中列在最後的那個表.
舉例:
SELECT https://www.doczj.com/doc/fb8478299.html, , B.MANAGER
FROM WORKER A,
LODGING B
WHERE A.LODGING = B.LODING;
由於LODGING表的LODING列上有一個索引, 而且WORKER表中沒有相比較的索引, WORKER表將被作為查詢中的基礎表.
28. 多個平等的索引
當SQL語句的執行路徑可以使用分佈在多個表上的多個索引時, ORACLE會同時使用多個索引並在運行時對它們的記錄進行合併, 檢索出僅對全部索引有效的記錄.
在ORACLE選擇執行路徑時,唯一性索引的等級高於非唯一性索引. 然而這個規則只有
當WHERE子句中索引列和常量比較才有效.如果索引列和其他表的索引類相比較. 這種子句在優化器中的等級是非常低的.
如果不同表中兩個想同等級的索引將被引用, FROM子句中表的順序將決定哪個會被率先使用. FROM子句中最後的表的索引將有最高的優先順序.
如果相同表中兩個想同等級的索引將被引用, WHERE子句中最先被引用的索引將有最高的優先順序.
舉例:
DEPTNO上有一個非唯一性索引,EMP_CA T也有一個非唯一性索引.
SELECT ENAME,
FROM EMP
WHERE DEPT_NO = 20
AND EMP_CAT = …A?;
這裏,DEPTNO索引將被最先檢索,然後同EMP_CAT索引檢索出的記錄進行合併. 執行路徑如下:
TABLE ACCESS BY ROWID ON EMP
AND-EQUAL
INDEX RANGE SCAN ON DEPT_IDX
INDEX RANGE SCAN ON CAT_IDX
29. 等式比較和範圍比較
當WHERE子句中有索引列, ORACLE不能合併它們,ORACLE將用範圍比較.
舉例:
DEPTNO上有一個非唯一性索引,EMP_CAT也有一個非唯一性索引.
SELECT ENAME
FROM EMP
WHERE DEPTNO > 20
AND EMP_CAT = …A?;
這裏只有EMP_CAT索引被用到,然後所有的記錄將逐條與DEPTNO條件進行比較. 執行路徑如下:
TABLE ACCESS BY ROWID ON EMP
INDEX RANGE SCAN ON CA T_IDX
30. 不明確的索引等級
當ORACLE無法判斷索引的等級高低差別,優化器將只使用一個索引,它就是在WHERE子句中被列在最前面的.
舉例:
DEPTNO上有一個非唯一性索引,EMP_CA T也有一個非唯一性索引.
SELECT ENAME
FROM EMP
WHERE DEPTNO > 20
AND EMP_CAT > …A?;
這裏, ORACLE只用到了DEPT_NO索引. 執行路徑如下:
TABLE ACCESS BY ROWID ON EMP
INDEX RANGE SCAN ON DEPT_IDX
譯者按:
我們來詴一下以下這種情況:
SQL> select index_name, uniqueness from user_indexes where table_name = 'EMP';
INDEX_NAME UNIQUENES
------------------------------ ---------
EMPNO UNIQUE
EMPTYPE NONUNIQUE
SQL> select * from emp where empno >= 2 and emp_type = 'A' ;
no rows selected
Execution Plan
----------------------------------------------------------
0 SELECT STA TEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
2 1 INDEX (RANGE SCAN) OF 'EMPTYPE' (NON-UNIQUE)
雖然EMPNO是唯一性索引,但是由於它所做的是範圍比較, 等級要比非唯一性索引的等式比較低!
31. 強制索引失效
如果兩個或以上索引具有相同的等級,你可以強制命令ORACLE優化器使用其中的一個(通過它,檢索出的記錄數量少) .
舉例:
SELECT ENAME
FROM EMP
WHERE EMPNO = 7935
AND DEPTNO + 0 = 10 /*DEPTNO上的索引將失效*/
AND EMP_TYPE || …? = …A? /*EMP_TYPE上的索引將失效*/
這是一種相當直接的提高查詢效率的辦法. 但是你必須謹慎考慮這種策略,一般來說,只有在你希望單獨優化幾個SQL時才能採用它.
這裏有一個例子關於何時採用這種策略,
假設在EMP表的EMP_TYPE列上有一個非唯一性的索引而EMP_CLASS上沒有索引.
SELECT ENAME
FROM EMP
WHERE EMP_TYPE = …A?
AND EMP_CLASS = …X?;
優化器會注意到EMP_TYPE上的索引並使用它. 這是目前唯一的選擇. 如果,一段時間以後, 另一個非唯一性建立在EMP_CLASS上,優化器必須對兩個索引進行選擇,在通常情況下,優化器將使用兩個索引並在他們的結果集合上執行排序及合併. 然而,如果其中一個索引（EMP_TYPE）接近於唯一性而另一個索引（EMP_CLASS）上有幾千個重複的值. 排序及合併就會成為一種不必要的負擔. 在這種情況下,你希望使優化器遮罩掉EMP_CLASS索引. 用下面的方案就可以解決問題.
SELECT ENAME
FROM EMP
WHERE EMP_TYPE = …A?
AND EMP_CLASS||?? = …X?;
32. 避免在索引列上使用計算．
WHERE子句中，如果索引列是函數的一部分．優化器將不使用索引而使用全表掃描．
舉例:
低效：
SELECT …
FROM DEPT
WHERE SAL * 12 > 25000;
高效:
SELECT …
FROM DEPT
WHERE SAL > 25000/12;
譯者按:
這是一個非常實用的規則，請務必牢記
33. 自動選擇索引
如果表中有兩個以上（包括兩個）索引，其中有一個唯一性索引，而其他是非唯一性．在這種情況下，ORACLE將使用唯一性索引而完全忽略非唯一性索引．
舉例:
SELECT ENAME
FROM EMP
WHERE EMPNO = 2326
AND DEPTNO = 20 ;
這裏，只有EMPNO上的索引是唯一性的，所以EMPNO索引將用來檢索記錄．TABLE ACCESS BY ROWID ON EMP
INDEX UNIQUE SCAN ON EMP_NO_IDX
34. 避免在索引列上使用NOT
通常，我們要避免在索引列上使用NOT, NOT會產生在和在索引列上使用函數相同的影響. 當ORACLE”遇到”NOT,他就會停止使用索引轉而執行全表掃描.
舉例:
低效: (這裏,不使用索引)
SELECT …
FROM DEPT
WHERE DEPT_CODE NOT = 0;
高效: (這裏,使用了索引)
SELECT …
FROM DEPT
WHERE DEPT_CODE > 0;
需要注意的是,在某些時候, ORACLE優化器會自動將NOT轉化成相對應的關係操作符.
NOT > to <=
NOT >= to <
NOT < to >=
NOT <= to >
譯者按:
在這個例子中,作者犯了一些錯誤. 例子中的低效率SQL是不能被執行的.
我做了一些測詴:
SQL> select * from emp where NOT empno > 1;
no rows selected
Execution Plan
----------------------------------------------------------
0 SELECT STA TEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
2 1 INDEX (RANGE SCAN) OF 'EMPNO' (UNIQUE)
SQL> select * from emp where empno <= 1;
no rows selected
Execution Plan
----------------------------------------------------------
0 SELECT STA TEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
2 1 INDEX (RANGE SCAN) OF 'EMPNO' (UNIQUE)
兩者的效率完全一樣，也許這符合作者關於” 在某些時候, ORACLE優化器會自動將
GreenPlumn的SQL语句查询优化 数据库查询预准备 1. VACUUM ?vacuum只是简单的回收空间且令其可以再次使用，没有请求排它锁，仍旧可以对表读写 ?vacuum full执行更广泛的处理，包括跨块移动行，以便把表压缩至使用最少的磁盘块数目存储。相对vacuum要慢，而且会请求排它锁。 ?定期执行：在日常维护中，需要对数据字典定期执行vacuum，可以每天在数据库空闲的时候进行。然后每隔一段较长时间（两三个月）对系统表执行一次vacuum full，这个操作需要停机，比较耗时，大表可能耗时几个小时。 ?reindex:执行vacuum之后,最好对表上的索引进行重建 2. ANALYZE ?命令：analyze [talbe [(column,..)]] ?收集表内容的统计信息，以优化执行计划。如创建索引后，执行此命令，对于随即查询将会利用索引。 ?自动统计信息收集 ?在postgresql.conf中有控制自动收集的参数gp_autostats_mode设置，gp_autostats_mode三个值：none、no_change、on_no_stats（默认） o none：禁止收集统计信息 o on change：当一条DML执行后影响的行数超过 gp_autostats_on_change_threshold参数指定的值时，会执行完这条DML后再 自动执行一个analyze 的操作来收集表的统计信息。 o no_no_stats：当使用create talbe as select 、insert 、copy时，如果在目标表中没有收集过统计信息，那么会自动执行analyze 来收集这张表的信息。gp 默认使用on_no_stats，对数据库的消耗比较小，但是对于不断变更的表，数 据库在第一次收集统计信息之后就不会再收集了。需要人为定时执行 analyze.
setserveroutput on; --计算两个整数的和与这两个整数的差的商 declare aint:=100; bint:=200; c number; begin c:=(a+b)/(a-a); dbms_output.put_line(c); exception whenzero_divide then dbms_output.put_line('除数不能为零!'); end; / declare Num_sal number; --声明一个数值变量 Var_ename varchar2(20); --声明一个字符串变量 begin select ename,sal into Var_ename,Num_sal from scott.emp where empno=7369; --检索指定的值并保存在变量中 dbms_output.put_line(Var_ename||'的工资是'||Num_sal); --输出变量的值end; / --简单的插入一条语句 create or replace procedure pro1 is begin insert into scott.emp(empno,ename)values(1111,'1111'); end; / exec pro1; select * from scott.emp; --删除一条语句(传参) create procedure pro2(in_empno number) is begin delete from emp where empno=in_empno; end; /
--简单的插入一条语句(传参) create or replace procedure pro3(in_empnonumber,in_ename varchar2) is begin insert into scott.emp(empno,ename)values(in_empno,in_ename); end; / declare --定义变量的格式是变量名称变量的类型 v_enamevarchar2(8); begin select ename into v_ename from emp where empno=&empno; --将查询到的值存入v_ename变量中 --输出v_ename dbms_output.put_line('雇员名是'||v_ename); end; / --将上面的块改成过程 create procedure pro4(v_in_empno number) is v_enamevarchar2(8); begin selectename into v_ename from emp where empno=v_in_empno; dbms_output.put_line('雇员名是'||v_ename); end; / --编写一个过程,实现输入雇员名,新工资可以修改雇员的工资 create procedure pro5(in_ename in varchar2,in_newsal in number) is
plsql安装和配置连接教程 总体步骤：先安装oracle client端，然后安装plsql，配置tnsname.ora 一：安装oracle client端 下载地址： 1.加压文件，安装oracle客户端 打开安装包，找到setup.exe，开始安装。报错,具体原因和解决办法和安装oracle服务端方法一样。只是要多修改一个文件，在两个文件里添加同样的内容即可。 解决办法很简单，这是因为版本注册问题，默认oracle 11没有添加win10的注册信息，所以要讲win10的注册信息添加到oracle的配置文件里。打开oracle安装包，找到stage文件夹，找到cvu_prereq.xml文件，用记事本打开可以看到如下内容，在
上面报错信息修改完成以后，重新setup.ext,下面界面选择“管理员”模式 安装以后在network\ADMIN文件夹中配置tnsnames.ora文件，如图：
如果client文件夹中没有network文件夹，说明安装client时安装类型没选对，这时也可以从instantclient包中，把network文件夹拷入client的目录下。 此时，基础环境已经配置完毕。 二：安装plsqldevlop 安装完毕后，不要输入账号密码先进入工具 点tools—preferences—输入client端的目录地址和oci.dll文件的地址 三：配置系统环境 右击我的电脑—属性—高级系统设置—环境变量 编辑Path，添加client端地址
说明：复制表(只复制结构,源表名：a 新表名：b) SQL: select * into b from a where 1<>1 说明：拷贝表(拷贝数据,源表名：a 目标表名：b) SQL: insert into b(a, b, c) select d,e,f from b; 说明：显示文章、提交人和最后回复时间 SQL: select a.title,https://www.doczj.com/doc/fb8478299.html,ername,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b 说明：外连接查询(表名1：a 表名2：b) SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c 说明：日程安排提前五分钟提醒 SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 说明：两张关联表，删除主表中已经在副表中没有的信息 SQL: delete from info where not exists ( select * from infobz where info.infid=infobz.infid ) 说明：-- SQL: SELECT A.NUM, https://www.doczj.com/doc/fb8478299.html,, B.UPD_DATE, B.PREV_UPD_DATE FROM TABLE1, (SELECT X.NUM, X.UPD_DATE, Y.UPD_DATE PREV_UPD_DATE FROM (SELECT NUM, UPD_DATE, INBOUND_QTY, STOCK_ONHAND FROM TABLE2 WHERE TO_CHAR(UPD_DATE,'YYYY/MM') = TO_CHAR(SYSDATE, 'YYYY/MM')) X, (SELECT NUM, UPD_DATE, STOCK_ONHAND FROM TABLE2 WHERE TO_CHAR(UPD_DATE,'YYYY/MM') = TO_CHAR(TO_DATE(TO_CHAR(SYSDATE, 'YYYY/MM') ¦¦ '/01','YYYY/MM/DD') - 1, 'YYYY/MM') ) Y, WHERE X.NUM = Y.NUM （+） AND X.INBOUND_QTY + NVL(Y.STOCK_ONHAND,0) <> X.STOCK_ONHAND ) B WHERE A.NUM = B.NUM 说明：-- SQL: select * from studentinfo where not exists(select * from student where studentinfo.id=student.id) and 系名称='"&strdepartmentname&"' and 专业名称='"&strprofessionname&"' order by 性别,生源地,高考总成绩 说明：从数据库中去一年的各单位电话费统计(电话费定额贺电化肥清单两个表来源） SQL: SELECT https://www.doczj.com/doc/fb8478299.html,erper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy') AS telyear, 说明：四表联查问题： SQL: select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where ..... 说明：得到表中最小的未使用的ID号 SQL:
ORACLE 数据库性能优化 参考书目： 《ORACLE 9i Database Performance Tuning Guide and Reference》《ORACLE 9i Database Reference》 《ORACLE 9i SQL Reference》 《ORACLE 9i Database Administrator’s Guide》
一、数据库实例创建过程参数确定 在创建数据库实例过程中，需要确定以下几个参数： 1. 数据块大小（DB_BLOCK_SIZE） 该参数指明了ORACLE所处理的数据存贮于数据文档以及SGA内存中的数据块大小。 该参数的可选择的范围为:4k,8k,16k,32k,64k。对于OLTP系统而言，取值可以为4K或8K，对于DSS系统而言，则可以取较大的数据，如32K或64K 建议统一取8K（即8192） 说明 DB_BLOCK_SIZE的大小将影响创建表时的EXTENT的大小。例如指定db_block_size=16K,某表空间的EXTENT MANAGEMENT 为local autoallocate，则其系统将extent的大小最小指定为1M.所以将可能导致空间的浪费。 2. 字符集(Character set) 该参数确定数据库以何种字符集来存贮CHAR以及V ARCHAR、V ARCHAR2等字符类型的值。对于ORACLE数据字典中的字符（如表及字段的COMMENT 内容）具有同样的作用。因此需要考虑如字符集的使用。对于国际项目，因为数据库中的comment内容（包括表及字符、存贮过程中的中文字符等内容）可能性需要以中文存贮，而用户业务数据使用的字符可能性是使用本地的语言，基于此，该参数需要选择支持UNICODE的字符编码的字符集。目前ORACLE9i支持以下二种UNICODE字符集： ?UTF8 ?AL32UTF8 建议统一取AL32UTF8
SQL Server 系统性能调优解决方案 前言 近几年,医药流通市场经历了激烈的震荡,导致行业逐步成熟和企业的快速变革,差异化经营成为众多医药流通的竞争选择。时空产品在中国医药流通企业的发展过程中得到了广泛且深入应用,大量的客户化开发和定制支撑了企业管理中横向和纵向的变化，很好的适应了企业在发展过程中不断变化的需求。 对于数据库管理系统的使用，很多用户都面临着一个很棘手的问题：系统效率下降。产生效率下降的因素是多方面： 1.硬件问题 2.软件问题 3.实施问题 正因为产生效率下降的因素很多，所以如何去查找原因成为我们首要关注的问题，时空公司也处在积极探索过程中。时空公司在解决一些客户问题的过程中积累了一些方法和思路，归纳总结后呈现给体系内的技术人员，本方案就系统效率调整所必需的基础知识、方法、技巧等几个方面进行阐述，从而让技术人员能够快速定位问题，解决问题，为合作伙伴提供优质，快捷的服务。 索引简介 索引是根据数据库表中一个或多个列的值进行排序的结构。索引提供指针以指向存储在表中指定列的数据值，然后根据指定的排序次序排列这些指针。数据库使用索引的方式与使用书的目录很相似，通过搜索索引找到特定的值，然后跟随指针到达包含该值的行。 索引键：用于创建索引的列。 索引类型 ?聚集索引： 聚集索引基于数据行的键值在表内排序和存储这些数据行。由于数据行按基于聚集索引键的排序次序存储，因此聚集索引对查找行很有效。每个表只能有一个聚集索引，因为数据行本身只能按一个顺序存储。数据行本身构成聚集索引的最低级别（叶子节点）。只有当表包含聚集索引时，表内的数据行才按排序次序存储。如果表没有聚集索引，则其数据行按堆集方式存储。 聚集索引对于那些经常要搜索范围值的列特别有效。使用聚集索引找到包含第一个值的行后，便可以确保包含后续索引值的行在物理相邻。例如：如果应用程序执行的一个查询经常检索某一日期范围内的记录，则使用聚集索引可以迅速找到包含开始日期的行，然后检索表中所有相邻的行，直到到达结束日期。这样有助于提高此类查询的性能。同样，如果对从表中检索的数据进行排序时经常要用到某一列，则可以将该表在该列上聚集（物理排序），避免每次查询该列时都进行排序，从而节省成本。 ?非聚集索引 非聚集索引具有完全独立于数据行的结构。非聚集索引的最低行包含非聚集索引的键值，并且每个键值项都有指针指向包含该键值的数据行。数据行不按基于非聚集键的次序存储。如
PLSQL语法介绍(有例子带注释) 关键字: oracle/plsql/游标/存储过程/触发器 --最简单的语句块 set serveroutput on; //用于输出显示 begin dbms_output.put_line('HeloWorld'); end; --一个简单的PL/SQL语句块 declare //声明变量，必须 v_ 开头 v_name varchar2(20); begin v_name := 'myname'; //变量的赋值格式 dbms_output.put_line(v_name); end; --语句块的组成 declare v_num number := 0; begin v_num := 2/v_num; dbms_output.put_line(v_num); exception //如果没有这部分，当出现异常的时候，就执行过不去 when others then dbms_output.put_line('error'); end; --变量声明的规则 1): 变量名不能够使用保留字，如from、select等 2): 第一个字符必须是字母 3): 变量名最多包含30个字符 4): 不要与数据库的表或者列同名 5): 每一行只能声明一个变量 --常用变量类型 1): binary_integer: 整数，主要用来计数而不是用来表示字段类型 2): number: 数字类型 3): char: 定长字符串 4): varchar2: 变长字符串 5): date: 日期 6): long: 长字符串，最长2GB 7): boolean: 布尔类型，可以取值为 true、false和null --变量声明，可以使用 %type 属性 declare v_empno number(4); v_empno2 emp.empno%type;//表示该变量的类型和emp表中的empno字
1.ORACLE的优化器共有3种 A、RULE (基于规则) b、COST (基于成本) c、CHOOSE (选择性) 设置缺省的优化器，可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明，如RULE，COST，CHOOSE，ALL_ROWS，FIRST_ROWS 。你当然也在SQL句级或是会话(session)级对其进行覆盖。 为了使用基于成本的优化器(CBO，Cost-Based Optimizer) ，你必须经常运行analyze 命令，以增加数据库中的对象统计信息(object statistics)的准确性。 如果数据库的优化器模式设置为选择性(CHOOSE)，那么实际的优化器模式将和是否运行过analyze 命令有关。如果table已经被analyze过，优化器模式将自动成为CBO ，反之，数据库将采用RULE 形式的优化器。 在缺省情况下，ORACLE采用CHOOSE优化器，为了避免那些不必要的全表扫描(full table scan) ，你必须尽量避免使用CHOOSE优化器，而直接采用基于规则或者基于成本的优化器。 2.访问Table的方式 ORACLE 采用两种访问表中记录的方式： A、全表扫描 全表扫描就是顺序地访问表中每条记录。ORACLE采用一次读入多个数据块(database block)的方式优化全表扫描。 B、通过ROWID访问表 你可以采用基于ROWID的访问方式情况，提高访问表的效率，ROWID包含了表中记录的物理位置信息。ORACLE采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系。通常索引提供了快速访问ROWID的方法，因此那些基于索引列的查询就可以得到性能上的提高。 3.共享SQL语句 为了不重复解析相同的SQL语句，在第一次解析之后，ORACLE将SQL语句存放在内存中。这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的内存可以被所有的数据库用户共享。因此，当你执行一个SQL语句(有时被称为一个游标)时，如果它和之前的执行过的语句完全相同，ORACLE就能很快获得已经被解析的语句以及最好的执行路径。ORACLE的这个功能大大地提高了SQL 的执行性能并节省了内存的使用。 可惜的是ORACLE只对简单的表提供高速缓冲(cache buffering)，这个功能并不适用于多表连接查询。 数据库管理员必须在init.ora中为这个区域设置合适的参数，当这个内存区域越大，就可以保留更多的语句，当然被共享的可能性也就越大了。 当你向ORACLE提交一个SQL语句，ORACLE会首先在这块内存中查找相同的语句。这里需要注明的是，ORACLE对两者采取的是一种严格匹配，要达成共享，SQL语句必须完全相同(包括空格，换行等)。 数据库管理员必须在init.ora中为这个区域设置合适的参数，当这个内存区域越大，就可以保留更多的语句，当然被共享的可能性也就越大了。 共享的语句必须满足三个条件： A、字符级的比较：当前被执行的语句和共享池中的语句必须完全相同。 B、两个语句所指的对象必须完全相同： C、两个SQL语句中必须使用相同的名字的绑定变量(bind variables)。 4.选择最有效率的表名顺序(只在基于规则的优化器中有效) ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，因此FROM子句中写在最后的表(基础表driving table)将被最先处理。在FROM子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。当ORACLE处理多个表时，会运用排序及合并的方式连接它们。首先，扫描第一个表(FROM子句中最后的那个表)并对记录进行派序，然后扫描第二个表(FROM子句中最后第二个表)，最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并。 如果有3个以上的表连接查询，那就需要选择交叉表(intersection table)作为基础表，交叉表是指
Sql优化方案 一．数据库优化技术 1.索引（强烈建议使用） 1.1优点 创建索引可以大大提高系统的性能。 第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。 第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。 第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。 第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。 第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。 1.2 缺点 第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。 第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。 1.3 使用准则 索引是建立在数据库表中的某些列的上面。因此，在创建索引的时候，应该仔细考虑在哪些列上可以创建索引，在哪些列上不能创建索引。 一般来说，应该在这些列上创建索引。 第一，在经常需要搜索的列上，可以加快搜索的速度；
第二，在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构； 第三，在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；第四，在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的； 第五，在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间； 第六，在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。 同样，对于有些列不应该创建索引。一般来说，不应该创建索引的的这些列具有下列特点： 第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。 第二，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。 第三，对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。 第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。 1.4 总结 1）索引提高了数据库的检索性能，但一定程度上牺牲了修改性能。因此适用于“多查询少修改”（insert，update，delete）的表。 2）对此类表中的外键，需要分组，排序或作为检索条件的字段建立索引 3）对此类表中查询使用少，字段取值少，字段数据量大的不应创建索引
一、简单查询 简单的Transact-SQL查询只包括选择列表、FROM子句和WHERE子句。它们分别说明所查询列、查询的 表或视图、以及搜索条件等。 例如，下面的语句查询testtable表中姓名为“张三”的nickname字段和email字段。SELECT nickname,email FROM testtable WHERE name='张三' (一) 选择列表 选择列表(select_list)指出所查询列，它可以是一组列名列表、星号、表达式、变量(包括局部变 量和全局变量)等构成。 1、选择所有列 例如，下面语句显示testtable表中所有列的数据： SELECT * FROM testtable 2、选择部分列并指定它们的显示次序 查询结果集合中数据的排列顺序与选择列表中所指定的列名排列顺序相同。 例如： SELECT nickname,email FROM testtable 3、更改列标题 在选择列表中，可重新指定列标题。定义格式为： 列标题=列名 列名列标题 如果指定的列标题不是标准的标识符格式时，应使用引号定界符，例如，下列语句使用汉字显示列 标题： SELECT 昵称=nickname,电子邮件=email FROM testtable 4、删除重复行 SELECT语句中使用ALL或DISTINCT选项来显示表中符合条件的所有行或删除其中重复的数据行，默认 为ALL。使用DISTINCT选项时，对于所有重复的数据行在SELECT返回的结果集合中只保留一行。 5、限制返回的行数 使用TOP n [PERCENT]选项限制返回的数据行数，TOP n说明返回n行，而TOP n PERCENT 时，说明n是
ORACLE的优化器共有3种 A、RULE (基于规则) b、COST (基于成本) c、CHOOSE (选择性) 设置缺省的优化器，可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明，如RULE，COST，CHOOSE，ALL_ROWS，FIRST_ROWS 。你当然也在SQL句级或是会话(session)级对其进行覆盖。 为了使用基于成本的优化器(CBO， Cost-Based Optimizer) ，你必须经常运行analyze 命令，以增加数据库中的对象统计信息(object statistics)的准确性。 如果数据库的优化器模式设置为选择性(CHOOSE)，那么实际的优化器模式将和是否运行过analyze命令有关。如果table已经被analyze过，优化器模式将自动成为CBO ，反之，数据库将采用RULE形式的优化器。 在缺省情况下，ORACLE采用CHOOSE优化器，为了避免那些不必要的全表扫描(full table scan) ，你必须尽量避免使用CHOOSE优化器，而直接采用基于规则或者基于成本的优化器。 2.访问Table的方式 ORACLE 采用两种访问表中记录的方式： A、全表扫描 全表扫描就是顺序地访问表中每条记录。ORACLE采用一次读入多个数据块(database block)的方式优化全表扫描。 B、通过ROWID访问表 你可以采用基于ROWID的访问方式情况，提高访问表的效率， ROWID 包含了表中记录的物理位置信息。ORACLE采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系。通常索引提供了快速访问ROWID的方法，因此那些基于索引列的查询就可以得到性能上的提高。 3.共享SQL语句 为了不重复解析相同的SQL语句，在第一次解析之后，ORACLE将SQL语句存放在存中。这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的存可以被所有的数据库用户共享。因此，当你执行一个SQL语句(有时被称为一个游标)时，如果它和之前的执行过的语句完全相同， ORACLE就能很快获得已经被解析的语句以及最好的执行路径。ORACLE的这个功能大提高了SQL的执行性能并节省了存的使用。 可惜的是ORACLE只对简单的表提供高速缓冲(cache buffering)，这个功能并不适用于多表连接查询。
数据库优化方案设计 XX信息管理平台从大型数据库环境四个不同级别的调整分析入手，分析数据库平台的系统结构和工作机理，从九个不同方面设计数据库的优化方案。 对于数据库的数据优化，主要有四个不同的调整级别，第一级调整是操作系统级包括硬件平台，第二级调整是RDBMS级的调整，第三级是数据库设计级的调整，最后一个调整级是SQL级。通常依此四级调整级别对数据库进行调整、优化，数据库的整体性能会得到很大的改善。下面从九个不同方面介绍数据库优化设计方案。 一、数据库优化自由结构 数据库的逻辑配置对数据库性能有很大的影响。为此，数据库平台一般对表空间设计提出有相应的优化结构，如ORACLE公司的OFA(Optimal flexible Architecture)，使用这种结构进行设计会大大简化物理设计中的数据管理。优化自由结构，简单地讲就是在数据库中可以高效自由地分布逻辑数据对象，因此首先要对数据库中的逻辑对象根据他们的使用方式和物理结构对数据库的影响来进行分类，这种分类包括将系统数据和用户数据分开、一般数据和索引数据分开、低活动表和高活动表分开等等。 数据库逻辑设计的结果应当符合下面的准则： （1）把以同样方式使用的段类型存储在一起； （2）按照标准使用来设计系统； （3）存在用于例外的分离区域； （4）最小化表空间冲突； （5）将数据字典分离。 二、充分利用系统全局区域 系统全局区域是数据库平台的心脏，如Oracle数据库的SGA（SYSTEM GLOBAL AREA) 。用户的进程对这个内存区发送事务，并且以这里作为高速缓存读取命中的数据，以实现加速的目的。正确的SGA大小对数据库的性能至关重要。SGA包括以下几个部分： 1、数据块缓冲区（data block buffer cache）是SGA中的一块高速缓存，占整个数据库大小的1%-2%，用来存储从数据库重读取的数据块（表、索引、簇等），因此采用least recently used (LRU，最近最少使用)的方法进行空间管理。 2、字典缓冲区。该缓冲区内的信息包括用户账号数据、数据文件名、段名、盘区位置、表说明和权限，它也采用LRU方式管理。 3、重做日志缓冲区。该缓冲区保存为数据库恢复过程中用于前滚操作。 4、SQL共享池。保存执行计划和运行数据库的SQL语句的语法分析树。也采用LRU 算法管理。如果设置过小，语句将被连续不断地再装入到库缓存，影响系统性能。 另外，SGA还包括大池、JAVA池、多缓冲池。但是主要是由上面4种缓冲区构成。对这些内存缓冲区的合理设置，可以大大加快数据查询速度，一个足够大的内存区可以把绝大多数数据存储在内存中，只有那些不怎么频繁使用的数据，才从磁盘读取，这样就可以大大提高内存区的命中率。 三、规范与反规范设计数据库
y物理模型CheckList （Oracle，性能） 1. 系统级优化 数据库参数配置 合理分配SGA及其内部参数（经验值如下）： SGA=phy*（60%-80%） Share pool=SAG*45% DB Cache=SGA*45% Log Buffer: 1~3M 注：Oracle9i在Windows下有bug，是由Windows下的SGA最大 值有2G的限制造成的 注意调整process和open cursor参数，这两个参数直接影响 数据库的session量 分离表和索引：将表和索引建立在不同的表空间，决不要将 不属于Oracle内部系统的对象存放到SYSTEM表空间。同 时，确保数据表空间和索引表空间置于不同的硬盘，减少I/O 竞争； 如果是企业版数据库，大表可以考虑采取分区存储措施，提 高系统的性能； 优化Export和Import工作：使用较大的BUFFER(比如10MB , 10,240,000)可以提高EXPORT和IMPORT的速度 定期分析查询计划，提高数据库的性能；
2. 索引相关 要对经常查询的字段建立索引，但是由于索引管理的开销， 在增删改操作频繁的情况下避免建立不必要的索引； 对于只读或者接近只读的场合，如数据仓库，对于势值比较 小的列可以考虑使用bitmap索引； 如果索引是建立在多个列上, 只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引. 3. SQL相关 Oracle的From子句表的顺序：记录越多的表放在越前面 （左）； Oracle的where子句表达式的顺序：过滤掉最大数目记录的条 件放到where子句的末尾； Select子句中避免使用‘*’，增加了查询表的列的开销； 在执行结果等效的情况下，使用Truncate代替Delete； 为了在查询过程中要尽量使用索引，对于like语句避免使用 右匹配或者中间匹配的模糊查询； 将过滤条件尽可能放到Where子句中，而不是放到Having子 句中； 在SQL语句中，要减少对表的查询，特别是在含有子查询的 SQL子句中； 使用表的别名可以减少解析的时间并避免引起歧义； 使用exists替代in； 用NOT EXISTS替代NOT IN； 通常情况下，采用表连接的方式比exists更有效率； 当提交一个包含一对多表信息(比如部门表和雇员表)的查询
SQL语句调优 1. SQL语句中IN包含的值不应过多 MySQL对于IN做了相应的优化，即将IN中的常量全部存储在一个数组里面，而且这个数组是排好序的。但是如果数值较多，产生的消耗也是比较大的。再例如：select id from t where num in(1,2,3) 对于连续的数值，能用between 就不要用in 了；再或者使用连接来替换。 2. SELECT语句务必指明字段名称 SELECT *增加很多不必要的消耗（cpu、io、内存、网络带宽）；增加了使用覆盖索引的可能性；当表结构发生改变时，前断也需要更新。所以要求直接在select后面接上字段名。 3. 当只需要一条数据的时候，使用limit 1 这是为了使EXPLAIN中type列达到const类型 4. 如果排序字段没有用到索引，就尽量少排序 5. 如果限制条件中其他字段没有索引，尽量少用or or两边的字段中，如果有一个不是索引字段，而其他条件也不是索引字段，会造成该查询不走索引的情况。很多时候使用union all 或者是union(必要的时候)的方式来代替“or”会得到更好的效果
6. 区分in和exists，not in和not exists ?select * from表A where id in (select id from表B) 上面sql语句相当于： ?select * from表A where exists(select * from表B where表B.id=表A.id)区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询。所以IN 适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。 关于not in和not exists，推荐使用not exists，不仅仅是效率问题，not in可能存在逻辑问题。如何高效的写出一个替代not exists的sql语句？ 原sql语句： ?select colname … from A表where a.id not in (select b.id from B表) 高效的sql语句： ?select colname … from A表Left join B表on where a.id = b.id where b.id is null 7. 分段查询
1、一张表，里面有ID自增主键，当insert了17条记录之后，删除了第15,16,17条记录，再把Mysql重启，再insert一条记录，这条记录的ID是18还是15 ？ (1)如果表的类型是MyISAM，那么是18 因为MyISAM表会把自增主键的最大ID记录到数据文件里，重启MySQL自增主键的最大ID也不会丢失 （2）如果表的类型是InnoDB，那么是15 InnoDB表只是把自增主键的最大ID记录到内存中，所以重启数据库或者是对表进行OPTIMIZE操作，都会导致最大ID丢失 2、Mysql的技术特点是什么？ Mysql数据库软件是一个客户端或服务器系统，其中包括：支持各种客户端程序和库的多线程SQL服务器、不同的后端、广泛的应用程序编程接口和管理工具。 3、Heap表是什么？ HEAP表存在于内存中，用于临时高速存储。 BLOB或TEXT字段是不允许的 只能使用比较运算符=，<，>，=>，= < HEAP表不支持AUTO_INCREMENT 索引不可为NULL 4、Mysql服务器默认端口是什么？ Mysql服务器的默认端口是3306。 5、与Oracle相比，Mysql有什么优势？ Mysql是开源软件，随时可用，无需付费。 Mysql是便携式的 带有命令提示符的GUI。 使用Mysql查询浏览器支持管理 6、如何区分FLOAT和DOUBLE？ 以下是FLOAT和DOUBLE的区别： 浮点数以8位精度存储在FLOAT中，并且有四个字节。 浮点数存储在DOUBLE中，精度为18位，有八个字节。 7、区分CHAR_LENGTH和LENGTH？ CHAR_LENGTH是字符数，而LENGTH是字节数。Latin字符的这两个数据是相同的，但是对于Unicode和其他编码，它们是不同的。 8、请简洁描述Mysql中InnoDB支持的四种事务隔离级别名称，以及逐级之间的区别？
SELECT语句的性能调优有时是一个非常耗时的任务，在我看来它遵循帕累托原则。20%的努力很可能会给你带来80%的性能提升，而为了获得另外20%的性能提升你可能需要花费80%的时间。除非你在金星工作，那里的每一天都等于地球上的243天，否则交付期限很有可能使你没有足够的时间来调优SQL查询。 根据我多年编写和运行SQL语句的经验，我开始开发一个检查列表，当我试图提高查询性能时供我参考。在进行查询计划和阅读我使用的数据库文档之前，我会参考其中的内容，数据库文档有时会很复杂。我的检查列表绝对说不上全面或科学，它更像是一个保守计算，但我可以说，遵循这些简单的步骤大部分时间我确实能得到性能提升。检查列表如下。 检查索引 在SQL语句的WHERE和JOIN部分中用到的所有字段上，都应该加上索引。进行这个3分钟SQL性能测试。不管你的成绩如何，一定要阅读那些带有信息的结果。 限制工作数据集的大小 检查那些SELECT语句中用到的表，看看你是否可以应用WHERE子句进行过滤。一个典型的例子是，当表中只有几千行记录时，一个查询能够很好地执行。但随着应用程序的成长，查询慢了下来。解决方案或许非常简单，限制查询来查看当前月的数据即可。 当你的查询语句带有子查询时，注意在子查询的内部语句上使用过滤，而不是在外部语句上。 只选择你需要的字段 额外的字段通常会增加返回数据的纹理，从而导致更多的数据被返回到SQL客户端。另外： •使用带有报告和分析功能的应用程序时，有时报告性能低是因为报告工具必须对收到的、带有详细形式的数据做聚合操作。 •偶尔查询也可能运行地足够快，但你的问题可能是一个网络相关的问题，因为大量的详细数据通过网络发送到报告服务器。 •当使用一个面向列的DBMS时，只有你选择的列会从磁盘读取。在你的查询中包含的列越少，IO开销就越小。 移除不必要的表 移除不必要的表的原因，和移除查询语句中不需要的字段的原因一致。 编写SQL语句是一个过程，通常需要大量编写和测试SQL语句的迭代过程。在开发过程中，你可能将表添加到查询中，而这对于SQL代码返回的数据可能不会有任何影响。一旦SQL运行正确，我发现许多人不会回顾他们的脚本，不会删除那些对最终的返回数据没有任何影响和作用的表。通过移除与那些不必要表的JOINS操作，你减少了大量数据库必须执行的流程。有时，就像移除列一样，你会发现你减少的数据又通过数据库返回来了。 移除外部连接查询 这说起来容易做起来难，它取决于改变表的内容有多大的影响。一个解决办法是通过在两个表的行中放置占位符来删除OUTER JOINS操作。假设你有以下的表，它们通过定义OUTER JOINS来确保返回所有的数据： customer_idcustomer_name 1John Doe 2Mary Jane 3Peter Pan 4Joe Soap
通过f5查看到的执行计划，其实是pl/sql developer工具内部执行查询 plan_table表然后格式化的结果。 select * from plan_table where statement_id＝...。其中 description列描述当前的数据库操作， object owner列表示对象所属用户， object name表示操作的对象， cost列表示当前操作的代价（消耗），这个列基本上就是评价sql语句的优劣，cardinality列表示操作影响的行数， bytes列表示字节数篇二：plsqldeveloper工具使用教程 plsql入门 pl/sql的概述 pl/sql的优势 pl/sql是一种块结构的语言，允许你将业务逻辑封装在一起，这是到目前为止使用pl/sql的最大优势 pl/sql是在服务器上运行，可以与数据库和sql引擎直接进行交互， pl/sql是什么？ (procedural language/sql) 是oracle在标准的sql语言上的扩展，pl/sql不仅允许嵌入sql语言，还可以定义变量和常量，允许使用条件语句和循环语句，允许使用列外处理各种错误，这样使得它的功能变得更加强大。 特性: 减少java程序的复杂性 一．过程,函数，触发器是pl/sql编写的 二．过程、函数、触发器是在oracle中 三． pl/sql是非常强大的数据库过程语言 四．过程，函数可以再java程序中调用 为什么学？ a) 提高应用程序的运行性能 b) 模块化的设计思想[分页的过程，订单的过程，转账的过程] c) 减少网络传输量（传统的方法，用sql语句传输！现在就只需要调用存储过程） d) 提高安全性（传统sql 可以看到表名字段等…） 不好： 移植性不好，（你写好的存储过程，函数等当我们要换数据库时，这些东西就没用了）开发工具： 1. sqlplus 开发工具 是oracle公司提供的一个工具，这个因为我们在以前介绍过： 2. pl/sql developer开发工具 pl/sql developer是用于开发pl/sql块的集成开发环境（ide） 它是一个独立的产品，而不是oracle的一个附带品， createprocedure sp_pro1//存储过程名字 is begin ---执行部分 insert into mytest values(‘’,’’); end; / 查看错误信息