oracle中sql語句的優化

一、執行順序及優化細則

1.表名順序優化 ?
(1) 基礎表放下面,當兩表進行關聯時數據量少的表的表名放右邊
表或視圖:?
Student_info?? (30000條數據)
Description_info (30條數據)??
select *
? from description_info di
????? ,student_info???? si --學生信息表
where si.student_id = di.lookup_code(+)
?? and di.lookup_type(+) = 'STUDENT_ID'??????
與????
select *
? from student_info???? si--學生信息表
????? ,description_info di
where si.student_id = di.lookup_code(+)
?? and di.lookup_type(+) = 'STUDENT_ID'???
以student_info作為基礎表,你會發現運行的速度會有很大的差距。
???
???
(2) 當出現多個表時,關聯表被稱之為交叉表,交叉表作為基礎表
select *
? from description_info di
??? ,description_info di2
????? ,student_info???? si --學生信息表
where si.student_id = di.lookup_code(+)
?? and di.lookup_type(+) = 'STUDENT_ID'
?? and si.school_id = di.lookup_code(+)
?? and di.lookup_type(+) = 'SCHOOL_ID'
與
select *
? from student_info???? si--學生信息表
????? ,description_info di
????? ,description_info di2
where si.student_id = di.lookup_code(+)
?? and di.lookup_type(+) = 'STUDENT_ID'
?? and si.school_id = di.lookup_code(+)
?? and di.lookup_type(+) = 'SCHOOL_ID'
以student_info作為基礎表,你會發現運行的速度會有很大的差距,
當基礎表放在后面,這樣的執行速度會明顯快很多。

2.where執行順序
where執行會從至下往上執行
select *
from student_info si --學生信息表
where si.school_id=10 --學院ID
and? si.system_id=100--系ID
擺放where子句時,把能過濾大量數據的條件放在最下邊

3. is null 和is not null
當要過濾列為空數據或不為空的數據時使用
select *
from student_info si --學生信息表
where si.school_id is null(當前列中的null為少數時用is not null,否則is null)

4.使用表別名
當查詢時出現多個表時,查詢時加上別名,
避免出現減少解析的時間字段歧義引起的語法錯誤。

5. where執行速度比having快
盡可能的使用where代替having
select? from student_info si
group by si.student_id
having si.system_id!=100
? and si.school_id!=10
(select? from student_info si
wehre si.system_id!=100
and si.school_id!=10
group by si.student_id)??
??
6.? * 號引起的執行效率
盡量減少使用select * 來進行查詢,當你查詢使用*,
數據庫會進行解析并將*轉換為全部列。

二、替代優化
1、用>=替代> ?
select ui.user_name
? from user_info ui--員工信息表
? where ui.student_id>=10
? 與
? select ui.user_name
? from user_info ui--員工信息表
? where ui.student_id>9
? 執行時>=會比>執行得要快?
??
2、用UNION替換OR (適用于索引列) ?
select ui.user_name
? from user_info ui--員工信息表
? where ui.student_id=10
? union?
select ui.user_name
? from user_info ui--員工信息表
? where ui.student_id=2??
?? 上面語句可有效避免全表查詢
?? select ui.user_name
? from user_info ui--員工信息表
? where ui.student_id=10?
? or ui.student_id=2
? 如果堅持要用OR, 可以把返回記錄最少的索引列寫在最前面
????
3、用in 代替or
select ui.user_name
? from user_info ui--員工信息表
? where ui.student_id=10
? or ui.student_id=20
? or ui.student_id=30
? 改成
? select ui.user_name
? from user_info ui--員工信息表
? where ui.student_id in (10,20,30)
? 執行會更有效率
????
4、 Union All 與Union
Union All重復輸出兩個結果集合中相同記錄
如果兩個并集中數據都不一樣.那么使用Union All 與Union是沒有區別的,
select ui.user_name
? from user_info ui--員工信息表
? where ui.student_id=10
? union All
select ui.user_name
? from user_info ui--員工信息表
? where ui.student_id=2??
? 與
? select ui.user_name
? from user_info ui--員工信息表
? where ui.student_id=10
? union?
select ui.user_name
? from user_info ui--員工信息表
? where ui.student_id=2??
但Union All會比Union要執行得快

5、分離表和索引?
總是將你的表和索引建立在另外的表空間內?
決不要將這些對象存放到SYSTEM表空間里

三、一些優化技巧

1、計算表的記錄數時

select count(si.student_id)?
from Student_info si(student_id為索引)
與
select count(*) from Student_info si
執行時.上面的語句明顯會比下面沒有用索引統計的語句要快

2.使用函數提高SQL執行速度

當出現復雜的查詢sql語名,可以考慮使用函數來提高速度
查詢學生信息并查詢學生(李明)個人信息與的數學成績排名
如?
select di.description student_name
????? ,(select res.order_num--排名
???????? from result res
??????? where res.student_id = di.student_id
??????? order by result_math) order_num
? from description_info di
????? ,student_info???? si --學生信息表
where si.student_id = di.lookup_code(+)
?? and di.lookup_type(+) = 'STUDENT_ID'
?? and di.description = '李明'
???
而且我們將上面order_num排名寫成一個fuction時
create or replace package body order_num_pkg is
function order_num(p_student_id number) return_number is
? v_return_number number;
begin
? select res.order_num --排名
??? into v_return_number
??? from result res
?? where res.student_id = di.student_id
?? order by result_math;
? return v_return_number;
exception
? when others then
??? null;
??? return null;
end;
end order_num_pkg;
執行
select di.description student_name
????? ,order_num_pkg.order_num(di.student_id) order_num
? from description_info di
????? ,student_info???? si --學生信息表
where si.student_id = di.lookup_code(+)
?? and di.lookup_type(+) = 'STUDENT_ID'
?? and di.description = '李明'
執行查詢時的速度也會有所提高?????
?
3.減少訪問數據庫的次數

執行次數的減少(當要查詢出student_id=100的學生和student_id=20的學生信息時)
select address_id
from student_info si --學生信息表
where si.student_id=100
與
select address_id
from student_info si --學生信息表
where si.student_id=20
都進行查詢.這樣的效率是很低的
而進行
(
select si.address_id,si2.address_id
from student_info si --學生信息表
,student_info si2?
where si.student_id=100
and si2.student_id=20?
與
select decode(si.student_id,100,address_id)
?? ,decode(si.student_id,20,address_id)
from student_info si
)
執行速度是提高了,但可讀性反而差了..
所以這種寫法個人并不太推薦

4、用Exists(Not Exists)代替In(Not In)

?? 在執行當中使用Exists或者Not Exists可以高效的進行查詢

5、Exists取代Distinct取唯一值的

?? 取出關聯表部門對員工時,這時取出員工部門時,出現多條..
select distinct di.dept_name?
? from departments_info di --部門表
????? ,user_info??????? ui --員工信息表
where ui.dept_no = di.dept_no
?? 可以修改成
? select di.dept_name
??? from departments_info di --部門表
?? where? exists (select 'X'
??????????? from user_info ui --員工信息表
?????????? where di.dept_no = ui.dept_no)
6、用表連接代替Exists
?? 通過表的關聯來代替exists會使執行更有效率
select ui.user_name
? from user_info ui--員工信息表
where exists (select 'x '
????????? from departments_info di--部門表
???????? where di.dept_no = ui.dept_no
?????????? and ui.dept_cat = 'IT');
執行是比較快,但還可以使用表的連接取得更快的查詢效率
?? select ui.user_name
??? from departments_info di
??????? ,user_info??????? ui --員工信息表
?? where ui.dept_no = di.dept_no
???? and ui.department_type_code = 'IT'

代碼是經測試并進行優化所寫,
以上只例子,具體使用還是要針對各個不同的具體的業務使用用Exists(Not Exists)代替In(Not In)

四、索引篇

1、運算導致的索引失效

select di.description student_name
????? ,(select res.order_num--排名
???????? from result res
??????? where res.student_id = di.student_id
??????? order by result_math) order_num
? from description_info di
????? ,student_info???? si --學生信息表
where si.student_id = di.lookup_code(+)
?? and di.lookup_type(+) = 'STUDENT_ID'
?? and di.student_id='100'
??
student_id為number類型的索引,當執行下列語句,
oracle會自動轉換成
select di.description student_name
????? ,(select res.order_num--排名
???????? from result res
??????? where res.student_id = di.student_id
??????? order by result_math) order_num
? from description_info di
????? ,student_info???? si --學生信息表
where si.student_id = di.lookup_code(+)
?? and di.lookup_type(+) = 'STUDENT_ID'
?? and di.student_id=to_number('100')
所幸,只是解析并轉換類型,并沒有導到失效,
但要是寫成下面,將會使用其失效
select di.description student_name
????? ,(select res.order_num--排名
???????? from result res
??????? where res.student_id = di.student_id
??????? order by result_math) order_num
? from description_info di
????? ,student_info???? si --學生信息表
where si.student_id = di.lookup_code(+)
?? and di.lookup_type(+) = 'STUDENT_ID'
?? and to_char(di.student_id)='100'
???
3、在索引列上進行計算引起的問題

4、 Is not null引起的問題(student_id為索引)

不要把存在空值的列做為索引,否則無法使用索引
select ui.user_name
? from user_info ui--員工信息表
? where ui.student_id is not null--索引失效?
??
select ui.user_name
? from user_info ui--員工信息表
? where ui.student_id>=-1--索引有效

5、Order by導致索引失效(student_id為索引)

select ui.user_name
? from user_info ui--員工信息表
? group by ui.student_id???
而使用
select ui.user_name
? from user_info ui--員工信息表
? where ui.student_id>=-1
? 將使其有效,
? 在order by中只存在兩種條件下可以使用索引
? (ORDER BY中所有的列必須包含在相同的索引中并保持在索引中的排列順序
ORDER BY中所有的列必須定義為非空. )
??
6、自動選擇索引
?
如果表中有兩個以上（包括兩個）索引，其中有一個唯一性索引，而其他是非唯一性．
在這種情況下，ORACLE將使用唯一性索引而完全忽略非唯一性索引．

7、 !=導致索引失效
?
select ui.user_name
? from user_info ui--員工信息表
? where ui.student_id!=0
在Where中使用!=將會把索引失效

8、%導致的索引失效

五、oracle 中的not Exists與Not in的性能巨大差異

Not Exists與Not in的作用同樣是排除數據,在oracle 中使用not in并不象mysql中的執行那么快,如(
select jt1.doc_num --單據號碼
????? ,oalc.description school_name --學校名稱
????? ,oalc2.description system_name --系名稱
????? ,oalc.description class_name --班級名稱
? from java_table1??????????? jt1
????? ,java_table_description oalc
????? ,java_table_description oalc2
????? ,java_table_description oalc3
where oalc.lookup_type(+) = 'JAVA_SCHOOL_NAME'
?? and jt1.school_id = oalc.lookup_code(+)
?? and oalc2.lookup_type(+) = 'JAVA_SYSTEM_NAME'
?? and jt1.system_id = oalc2.lookup_code(+)
?? and oalc3.lookup_type(+) = 'JAVA_CLASS_NAME'
?? and jt1.class_id = oalc3.lookup_code(+)
?? and not exists
(select jt2.header_id
????????? from java_table2 jt2 jt1.header_id = jt2.header_id))

與

select jt1.doc_num --單據號碼
????? ,oalc.description school_name --學校名稱
????? ,oalc2.description system_name --系名稱
????? ,oalc.description class_name --班級名稱
? from java_table1??????????? jt1
????? ,java_table_description oalc
????? ,java_table_description oalc2
????? ,java_table_description oalc3
where oalc.lookup_type(+) = 'JAVA_SCHOOL_NAME'
?? and jt1.school_id = oalc.lookup_code(+)
?? and oalc2.lookup_type(+) = 'JAVA_SYSTEM_NAME'
?? and jt1.system_id = oalc2.lookup_code(+)
?? and oalc3.lookup_type(+) = 'JAVA_CLASS_NAME'
?? and jt1.class_id = oalc3.lookup_code(+)
?? and jt1.header_id not in (select jt2.header_id from java_table2 jt2)

當jt2表中的數據比較大時,就會出現巨大的差異,以上只能是我的個人理解與測試結果(java_table1 視圖測試

數據量為36749,java_table2 為300條),如有其它可相互討論

查詢哪些書沒有借出？?
典型的查詢方式為：?
SELECT TITLE FROM BOOKSHELF WHERE TITLE NOT IN?
?? (SELECT TITLE FROM BOOKSHELF_CHECKOUT)?
ORDER BY TITLE；?
如果BOOKSHELF_CHECKOUT很大的話，速度可能會很慢，因為ORACLE會在BOOKSHELF_CHECKOUT上執行一個時間密集型的全表掃描。?

oracle 中not in 效率不高?
一：使用外部連接?
SELECT DISTINCT C.TITLEFROM BOOKSHELF_CHECKOUT B?
RIGHT OUTER JOIN BOOKSHELF C ON B.TITLE = C.TITLE?
WHERE B.TITLE IS NULL?
ORDER BY C.TITLE ;?
優化后的程序可以使用連接列上的索引。?
WHERE B.TITLE IS NULL?
表示不出現在BOOKSHELF_CHECKOUT中的TITLE列（ORACLE作為NULL列返回，可參考外部連接方面的內容）?
二：使用NOT EXISTS?
SELECT B.TITLE FROM BOOKSHELF?
B WHERE NOT EXISTS?
? (SELECT 'X' FROM BOOKSHELF_CHECKOUT C WHERE C.TITLE = B.TITLE) ORDER BY B.TITLE?
對于BOOKSHELF中每一個記錄和BOOKSHELF_CHECKOUT匹配則是EXISTS.NOT EXISTS則是不存在的。NOT EXISTS往往可以使用可利用的索引，NOT IN 可能無法使用這些索引。?

此外：?
在Oracle中，not in (...) 括號中的返回值不能存在null值，如果不能確定返回結果一定無null值，還是改為not esists吧。而且not in效率低下，一般不能用到索引，生產環境的程序最好不要使用。

http://myjcwy.iteye.com/blog/553563

oracle中sql語句的優化

更多文章、技術交流、商務合作、聯系博主

微信掃碼或搜索：z360901061

微信掃一掃加我為好友

QQ號聯系： 360901061

您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。

【本文對您有幫助就好】元

2元

5元

10元

20元

自定義

亚洲免费在线-亚洲免费在线播放-亚洲免费在线观看-亚洲免费在线观看视频-亚洲免费在线看-亚洲免费在线视频

oracle中sql語句的優化