- 相關(guān)推薦
Oracle認(rèn)證：Oracle避免全表掃描方式
不知道各位同學(xué)提起Oracle認(rèn)證考試來(lái)還頭疼嗎?還覺得沒有什么復(fù)習(xí)頭緒嗎?下面小編就為大家整理了一些Oracle認(rèn)證復(fù)習(xí)備考資料。希望大家可以從中學(xué)習(xí)答題方法，讓自己的得到進(jìn)步!
1.對(duì)返回的行無(wú)任何限定條件，即沒有where 子句
2.未對(duì)數(shù)據(jù)表與任何索引主列相對(duì)應(yīng)的行限定條件
例如：在City-State-Zip列創(chuàng)建了三列復(fù)合索引，那么僅對(duì)State列限定條件不能使用這個(gè)索引，因?yàn)镾tate不是索引的主列。
3.對(duì)索引的主列有限定條件，但是在條件表達(dá)式里使用以下表達(dá)式則會(huì)使索引失效，造成全表掃描：
(1)where子句中對(duì)字段進(jìn)行函數(shù)、表達(dá)式操作，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描，
Demo:
where upper(city)='TokYo' 或 City || 'X' like 'TOKYO%',
select id from t where num/2=100 應(yīng)改為： select id from t where num=100*2
select * from emp where to_char(hire_date,'yyyymmdd')='20080411'(不使用)
select * from emp where hire_date = to_char('20080411','yyyymmdd')(使用)
(2)查詢字段is null時(shí)索引失效，引起全表掃描。
where City is null 或 ,where City is not null,
解決方法：SQL語(yǔ)法中使用NULL會(huì)有很多麻煩，最好索引列都是NOT NULL的;對(duì)于is null,可以建立組合索引，nvl(字段，0)，對(duì)表和索引analyse后，is null查詢時(shí)可以重新啟用索引查找，但是效率還不是值得肯定;is not null 時(shí)永遠(yuǎn)不會(huì)使用索引。一般數(shù)據(jù)量大的表不要用is null查詢。
select id from t where num is null
可以在num上設(shè)置默認(rèn)值0,確保表中num列沒有null值，然后這樣查詢：
select id from t where num=0
(3)查詢條件中使用了不等于操作符(<>、!=)會(huì)限制索引、引起全表掃描
Where city!='TOKYO'.
解決方法：通過(guò)把不等于操作符改成or,可以使用索引，避免全表掃描。例如，把column<>'aaa',改成column<'aaa' or column>'aaa',就可以使用索引了。
(4)對(duì)索引的主列有限定條件，但是條件使用like操作以及值以'%'開始或者值是一個(gè)賦值變量。例如：
where City like '%YOK%'
where City like: City_bind_Variable xl_rao
select * from emp where name like '%A' (不使用索引)
select * from emp where name like 'A%' (使用索引)
解決辦法：首先盡量避免模糊查詢，如果因?yàn)闃I(yè)務(wù)需要一定要使用模糊查詢，則至少保證不要使用全模糊查詢，對(duì)于右模糊查詢，即like '…%',是會(huì)使用索引的;左模糊like '%…'無(wú)法直接使用索引，但可以利用reverse + function index 的形式，變化成 like '…%';全模糊是無(wú)法優(yōu)化的，一定要的話考慮用搜索引擎。出于降低數(shù)據(jù)庫(kù)服務(wù)器的負(fù)載考慮，盡可能地減少數(shù)據(jù)庫(kù)模糊查詢。
4.or語(yǔ)句使用不當(dāng)會(huì)引起全表掃描
原因：where子句中比較的兩個(gè)條件，一個(gè)有索引，一個(gè)沒索引，使用or則會(huì)引起全表掃描。例如：where A=:1 or B=:2,A上有索引，B上沒索引，則比較B=:2時(shí)會(huì)重新開始全表掃描
5.模糊查詢效率很低：
原因：like本身效率就比較低，應(yīng)該盡量避免查詢條件使用like;對(duì)于like'%…%'(全模糊)這樣的條件，是無(wú)法使用索引的，全表掃描自然效率很低;另外，由于匹配算法的關(guān)系，模糊查詢的字段長(zhǎng)度越大，模糊查詢效率越低。
解決辦法：首先盡量避免模糊查詢，如果因?yàn)闃I(yè)務(wù)需要一定要使用模糊查詢，則至少保證不要使用全模糊查詢，對(duì)于右模糊查詢，即like'…%',是會(huì)使用索引的;左模糊like
'%…'無(wú)法直接使用索引，但可以利用reverse + function index的形式，變化成like'…%';全模糊是無(wú)法優(yōu)化的，一定要的話考慮用搜索引擎。出于降低數(shù)據(jù)庫(kù)服務(wù)器的負(fù)載考慮，盡可能地減少數(shù)據(jù)庫(kù)模糊查詢。
6.查詢條件中含有is null的select語(yǔ)句執(zhí)行慢
原因：Oracle 中，查詢字段is null時(shí)單索引失效，引起全表掃描。
解決方法：SQL語(yǔ)法中使用NULL會(huì)有很多麻煩，最好索引列都是NOT NULL的;對(duì)于is null,可以建立組合索引，nvl(字段，0)，對(duì)表和索引analyse后，is null查詢時(shí)可以重新啟用索引查找，但是效率還不是值得肯定;is not null時(shí)永遠(yuǎn)不會(huì)使用索引。一般數(shù)據(jù)量大的表不要用is null查詢。
7.查詢條件中使用了不等于操作符(<>、!=)的select語(yǔ)句執(zhí)行慢
原因：SQL中，不等于操作符會(huì)限制索引，引起全表掃描，即使比較的字段上有索引
解決方法：通過(guò)把不等于操作符改成or,可以使用索引，避免全表掃描。例如，把column<>'aaa',改成column<'aaa'or column>'aaa',就可以使用索引了。
8.使用組合索引，如果查詢條件中沒有前導(dǎo)列，那么索引不起作用，會(huì)引起全表掃描;但是從Oracle9i開始，引入了索引跳躍式掃描的特性，可以允許優(yōu)化器使用組合索引，即便索引的前導(dǎo)列沒有出現(xiàn)在WHERE子句中。例如：create index skip1 on emp5(job,empno); 全索引掃描select count(*) from emp5 where empno=7900; 索引跳躍式掃描select /*+ index(emp5 skip1)*/ count(*) from emp5 where empno=7900;前一種是全表掃描，后一種則會(huì)使用組合索引。
9.or語(yǔ)句使用不當(dāng)會(huì)引起全表掃描
原因：where子句中比較的兩個(gè)條件，一個(gè)有索引，一個(gè)沒索引，使用or則會(huì)引起全表掃描。例如：where A=:1 or B=:2,A上有索引，B上沒索引，則比較B=:2時(shí)會(huì)重新開始全表掃描。
10.組合索引，排序時(shí)應(yīng)按照組合索引中各列的順序進(jìn)行排序，即使索引中只有一個(gè)列是要排序的，否則排序性能會(huì)比較差。例如：create index skip1 on emp5(job,empno,date); select job,empno from emp5 where job='manager'and empno='10'order by job,empno,date desc;實(shí)際上只是查詢出符合job='manager'and empno='10'條件的記錄并按date降序排列，但是寫成order by date desc性能較差。
11.Update語(yǔ)句，如果只更改1、2個(gè)字段，不要Update全部字段，否則頻繁調(diào)用會(huì)引起明顯的性能消耗，同時(shí)帶來(lái)大量日志。
12.對(duì)于多張大數(shù)據(jù)量的表JOIN,要先分頁(yè)再JOIN,否則邏輯讀會(huì)很高，性能很差。
據(jù)了解到，2016年Oracle認(rèn)證考試已經(jīng)在緊張的備考中了，在后期中考來(lái)臨之際我們將會(huì)第一時(shí)間為廣大考生發(fā)布中考時(shí)間安排，請(qǐng)廣大考生隨時(shí)關(guān)注本站。下面是Oracle認(rèn)證復(fù)習(xí)備考資料。
13.select count(*) from table;這樣不帶任何條件的count會(huì)引起全表掃描，并且沒有任何業(yè)務(wù)意義，是一定要杜絕的。
14.sql的where條件要綁定變量，比如where column=:1,不要寫成where column='aaa',這樣會(huì)導(dǎo)致每次執(zhí)行時(shí)都會(huì)重新分析，浪費(fèi)CPU和內(nèi)存資源。
15.不要使用in操作符，這樣數(shù)據(jù)庫(kù)會(huì)進(jìn)行全表掃描，
推薦方案：在業(yè)務(wù)密集的SQL當(dāng)中盡量不采用IN操作符
16.not in 使用not in也不會(huì)走索引
推薦方案：用not exists或者(外聯(lián)結(jié)+判斷為空)來(lái)代替
17.> 及 < 操作符(大于或小于操作符)
大于或小于操作符一般情況下是不用調(diào)整的，因?yàn)樗兴饕蜁?huì)采用索引查找，但有的情況下可以對(duì)它進(jìn)行優(yōu)化，如一個(gè)表有100萬(wàn)記錄，一個(gè)數(shù)值型字段 A,30萬(wàn)記錄的A=0,30萬(wàn)記錄的A=1,39萬(wàn)記錄的A=2,1萬(wàn)記錄的A=3.那么執(zhí)行A>2與A>=3的效果就有很大的區(qū)別了，因?yàn)锳>2時(shí)ORACLE會(huì)先找出為2的記錄索引再進(jìn)行比較，而A>=3時(shí)ORACLE則直接找到=3的記錄索引。
18.UNION操作符
UNION在進(jìn)行表鏈接后會(huì)篩選掉重復(fù)的記錄，所以在表鏈接后會(huì)對(duì)所產(chǎn)生的結(jié)果集進(jìn)行排序運(yùn)算，刪除重復(fù)的記錄再返回結(jié)果。實(shí)際大部分應(yīng)用中是不會(huì)產(chǎn)生重復(fù)的記錄，最常見的是過(guò)程表與歷史表UNION.如：
select * from gc_dfys
union
select * from ls_jg_dfys
這個(gè)SQL在運(yùn)行時(shí)先取出兩個(gè)表的結(jié)果，再用排序空間進(jìn)行排序刪除重復(fù)的記錄，最后返回結(jié)果集，如果表數(shù)據(jù)量大的話可能會(huì)導(dǎo)致用磁盤進(jìn)行排序。
推薦方案：采用UNION ALL操作符替代UNION,因?yàn)閁NION ALL操作只是簡(jiǎn)單的將兩個(gè)結(jié)果合并后就返回。
19.WHERE后面的條件順序影響
WHERE子句后面的條件順序?qū)Υ髷?shù)據(jù)量表的查詢會(huì)產(chǎn)生直接的影響，如
Select * from zl_yhjbqk where dy_dj = '1K以下' and xh_bz=1
Select * from zl_yhjbqk where xh_bz=1 and dy_dj = '1K以下'
以上兩個(gè)SQL中dy_dj及xh_bz兩個(gè)字段都沒進(jìn)行索引，所以執(zhí)行的時(shí)候都是全表掃描，第一條SQL的dy_dj = '1KV以下'條件在記錄集內(nèi)比率為99%,而xh_bz=1的比率只為0.5%,在進(jìn)行第一條SQL的時(shí)候99%條記錄都進(jìn)行dy_dj及xh_bz的比較，而在進(jìn)行第二條SQL的時(shí)候0.5%條記錄都進(jìn)行dy_dj及xh_bz的比較，以此可以得出第二條SQL的CPU占用率明顯比第一條低。
20.查詢表順序的影響
在FROM后面的表中的列表順序會(huì)對(duì)SQL執(zhí)行性能影響，在沒有索引及ORACLE沒有對(duì)表進(jìn)行統(tǒng)計(jì)分析的情況下ORACLE會(huì)按表出現(xiàn)的順序進(jìn)行鏈接，由此因?yàn)楸淼捻樞虿粚?duì)會(huì)產(chǎn)生十分耗服務(wù)器資源的數(shù)據(jù)交叉。(注：如果對(duì)表進(jìn)行了統(tǒng)計(jì)分析，ORACLE會(huì)自動(dòng)先進(jìn)小表的鏈接，再進(jìn)行大表的鏈接)
【Oracle認(rèn)證：Oracle避免全表掃描方式】相關(guān)文章：
oracle sysdba級(jí)用戶的認(rèn)證方式01-21
Oracle認(rèn)證：ORACLE綁定變量BINDPEEKING03-08
Oracle認(rèn)證作用03-19
Oracle最新認(rèn)證03-09
Oracle認(rèn)證途徑03-20
Oracle認(rèn)證：Oracle控制件文件修復(fù)03-18