資料庫索引:

索引有單列索引
復合索引之說

如何某表的某個欄位有主鍵約束和唯一性約束，則Oracle 則會自動在相應的約束列上建議唯一索引，資料庫索引主要進行提高訪問速度，

建設原則:

　1、索引應該經常建在Where 子句經常用到的列上，如果某個大表經常使用某個欄位進行查詢，并且檢索行數小于總表行數的5%，則應該考慮，

　2、對于兩表連接的欄位，應該建立索引，如果經常在某表的一個欄位進行Order By 則也經過進行索引，

　3、不應該在小表上建設索引，

優缺點:
　1、索引主要進行提高資料的查詢速度，當進行DML時，會更新索引，因此索引越多，則DML越慢，其需要維護索引，因此在創建索引及DML需要權衡，

創建索引:
　單一索引:Create Index <Index-Name> On <Table_Name>(Column_Name);

　復合索引: Create Index i_deptno_job on emp(deptno,job); —>在emp表的deptno、job列建立索引，

　　select * from emp where deptno=66 and job='sals' ->走索引，

　　select * from emp where deptno=66 OR job='sals' ->將進行全表掃描，不走索引

　　select * from emp where deptno=66 ->走索引，

　　select * from emp where job='sals' ->進行全表掃描、不走索引，

　　如果在where 子句中有OR 運算子或單獨參考Job 列(索引列的后面列) 則將不會走索引，將會進行全表掃描，

Sql 優化:

當Oracle資料庫拿到SQL陳述句時，其會根據查詢優化器分析該陳述句，并根據分析結果生成查詢執行計劃，
也就是說，資料庫是執行的查詢計劃，而不是Sql陳述句，
查詢優化器有rule-based-optimizer(基于規則的查詢優化器) 和Cost-Based-optimizer(基于成本的查詢優化器)，
其中基于規則的查詢優化器在10g版本中消失，
對于規則查詢，其最后查詢的是全表掃描，而CBO則會根據統計資訊進行最后的選擇，

1、先執行From ->Where ->Group By->Order By

2、執行From 字句是從右往左進行執行，因此必須選擇記錄條數最少的表放在右邊，這是為什么呢？　　

3、對于Where字句其執行順序是從后向前執行、因此可以過濾最大數量記錄的條件必須寫在Where子句的末尾，而對于多表之間的連接，則寫在之前，
因為這樣進行連接時，可以去掉大多不重復的項，　　

4. SELECT子句中避免使用(*)ORACLE在決議的程序中, 會將’*’ 依次轉換成所有的列名, 這個作業是通過查詢資料字典完成的, 這意味著將耗費更多的時間

5、索引失效的情況:
　① Not Null/Null 如果某列建立索引,當進行Select * from emp where depto is not null/is null，則會是索引失效，
　② 索引列上不要使用函式,SELECT Col FROM tbl WHERE substr(name ,1 ,3 ) = 'ABC'
或者SELECT Col FROM tbl WHERE name LIKE '%ABC%' 而SELECT Col FROM tbl WHERE name LIKE 'ABC%' 會使用索引，

　③ 索引列上不能進行計算SELECT Col FROM tbl WHERE col / 10 > 10 則會使索引失效，應該改成
SELECT Col FROM tbl WHERE col > 10 * 10

　④ 索引列上不要使用NOT （ != 、 <> ）如:SELECT Col FROM tbl WHERE col ! = 10
應該改成：SELECT Col FROM tbl WHERE col > 10 OR col < 10 ，

6、用UNION替換OR(適用于索引列)
　 union:是將兩個查詢的結果集進行追加在一起，它不會引起列的變化，由于是追加操作，需要兩個結果集的列數應該是相關的，
并且相應列的資料型別也應該相當的，union 回傳兩個結果集，同時將兩個結果集重復的項進行消除，如果不進行消除，用UNOIN ALL.

通常情況下, 用UNION替換WHERE子句中的OR將會起到較好的效果. 對索引列使用OR將造成全表掃描. 注意, 以上規則只針對多個索引列有效.
如果有column沒有被索引, 查詢效率可能會因為你沒有選擇OR而降低. 在下面的例子中, LOC_ID 和REGION上都建有索引.

　　高效:
　　SELECT LOC_ID , LOC_DESC , REGION
　　FROM LOCATION
　　WHERE LOC_ID = 10
　　UNION
　　SELECT LOC_ID , LOC_DESC , REGION
　　FROM LOCATION
　　WHERE REGION = “MELBOURNE”

　　低效:
　　SELECT LOC_ID , LOC_DESC , REGION
　　FROM LOCATION
　　WHERE LOC_ID = 10 OR REGION = “MELBOURNE”
　　如果你堅持要用OR, 那就需要回傳記錄最少的索引列寫在最前面.

7. 用EXISTS替代IN、用NOT EXISTS替代NOT IN
在許多基于基礎表的查詢中, 為了滿足一個條件, 往往需要對另一個表進行聯接. 在這種情況下, 使用EXISTS(或NOT EXISTS)通常將提高查詢的效率.
在子查詢中, NOT IN子句將執行一個內部的排序和合并. 無論在哪種情況下, NOT IN都是最低效的(因為它對子查詢中的表執行了一個全表遍歷).
為了避免使用NOT IN, 我們可以把它改寫成外連接(Outer Joins)或NOT EXISTS.

例子：

高效: SELECT * FROM EMP (基礎表) WHERE EMPNO > 0 AND EXISTS (SELECT ‘X’ FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB’)

低效: SELECT * FROM EMP (基礎表) WHERE EMPNO > 0 AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = ‘MELB’)

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/2492.html

標籤：Oracle

上一篇：java +mybatis 獲取oracle存盤的blob物件

下一篇：Oracle鎖表與解鎖的方案

Oracle 建立索引及SQL優化

資料庫索引: