Oracle認證：Oracle避免全表掃描方式

　　不知道各位同學(xué)提起Oracle認證考試來(lái)還頭疼嗎?還覺(jué)得沒(méi)有什么復習頭緒嗎?下面小編就為大家整理了一些Oracle認證復習備考資料。希望大家可以從中學(xué)習答題方法，讓自己的得到進(jìn)步!

　　1.對返回的行無(wú)任何限定條件，即沒(méi)有where 子句

　　2.未對數據表與任何索引主列相對應的行限定條件

　　例如：在City-State-Zip列創(chuàng )建了三列復合索引，那么僅對State列限定條件不能使用這個(gè)索引，因為State不是索引的主列。

　　3.對索引的主列有限定條件，但是在條件表達式里使用以下表達式則會(huì )使索引失效，造成全表掃描：

　　(1)where子句中對字段進(jìn)行函數、表達式操作，這將導致引擎放棄使用索引而進(jìn)行全表掃描，

　　Demo:

　　where upper(city)='TokYo' 或 City || 'X' like 'TOKYO%',

　　select id from t where num/2=100 應改為： 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)查詢(xún)字段is null時(shí)索引失效，引起全表掃描。

　　where City is null 或 ,where City is not null,

　　解決方法：SQL語(yǔ)法中使用NULL會(huì )有很多麻煩，最好索引列都是NOT NULL的;對于is null,可以建立組合索引，nvl(字段，0)，對表和索引analyse后，is null查詢(xún)時(shí)可以重新啟用索引查找，但是效率還不是值得肯定;is not null 時(shí)永遠不會(huì )使用索引。一般數據量大的表不要用is null查詢(xún)。

　　select id from t where num is null

　　可以在num上設置默認值0,確保表中num列沒(méi)有null值，然后這樣查詢(xún)：

　　select id from t where num=0

　　(3)查詢(xún)條件中使用了不等于操作符(<>、!=)會(huì )限制索引、引起全表掃描

　　Where city!='TOKYO'.

　　解決方法：通過(guò)把不等于操作符改成or,可以使用索引，避免全表掃描。例如，把column<>'aaa',改成column<'aaa' or column>'aaa',就可以使用索引了。

　　(4)對索引的主列有限定條件，但是條件使用like操作以及值以'%'開(kāi)始或者值是一個(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%' (使用索引)

　　解決辦法：首先盡量避免模糊查詢(xún)，如果因為業(yè)務(wù)需要一定要使用模糊查詢(xún)，則至少保證不要使用全模糊查詢(xún)，對于右模糊查詢(xún)，即like '…%',是會(huì )使用索引的;左模糊like '%…'無(wú)法直接使用索引，但可以利用reverse + function index 的形式，變化成 like '…%';全模糊是無(wú)法優(yōu)化的，一定要的話(huà)考慮用搜索引擎。出于降低數據庫服務(wù)器的負載考慮，盡可能地減少數據庫模糊查詢(xún)。

　　4.or語(yǔ)句使用不當會(huì )引起全表掃描

　　原因：where子句中比較的兩個(gè)條件，一個(gè)有索引，一個(gè)沒(méi)索引，使用or則會(huì )引起全表掃描。例如：where A=:1 or B=:2,A上有索引，B上沒(méi)索引，則比較B=:2時(shí)會(huì )重新開(kāi)始全表掃描

　　5.模糊查詢(xún)效率很低：

　　原因：like本身效率就比較低，應該盡量避免查詢(xún)條件使用like;對于like'%…%'(全模糊)這樣的條件，是無(wú)法使用索引的，全表掃描自然效率很低;另外，由于匹配算法的關(guān)系，模糊查詢(xún)的字段長(cháng)度越大，模糊查詢(xún)效率越低。

　　解決辦法：首先盡量避免模糊查詢(xún)，如果因為業(yè)務(wù)需要一定要使用模糊查詢(xún)，則至少保證不要使用全模糊查詢(xún)，對于右模糊查詢(xún)，即like'…%',是會(huì )使用索引的;左模糊like

　　'%…'無(wú)法直接使用索引，但可以利用reverse + function index的形式，變化成like'…%';全模糊是無(wú)法優(yōu)化的，一定要的話(huà)考慮用搜索引擎。出于降低數據庫服務(wù)器的負載考慮，盡可能地減少數據庫模糊查詢(xún)。

　　6.查詢(xún)條件中含有is null的select語(yǔ)句執行慢

　　原因：Oracle 中，查詢(xún)字段is null時(shí)單索引失效，引起全表掃描。

　　解決方法：SQL語(yǔ)法中使用NULL會(huì )有很多麻煩，最好索引列都是NOT NULL的;對于is null,可以建立組合索引，nvl(字段，0)，對表和索引analyse后，is null查詢(xún)時(shí)可以重新啟用索引查找，但是效率還不是值得肯定;is not null時(shí)永遠不會(huì )使用索引。一般數據量大的表不要用is null查詢(xún)。

　　7.查詢(xún)條件中使用了不等于操作符(<>、!=)的select語(yǔ)句執行慢

　　原因：SQL中，不等于操作符會(huì )限制索引，引起全表掃描，即使比較的字段上有索引

　　解決方法：通過(guò)把不等于操作符改成or,可以使用索引，避免全表掃描。例如，把column<>'aaa',改成column<'aaa'or column>'aaa',就可以使用索引了。

　　8.使用組合索引，如果查詢(xún)條件中沒(méi)有前導列，那么索引不起作用，會(huì )引起全表掃描;但是從Oracle9i開(kāi)始，引入了索引跳躍式掃描的特性，可以允許優(yōu)化器使用組合索引，即便索引的前導列沒(méi)有出現在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ǔ)句使用不當會(huì )引起全表掃描

　　原因：where子句中比較的兩個(gè)條件，一個(gè)有索引，一個(gè)沒(méi)索引，使用or則會(huì )引起全表掃描。例如：where A=:1 or B=:2,A上有索引，B上沒(méi)索引，則比較B=:2時(shí)會(huì )重新開(kāi)始全表掃描。

　　10.組合索引，排序時(shí)應按照組合索引中各列的順序進(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í)際上只是查詢(xún)出符合job='manager'and empno='10'條件的記錄并按date降序排列，但是寫(xiě)成order by date desc性能較差。

　　11.Update語(yǔ)句，如果只更改1、2個(gè)字段，不要Update全部字段，否則頻繁調用會(huì )引起明顯的性能消耗，同時(shí)帶來(lái)大量日志。

　　12.對于多張大數據量的表JOIN,要先分頁(yè)再JOIN,否則邏輯讀會(huì )很高，性能很差。

　　據了解到，2016年Oracle認證考試已經(jīng)在緊張的備考中了，在后期中考來(lái)臨之際我們將會(huì )第一時(shí)間為廣大考生發(fā)布中考時(shí)間安排，請廣大考生隨時(shí)關(guān)注本站。下面是Oracle認證復習備考資料。

　　13.select count(*) from table;這樣不帶任何條件的count會(huì )引起全表掃描，并且沒(méi)有任何業(yè)務(wù)意義，是一定要杜絕的。

　　14.sql的where條件要綁定變量，比如where column=:1,不要寫(xiě)成where column='aaa',這樣會(huì )導致每次執行時(shí)都會(huì )重新分析，浪費CPU和內存資源。

　　15.不要使用in操作符，這樣數據庫會(huì )進(jìn)行全表掃描，

　　推薦方案：在業(yè)務(wù)密集的SQL當中盡量不采用IN操作符

　　16.not in 使用not in也不會(huì )走索引

　　推薦方案：用not exists或者(外聯(lián)結+判斷為空)來(lái)代替

　　17.> 及 < 操作符(大于或小于操作符)

　　大于或小于操作符一般情況下是不用調整的，因為它有索引就會(huì )采用索引查找，但有的情況下可以對它進(jìn)行優(yōu)化，如一個(gè)表有100萬(wàn)記錄，一個(gè)數值型字段 A,30萬(wàn)記錄的A=0,30萬(wàn)記錄的A=1,39萬(wàn)記錄的A=2,1萬(wàn)記錄的A=3.那么執行A>2與A>=3的效果就有很大的區別了，因為A>2時(shí)ORACLE會(huì )先找出為2的記錄索引再進(jìn)行比較，而A>=3時(shí)ORACLE則直接找到=3的記錄索引。

　　18.UNION操作符

　　UNION在進(jìn)行表鏈接后會(huì )篩選掉重復的記錄，所以在表鏈接后會(huì )對所產(chǎn)生的結果集進(jìn)行排序運算，刪除重復的記錄再返回結果。實(shí)際大部分應用中是不會(huì )產(chǎn)生重復的記錄，最常見(jiàn)的是過(guò)程表與歷史表UNION.如：

　　select * from gc_dfys

　　union

　　select * from ls_jg_dfys

　　這個(gè)SQL在運行時(shí)先取出兩個(gè)表的結果，再用排序空間進(jìn)行排序刪除重復的記錄，最后返回結果集，如果表數據量大的話(huà)可能會(huì )導致用磁盤(pán)進(jìn)行排序。

　　推薦方案：采用UNION ALL操作符替代UNION,因為UNION ALL操作只是簡(jiǎn)單的將兩個(gè)結果合并后就返回。

　　19.WHERE后面的條件順序影響

　　WHERE子句后面的條件順序對大數據量表的查詢(xún)會(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è)字段都沒(méi)進(jìn)行索引，所以執行的時(shí)候都是全表掃描，第一條SQL的dy_dj = '1KV以下'條件在記錄集內比率為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.查詢(xún)表順序的影響

　　在FROM后面的表中的列表順序會(huì )對SQL執行性能影響，在沒(méi)有索引及ORACLE沒(méi)有對表進(jìn)行統計分析的情況下ORACLE會(huì )按表出現的順序進(jìn)行鏈接，由此因為表的順序不對會(huì )產(chǎn)生十分耗服務(wù)器資源的數據交叉。(注：如果對表進(jìn)行了統計分析，ORACLE會(huì )自動(dòng)先進(jìn)小表的鏈接，再進(jìn)行大表的鏈接)

【Oracle認證：Oracle避免全表掃描方式】相關(guān)文章：

oracle sysdba級用戶(hù)的認證方式01-21

Oracle認證：ORACLE綁定變量BINDPEEKING03-08

Oracle認證作用03-19

Oracle認證簡(jiǎn)介11-30

Oracle最新認證03-09

Oracle認證途徑03-20

Oracle認證：Oracle控制件文件修復03-18

Oracle認證：Oracle內存結構研究-PGA篇03-08

Oracle認證職業(yè)前景03-19