SQLO優(yōu)化簡介參考模板

1、寫出高效的SQL在多數(shù)情況下，Oracle使用索引來更快地遍歷表，優(yōu)化器主要根據(jù)定義的索引來提高性能。但是，如果在SQL語句的where子句中寫的SQL代碼不合理，就會造成優(yōu)化器刪去索引而使用全表掃描，一般就這種SQL語句就是所謂的劣質(zhì)SQL語句。在編寫SQL語句時我們應(yīng)清楚優(yōu)化器根據(jù)何種原則來刪除索引，這有助于寫出高性能的SQL語句。1. IS NULL 與 IS NOT NULL避免在索引中使用任何可以為空的列，ORACLE將無法使用該索引。 對于單列索引，如果列包含空值，索引中將不存在此記錄； 對于復(fù)合索引，如果每個列都為空，索引中同樣不存在此記錄； 如果至少有一個列不為空，則記錄存在于

2、索引中。 舉例: 如果唯一性索引建立在表的A列和B列上, 并且表中存在一條記錄的A,B值為(123,null)， ORACLE將不接受下一條具有相同A,B值（123,null）的記錄(插入)， 然而如果所有的索引列都為空，ORACLE將認(rèn)為整個鍵值為空而空不等于空。 因此你可以插入1000 條具有相同鍵值的記錄,當(dāng)然它們都是空! 因?yàn)榭罩挡淮嬖谟谒饕兄?所以WHERE子句中對索引列進(jìn)行空值比較將使ORACLE停用該索引。不能用null作索引，任何包含null值的列都將不會被包含在索引中。即使索引有多列這樣的情況下，只要這些列中有一列含有null，該列就會從索引中排除。也就是說如果某列存在空值

3、，即使對該列建索引也不會提高性能。任何在where子句中使用is null或is not null的語句優(yōu)化器是不允許使用索引的。注意：如果我們必須要用 is null，又需要提供查詢效率 可以用函數(shù)索引實(shí)例如下：create table test_date (name varchar2(20),day date);insert into test_date(name ,day) values (lucy,null);insert into test_date(name ,day) values (jony,null);insert into test_date(name,day) value

4、s (james,sysdate);select * from test_date;-創(chuàng)建decode函數(shù)索引來代替 create index finx_day on test_date(decode(day,null,N, Y)-使用decode判斷來代替is null判斷select * from test_date a where decode(day,null,N,Y) = NSELECT STATEMENT, GOAL = CHOOSE TABLE ACCESS FULL Object owner=SP2 Object name=TEST_DATE 注意 要使用cost 才會使用fu

5、nction index1 / 8analyze table test_date compute statistics for table for all indexes for all indexed columns; select * from test_date a where decode(day,null,N,Y) = NSELECT STATEMENT, GOAL = CHOOSE Cost=1 Cardinality=1 Bytes=11TABLE ACCESS BY INDEX ROWID Object owner=SP2 Object name=TEST_DATE Cost=

6、1 Cardinality=1 Bytes=11 INDEX RANGE SCAN Object owner=SP2 Object name=FINX_DAY Cost=2 Cardinality=1 2. 聯(lián)接列對于有聯(lián)接的列，即使最后的聯(lián)接值為一個靜態(tài)值，優(yōu)化器是不會使用索引的。我們一起來看一個例子，假定有一個職工表（employee），對于一個職工的姓和名分成兩列存放（FIRST_NAME和LAST_NAME），現(xiàn)在要查詢一個叫比爾.克林頓（Bill Cliton）的職工。下面是一個采用聯(lián)接查詢的SQL語句，select * from employs where first_name|

7、|last_name =Beill Cliton上面這條語句完全可以查詢出是否有Bill Cliton這個員工，但是這里需要注意，系統(tǒng)優(yōu)化器對基于last_name創(chuàng)建的索引沒有使用。當(dāng)采用下面這種SQL語句的編寫，Oracle系統(tǒng)就可以采用基于last_name創(chuàng)建的索引。Select * from employee wherefirst_name =Beill and last_name =Cliton遇到下面這種情況又如何處理呢？如果一個變量（name）中存放著Bill Cliton這個員工的姓名，對于這種情況我們又如何避免全程遍歷，使用索引呢？可以使用一個函數(shù)，將變量name中的姓和名

8、分開就可以了，但是有一點(diǎn)需要注意，這個函數(shù)是不能作用在索引列上。下面是SQL查詢腳本：select * from employeewherefirst_name = SUBSTR(&name,1,INSTR(&name, )-1)andlast_name = SUBSTR(&name,INSTR(&name, )+1)注：substr和instr用法1)、substr(string string, int a, int b) 參數(shù)1:string 要處理的字符串 參數(shù)2：a 截取字符串的開始位置（起始位置是0） 參數(shù)3：b 截取的字符串的長度(而不是字符串的結(jié)束位置) 例如： substr(A

9、BCDEFG, 0); /返回：ABCDEFG，截取所有字符 substr(ABCDEFG, 2); /返回：CDEFG，截取從C開始之后所有字符 substr(ABCDEFG, 0, 3); /返回：ABC，截取從A開始3個字符 substr(ABCDEFG, 0, 100); /返回：ABCDEFG，100雖然超出預(yù)處理的字符串最長度，但不會影響返回結(jié)果，系統(tǒng)按預(yù)處理字符串最大數(shù)量返回。 substr(ABCDEFG, 0, -3); /返回：EFG，注意參數(shù)-3，為負(fù)值時表示從尾部開始算起，字符串排列位置不變。 2)、instr的語法如下：instr( string1, string2

10、, start_position , nth_appearance )函數(shù)說明：該函數(shù)返回參數(shù)string2在參數(shù)string1中的位置。start_position參數(shù)表示將從string1的第幾個字符開始來查找string2。該參數(shù)為可選參數(shù)，如果省略，那么默認(rèn)為1。instr函數(shù)默認(rèn)的查找順序?yàn)閺淖蟮接?。?dāng)該參數(shù)為負(fù)數(shù)的時候，則從右邊開始查找。nth_appearance表示返回第幾次出現(xiàn)的string2的位置。該參數(shù)為可選參數(shù)，如果省略則默認(rèn)為1，也就是返回首次出現(xiàn)string2的位置。示例：SELECT INSTR(CORPORATE FLOOR, OR, -1, 1) “aaa”

11、FROM DUAL;aaa-143. 帶通配符（%）的like語句同樣以上面的例子來看這種情況。目前的需求是這樣的，要求在職工表中查詢名字中包含cliton的人?？梢圆捎萌缦碌牟樵僑QL語句：select * from employee where last_name like %cliton%這里由于通配符（%）在搜尋詞首出現(xiàn)，所以O(shè)racle系統(tǒng)不使用last_name的索引。在很多情況下可能無法避免這種情況，但是一定要心中有底，通配符如此使用會降低查詢速度。然而當(dāng)通配符出現(xiàn)在字符串其他位置時，優(yōu)化器就能利用索引。在下面的查詢中索引得到了使用：select * from employee

12、where last_name like c%4. Order by語句ORDER BY語句決定了Oracle如何將返回的查詢結(jié)果排序。Order by語句對要排序的列沒有什么特別的限制，也可以將函數(shù)加入列中（象聯(lián)接或者附加等）。任何在Order by語句的非索引項(xiàng)或者有計(jì)算表達(dá)式都將降低查詢速度。仔細(xì)檢查order by語句以找出非索引項(xiàng)或者表達(dá)式，它們會降低性能。解決這個問題的辦法就是重寫order by語句以使用索引，也可以為所使用的列建立另外一個索引，同時應(yīng)絕對避免在order by子句中使用表達(dá)式。5. NOT 的理想替代方案我們在查詢時經(jīng)常在where子句使用一些邏輯表達(dá)式，如大于

13、、小于、等于以及不等于等等，也可以使用and（與）、or（或）以及not（非）。NOT可用來對任何邏輯運(yùn)算符號取反。下面是一個NOT子句的例子：. where not (status =VALID)如果要使用NOT，則應(yīng)在取反的短語前面加上括號，并在短語前面加上NOT運(yùn)算符。NOT運(yùn)算符包含在另外一個邏輯運(yùn)算符中，這就是不等于（;）運(yùn)算符。換句話說，即使不在查詢where子句中顯式地加入NOT詞，NOT仍在運(yùn)算符中，見下例：. where status INVALID再看下面這個例子：select * from employee where salary3000;對這個查詢，可以改寫為不使用N

14、OT：select * from employee where salary;3000;雖然這兩種查詢的結(jié)果一樣，但是第二種查詢方案會比第一種查詢方案更快些。第二種查詢允許Oracle對salary列使用索引，而第一種查詢則不能使用索引。6. IN和EXISTS(下面有個重復(fù)的)有時候會將一列和一系列值相比較。最簡單的辦法就是在where子句中使用子查詢。在where子句中可以使用兩種格式的子查詢。第一種格式是使用IN操作符：. where column in(select * from . where .);第二種格式是使用EXIST操作符：. where exists (select X

15、from .where .);我相信絕大多數(shù)人會使用第一種格式，因?yàn)樗容^容易編寫，而實(shí)際上第二種格式要遠(yuǎn)比第一種格式的效率高。在Oracle中可以幾乎將所有的IN操作符子查詢改寫為使用EXISTS的子查詢。第二種格式中，子查詢以select X開始。運(yùn)用EXISTS子句不管子查詢從表中抽取什么數(shù)據(jù)它只查看where子句。這樣優(yōu)化器就不必遍歷整個表而僅根據(jù)索引就可完成工作（這里假定在where語句中使用的列存在索引）。相對于IN子句來說，EXISTS使用相連子查詢，構(gòu)造起來要比IN子查詢困難一些。通過使用EXIST，Oracle系統(tǒng)會首先檢查主查詢，然后運(yùn)行子查詢直到它找到第一個匹配項(xiàng)，這就節(jié)

16、省了時間。Oracle系統(tǒng)在執(zhí)行IN子查詢時，首先執(zhí)行子查詢，并將獲得的結(jié)果列表存放在在一個加了索引的臨時表中。在執(zhí)行子查詢之前，系統(tǒng)先將主查詢掛起，待子查詢執(zhí)行完畢，存放在臨時表中以后再執(zhí)行主查詢。這也就是使用EXISTS比使用IN通常查詢速度快的原因。同時應(yīng)盡可能使用NOT EXISTS來代替NOT IN，盡管二者都使用了NOT（不能使用索引而降低速度），NOT EXISTS要比NOT IN查詢效率更高7、Where子句中的連接順序： （CBO根據(jù)對表做的分析進(jìn)行執(zhí)行，RBO根據(jù)索引）ORACLE采用自下而上的順序解析WHERE子句。 根據(jù)這個原理，表之間的連接必須寫在其他WHERE條件之

17、前， 那些可以過濾掉最大數(shù)量記錄的條件必須寫在WHERE子句的末尾。注：RBO CBO 模式Oracle的優(yōu)化器有兩種優(yōu)化方式,即基于規(guī)則的優(yōu)化方式(Rule-Based Optimization，簡稱為RBO)和基于代價(jià)的優(yōu)化方式(Cost-Based Optimization，簡稱為CBO)，在Oracle8及以后的版本,Oracle強(qiáng)列推薦用CBO的方式RBO方式：優(yōu)化器在分析SQL語句時,所遵循的是Oracle內(nèi)部預(yù)定的一些規(guī)則。比如我們常見的，當(dāng)一個where子句中的一列有索引時去走索引。CBO方式：它是看語句的代價(jià)(Cost),這里的代價(jià)主要指Cpu和內(nèi)存。優(yōu)化器在判斷是否用這種方

18、式時,主要參照的是表及索引的統(tǒng)計(jì)信息。統(tǒng)計(jì)信息給出表的大小、有少行、每行的長度等信息。這些統(tǒng)計(jì)信息起初在庫內(nèi)是沒有的，是做analyze后才出現(xiàn)的，很多的時侯過期統(tǒng)計(jì)信息會令優(yōu)化器做出一個錯誤的執(zhí)行計(jì)劃,因些應(yīng)及時更新這些信息。Examda提示：主索引不一定就是優(yōu)的，比如一個表只有兩行數(shù)據(jù)，一次IO就可以完成全表的檢索,而此時走索引時則需要兩次IO,這時全表掃描(full table scan)是最好優(yōu)化模式包括Rule、Choose、First rows、All rows四種方式：Rule：基于規(guī)則的方式。Choolse：默認(rèn)的情況下Oracle用的便是這種方式。指的是當(dāng)一個表或或索引有統(tǒng)計(jì)

19、信息，則走CBO的方式，如果表或索引沒統(tǒng)計(jì)信息，表又不是特別的小，而且相應(yīng)的列有索引時，那么就走索引，走RBO的方式。First Rows：它與Choose方式是類似的，所不同的是當(dāng)一個表有統(tǒng)計(jì)信息時，它將是以最快的方式返回查詢的最先的幾行，從總體上減少了響應(yīng)時間。All Rows:也就是我們所說的Cost的方式，當(dāng)一個表有統(tǒng)計(jì)信息時，它將以最快的方式返回表的所有的行，從總體上提高查詢的吞吐量。沒有統(tǒng)計(jì)信息則走RBO的方式。設(shè)定選用哪種優(yōu)化模式：A、Instance級別我們可以通過在initSID.ora文件中設(shè)定OPTIMIZER_MODE=RULE/CHOOSE/FIRST_ROWS/AL

20、L_ROWS如果沒設(shè)定OPTIMIZER_MODE參數(shù)則默認(rèn)用的是Choose方式。B、Sessions級別通過ALTER SESSION SET OPTIMIZER_MODE=RULE/CHOOSE/FIRST_ROWS/ALL_ROWS來設(shè)定。C、語句級別用Hint（/*+ . */）來設(shè)定為什么表的某個字段明明有索引，但執(zhí)行計(jì)劃卻不走索引？1、優(yōu)化模式是all_rows的方式2、表作過analyze，有統(tǒng)計(jì)信息(最可能的就是統(tǒng)計(jì)信息有誤)3、表很小，上文提到過的，Oracle的優(yōu)化器認(rèn)為不值得走索引。我們可以查看一下一個表或索引是否是統(tǒng)計(jì)信息SELECT * FROM user_tabl

21、esWHERE table_name=AND num_rows is not null;SELECT * FROM user_indexesWHERE table_name=AND num_rows is not null;當(dāng)我們使用CBO的方式,就應(yīng)當(dāng)及時去更新表和索引的統(tǒng)計(jì)信息,以免生形不切合實(shí)的執(zhí)行計(jì)劃。ANALYZE table table_name COMPUTE STATISTICS;ANALYZE INDEX index_name ESTIMATE STATISTICS;8、Select子句中避免使用 “ * ”： 當(dāng)你想在select子句中列出所有的column時,使用動態(tài)SQ

22、L列引用 * 是一個方便的方法。 不幸的是，這是一個非常低效的方法。 實(shí)際上，ORACLE在解析的過程中，會將 * 依次轉(zhuǎn)換成所有的列名, 這個工作是通過查詢數(shù)據(jù)字典完成的, 這意味著將耗費(fèi)更多的時間。9、減少訪問數(shù)據(jù)庫的次數(shù)： 當(dāng)執(zhí)行每條SQL語句時，ORACLE在內(nèi)部執(zhí)行了許多工作: 解析SQL語句、估算索引的利用率、綁定變量、讀數(shù)據(jù)塊等等。 由此可見，減少訪問數(shù)據(jù)庫的次數(shù)，就能實(shí)際上減少ORACLE的工作量。 舉例： 題目我要查找編號為0001、0002學(xué)生的信息。 （低效） select name,age,gender,address from t_student where id =

23、 0001; select name,age,gender,address from t_student where id = 0002; （高效） select ,a.age,a.gender,a.address,,b.age,b.gender,b.address from t_student a,t_student b where a.id = 0001 and b.id = 0002;10、使用Decode函數(shù)來減少處理時間： 使用DECODE函數(shù)可以避免重復(fù)掃描相同記錄或重復(fù)連接相同的表。 舉例： （低效） select count(*), sum(banace

24、) from table1 where dept_id = 0001 and name like anger%; select count(*), sum(banace) from table1 where dept_id = 0002 and name like anger%; （高效） select count(decode(dept_id,0001,XYZ,null) count_01,count(decode(dept_id,0002,XYZ,null) count_02, sum(decode(dept_id,0001,dept_id,null) sum_01,sum(decode(

25、dept_id,0002,dept_id,null) sum_02 from table1 where name like anger%;11、整合簡單,無關(guān)聯(lián)的數(shù)據(jù)庫訪問： 如果你有幾個簡單的數(shù)據(jù)庫查詢語句，你可以把它們整合到一個查詢中(即使它們之間沒有關(guān)系) 舉例： （低效） select name from table1 where id = 0001; select name from table2 where id = 0001; select name from table3 where id = 0001; （高效） select , , from table1 t1, table2 t2, table3 t3 where t1.id(+) = 0001 and t2.id(+) = 0001 and t3.id(+) = 0001 注：上面例子雖然高效，但是可讀性差，需要量情而定啊！12、刪除重復(fù)記錄： 最高效的刪除重復(fù)記錄方法 ( 因?yàn)槭褂昧薘OWID) 舉例： delete from table1 t1 where t1