SQL編寫及其優(yōu)化培訓(xùn)

1、軟腦軟件北京公司S QL 編 寫 及 其 優(yōu) 化 培 訓(xùn)溫有飄2005年4月28日本培訓(xùn)包含的內(nèi)容如下： 41. 表、索引相關(guān)知識(shí)及其在數(shù)據(jù)庫(kù)內(nèi)部相關(guān)的物理存儲(chǔ) 41.1. 數(shù)據(jù)庫(kù)中最小的物理存儲(chǔ)單位：塊 (block) 41.2. 表數(shù)據(jù)在塊中的存儲(chǔ)以及RowId 信息 41.3. 索引 41.4. Bitmap 索引介紹 52. 數(shù)據(jù)庫(kù)查詢操作的內(nèi)部處理過(guò)程 62.1. 數(shù)據(jù)庫(kù)的數(shù)據(jù)讀取是以塊為單位的 62.2. 查詢操作內(nèi)部過(guò)程以及索引的作用 62.3. 執(zhí)行計(jì)劃概念 72.4. 排序處理過(guò)程以及各種引起排序的操作 72.5. 多表join 操作的內(nèi)部過(guò)程 73. 寫 SQL 語(yǔ)句時(shí)在性

2、能方面的目標(biāo) 83.1. select 列表中減少不需要的數(shù)據(jù) 83.2. from列表中去除不需要的表 83.3. where 條件中應(yīng)該考慮到索引的使用，防止一些寫法 83.3.1. 防止過(guò)濾字段中套用函數(shù)，如果必須，則考慮函數(shù)索引 83.3.2. 防止把列放入表達(dá)式中去比較 93.4. 防止不需要的排序 93.5. 防止數(shù)據(jù)類型的隱式轉(zhuǎn)換 93.6. 程序代碼中的注意點(diǎn) 103.6.1. 由多個(gè) SQL 語(yǔ)句完成的一個(gè)操作盡可能寫成一個(gè)SQL 就完成， 防止分解103.6.2. 使用數(shù)據(jù)庫(kù)提供的約束來(lái)判斷某些錯(cuò)誤，如唯一性104. SQL 語(yǔ)句知識(shí) 114.1. SQL 語(yǔ)句串講 114

3、.1.1. select f1 from A 114.1.2. select f1 from A where f2=v_1 114.1.3. select f1 from A order by f2 114.1.4. select sum(f1) from A 124.1.5. select f2,sum(f1) from A group by f2 124.1.6. select a_f2,b_f2 from A,B where A.f1=B.f1 124.1.7. Select * from report where SALES_CODE in (select SALES_CODE fro

4、manken where：.); 144.1.8. Select * from report where SALES_CODE exists (select 1 from ankenwhere );144.1.9. in,exists 的選擇 144.1.10. select yadfw as t1,kopeew as t2 From A union all select uufger as t1,poeprvcx as t2 from B 144.2. 幾種常見(jiàn)操作或函數(shù) 154.2.1. 比較符 like 154.2.2. is null ，isnot null 154.2.3. to_c

5、har,to_date,to_number 154.2.4. nvl 154.2.5. 其他 165. 表設(shè)計(jì)的一些建議 16參考文獻(xiàn) 16本培訓(xùn)包含的內(nèi)容如下：表、索引相關(guān)知識(shí)及其在數(shù)據(jù)庫(kù)內(nèi)部相關(guān)的物理存儲(chǔ)；數(shù)據(jù)庫(kù)查詢操作的內(nèi)部處理過(guò)程；寫SQL語(yǔ)句時(shí)在性能方面的目標(biāo)；SQL語(yǔ)句知識(shí)；表設(shè)計(jì)的一些建議下面闡述的內(nèi)容以 Oracle為例來(lái)講述的，在排版方面可能有不妥當(dāng)?shù)牡胤?，或有些?nèi)容前 后重復(fù)，但各種知識(shí)應(yīng)該比較容易理解和接受；1 .表、索引相關(guān)知識(shí)及其在數(shù)據(jù)庫(kù)內(nèi)部相關(guān)的物理存儲(chǔ)1.1. 數(shù)據(jù)庫(kù)中最小的物理存儲(chǔ)單位：塊(block)數(shù)據(jù)庫(kù)最小的物理存儲(chǔ)單位是塊9i默認(rèn)是8k，每個(gè)塊只能屬于一

6、個(gè)表；一行數(shù)據(jù)插入時(shí)會(huì)占用塊中的一些存儲(chǔ)，當(dāng)塊不能再容納新數(shù)據(jù)時(shí)，則將啟用新的塊存儲(chǔ)數(shù)據(jù);數(shù)據(jù)緩沖池1.2. 表數(shù)據(jù)在塊中的存儲(chǔ)以及 RowId信息表的字段除了設(shè)計(jì)者設(shè)計(jì)的字段外， 還有個(gè)字段Rowid ; RowId是oracle為每個(gè)表自動(dòng) 增加的一個(gè)字段。當(dāng)表中插入一行記錄時(shí)，此記錄在塊就有一個(gè)唯一的物理位置， 這個(gè)位置信息就保存在此行記錄的RowId字段；1.3. 索引表中記錄的存儲(chǔ)順序是以先后插入順序存儲(chǔ)的，在一個(gè)數(shù)據(jù)量很大的表中， 如果不引入其他的手段，每次查找小部分記錄都是從第一條掃描到最后一條，這樣，系統(tǒng)將慢得不能使用；使用索引可以有效解決問(wèn)題；如圖：Report表(100萬(wàn)條

7、記錄)RowIdSession_codeSales_codeEmployee_codeJAaNs1AAQAAA7yTAah100061000017AAANslAAYAAAAmWAAX0007100015。 索引Employee_codeRowd_ |517AAANslAAYAAAAmWA aaansiaaqaaATytAA索引的本質(zhì)用途是通過(guò)它使讀取進(jìn)程在掃描源表時(shí)的數(shù)據(jù)塊范圍大大減小了，因此性能 大幅度提高;一般情況下，當(dāng)檢索的記錄與所有記錄數(shù)比較 <=4%時(shí)，索引是很有效的，當(dāng)比例更大 時(shí)，索引反而有可能降低性能， 因?yàn)榧词故褂昧怂饕?讀取進(jìn)程還是掃描了大部分的表 中的塊，如果這樣

8、，倒不如不用索引直接掃描源表，因?yàn)闇p少了讀取索引的塊的開(kāi)銷；當(dāng)索引建好后，oracle優(yōu)化器在執(zhí)行sql時(shí)會(huì)選擇是否使用索引，所以設(shè)計(jì)表者不用擔(dān) 憂此索引會(huì)影響檢索性能；創(chuàng)建索引的標(biāo)準(zhǔn)是那一列是否經(jīng)常在where條件中出現(xiàn)，否則不應(yīng)該建立，因?yàn)?，每次表記錄的增加、刪除以及修改那個(gè)字段值時(shí)，還要維護(hù)索引，增加了開(kāi)銷；此節(jié)講的索引叫 B_tree索引，在內(nèi)部存儲(chǔ)中類似樹(shù)狀結(jié)構(gòu)，有枝和葉，枝是 oracle內(nèi) 部存儲(chǔ)的一些連接數(shù)據(jù)，葉才存儲(chǔ)實(shí)際的值，如圖：表中每條記錄只要那個(gè)列是非NULL值，在索引中都有一個(gè)條目entry來(lái)存儲(chǔ)如上圖，類似于表在塊中一條條記錄存儲(chǔ)；1.4. Bitmap索引介紹當(dāng)表

9、中某列的distinct值比較少時(shí)，使用 B_tree索引效率就不高了，因?yàn)橐源肆衼?lái)做 where條件過(guò)濾的話，結(jié)果集很可能超過(guò)總記錄數(shù)的4%,正如前面說(shuō)過(guò)的，超過(guò) 4%記錄使用B_tree索引，效率反而下降。使用Bitmap位圖索引可以解決這個(gè)問(wèn)題；Bitmap索引使用一個(gè)位圖來(lái)記錄數(shù)據(jù)情況，舉例：商品表product,有一個(gè)字段color存儲(chǔ)顏色值，在所有商品中最多就10種不同的顏色,創(chuàng)建的位圖圖示如下：Indexstart ROWIDA A AK Al A AI A ft AC AAAROWIDAMKMhAIAAAFNOARq AAAKAIAMAMFNQARq aAAEAI AAIAAA

10、F HQ ABq kAAIAlbitmap100010010GQ1001D1CO> OCOIOIQDOOI0Q100QC0> 310OOCOailQO0QO10Cl> ooiooaioooooiooooio?其中，Start ROWID是表中的第一條記錄的物理地址，end ROWID是表中最后一條記錄的物理地址，bitmap是一張位圖，存儲(chǔ)一連串的0或1;列值為blue的bitmap中，每個(gè)bit的位置在源表中都能找到某個(gè)位置與它一一對(duì)應(yīng)，它們?cè)诟髯詫?duì)象中的相對(duì)位置一樣。bit值為1時(shí)，則源表對(duì)應(yīng)位置那個(gè) rowid所在的記錄的color字段值為blue',為0時(shí)，則

11、非blue'值；類似的，Green, Red, Yellow 都有各自的bitmap;試想，當(dāng)where條件中有過(guò)濾條件 A and B,條件A可以使用B_tree索引，條件B 可以使用bitmap索引，那么從A條件中可以得到一個(gè) RowId的集合，從B條件也可以 得到RowId結(jié)合，這樣，只要從第一個(gè)rowid集中去除第二個(gè)rowid集中的值，得到結(jié) 果集Rowid ,我們就可以從源表得到數(shù)據(jù)了；Bitmap使用了壓縮技術(shù)，節(jié)約了存儲(chǔ)，并且在一個(gè)bitmap上對(duì)某個(gè)位bit的值是1還是 0時(shí)速度是非?？斓?；2 .數(shù)據(jù)庫(kù)查詢操作的內(nèi)部處理過(guò)程2.1. 數(shù)據(jù)庫(kù)的數(shù)據(jù)讀取是以塊為單位的Or

12、acle讀取表中的數(shù)據(jù)時(shí)，是以整個(gè)塊為單位的，有可能一次讀多個(gè)塊；從索引中得到 rowid的物理位置時(shí)，也要把含rowid這個(gè)位置的塊從磁盤中讀出， 然后才處理個(gè)別行；2.2. 查詢操作內(nèi)部過(guò)程以及索引的作用舉仞ij: select * from personal_function where employee_code= -200 ;上述查詢，如果 employee_code上沒(méi)有索引，則會(huì)把 personal_function表數(shù)據(jù)的塊全部 讀入內(nèi)存中，同時(shí)從第一條記錄查到最后一條記錄，對(duì)符合條件的記錄返回給用戶；如果employee_code有索引，則使用索引檢索出含符合條件的Rowid

13、的塊讀入內(nèi)存，然后定位到rowid指示的位置上把記錄返回給用戶；2.3. 執(zhí)行計(jì)劃概念2.2節(jié)中，如果 employee_code=-200的記錄數(shù)在占總記錄數(shù)超過(guò)4%,則有可能就不會(huì)用索引查找，而是直接全表掃描，這是oracle的優(yōu)化器經(jīng)過(guò)各種檢測(cè)后會(huì)自動(dòng)選擇的；Oracle對(duì)已存在數(shù)據(jù)統(tǒng)計(jì)特征會(huì)選擇不同的執(zhí)行路徑或者全表，或者使用索引等，這就產(chǎn)生了不同的執(zhí)行計(jì)劃，使能到達(dá)最大的性能。執(zhí)行計(jì)劃選擇的模式有RBO和CBO方式，采用CBO方式時(shí)（這是oracle公司強(qiáng)烈建議使用的），sql語(yǔ)句中from后表的先后順序以及 where條件中各個(gè)條件的先后順序變得 不是很重要了，如果 RBO方式則不

14、然；CBO全稱 Cost-based Optimizer,基于代價(jià)的優(yōu)化。 2.2節(jié)提到的 SQL語(yǔ)句，Oracle是 否使用索引，其內(nèi)部會(huì)做各種比較，然后自動(dòng)做出選擇；為了使 Oracle做出更精確的 比較，我們應(yīng)該周期性地對(duì)表進(jìn)行統(tǒng)計(jì)，使 Oracle 了解到真實(shí)情況后做出更準(zhǔn)確的判 斷。因?yàn)橐y(tǒng)計(jì)，所以稱之為基于代價(jià)；RBO全稱Rule-based Optimizer,基于規(guī)則，就是說(shuō) Oracle定義的一套執(zhí)行先后順序， 如，有索引則一定會(huì)用索引，就如 2.2節(jié)的SQL語(yǔ)句，但這樣有時(shí)并不是最好的執(zhí)行 選擇。2.4. 排序處理過(guò)程以及各種引起排序的操作舉仞ij: select * fr

15、om personal_function where employee_code= -200 order by func_name;由2.2節(jié)可知，如果沒(méi)有 order by語(yǔ)句時(shí)，oracle只要檢索到一條符合條件的記錄就立 刻返回給用戶，直到所有數(shù)據(jù)返回完畢。當(dāng)有 order by時(shí)，情況就不一樣了，oracle會(huì)把檢索到的每一條記錄先保存在一個(gè)用于排序的內(nèi)存中，當(dāng)所有符合條件的記錄在那個(gè)區(qū)域完成排序后，再返回給用戶；通過(guò)排序這個(gè)操作，oracle中間多處理了一個(gè)步驟。數(shù)據(jù)量越大，排序時(shí)間則更長(zhǎng)，當(dāng) 數(shù)據(jù)量到達(dá)所找的內(nèi)存區(qū)域無(wú)法容納時(shí)，將使用磁盤做為臨時(shí)排序區(qū)，此時(shí)，性能會(huì)大大降低，用戶將

16、等待更長(zhǎng)的時(shí)間才能得到返回的結(jié)果；因此，在一個(gè) SQL語(yǔ)句中，如果對(duì)返回的記錄集沒(méi)有順序要求時(shí)，應(yīng)該去除引起排序 的語(yǔ)句；弓I起排序的操作有：order by, distinct, union , group by2.5. 多表join操作的內(nèi)部過(guò)程舉例說(shuō)明：Esm系統(tǒng)中的3張表結(jié)構(gòu)如下:Employee:EMPLOYEE_CODE職員代碼EMPLOYEE_NAME職員名稱EMPLOYEE_KANA職員全稱Emp_detialEMPLOYEE_CODE職員代碼DepartDepart_CODE部門代碼DEPART_NAME部門名稱DEPART_CODE職員部門代碼現(xiàn)在要求列出所有職員的名稱和其

17、對(duì)應(yīng)的部門名稱，SQL語(yǔ)句如下：select aa.employee_name,cc.depart_namefrom employee aa,emp_detail bb,depart ccwhere aa.employee_code=bb.employee_code and bb.depart_code=cc.depart_code;執(zhí)行時(shí)，oracle可以選擇如下的一個(gè)執(zhí)行計(jì)劃可參考4.1.6節(jié)的流程圖：Employe 表數(shù)據(jù)emp_detaildepart00001張三00002李四00003王五00002100、0000320000001300200開(kāi)發(fā)2部300開(kāi)發(fā)3部100開(kāi)發(fā)1部1

18、) 讀取employee的第一條記錄，得到職員代碼 A;2) 再在emp_detail查找職員代碼為 A的第一條記錄，得到部門代碼 B;3) 在表depart中查找部門代碼為 B的記錄；4)在各個(gè)表的記錄中中取出需要的信息返回給用戶；5) ooo 2步和1步是個(gè)循環(huán)操作，2步嵌套于1步中，直到所有信息返回給用戶；Oracle如果有其他的執(zhí)行記錄更好的話，會(huì)選擇其他的；3. 寫SQL語(yǔ)句時(shí)在性能方面的目標(biāo)3.1. select列表中減少不需要的數(shù)據(jù)select列表需要的信息應(yīng)該按需索取，不能因?yàn)閳D方便用 *'把所有字段內(nèi)容取到客戶端，這樣，既增加服務(wù)器的負(fù)荷，又增加網(wǎng)絡(luò)流量；3.2. f

19、rom列表中去除不需要的表出現(xiàn)在from后的表，oracle都會(huì)對(duì)它檢索并與其他表進(jìn)行join操作，如果把不需要的表不經(jīng)意間放在了 from后面，可能會(huì)增加幾倍甚至幾十倍的負(fù)荷；如果 where條件中也沒(méi)有加上進(jìn)行join的條件，oracle將對(duì)它進(jìn)行笛卡兒乘積的 join ,這種負(fù)荷可想而知；3.3. where條件中應(yīng)該考慮到索引的使用，防止一些寫法3.3.1. 防止過(guò)濾字段中套用函數(shù)，如果必須，則考慮函數(shù)索引舉例：統(tǒng)計(jì)在某天的日?qǐng)?bào)登記個(gè)數(shù)，其中，某天這個(gè)值V_date是從程序外面以參數(shù)的形式傳遞進(jìn)去的字符串，格式如yyyy-mm-dd ;比較兩種寫法：第一種：Select count(*

20、) from report where to_char(day, yyyy-mm-dd )=V_date第二種：Select count(*) from report where day=to_date(V_date ， yyyy-mm-dd ) ；必須選擇第二種，因?yàn)槿绻鹍ay 上有索引，而day 作為函數(shù) to_char 的參數(shù)， oracle將不使用它的索引， 因而進(jìn)行全表掃描， 這樣的后果是， 也許 2 秒內(nèi)就能統(tǒng)計(jì)出來(lái)的結(jié)果，卻可能要花幾十分鐘甚至幾小時(shí)； report 表數(shù)據(jù)量越大，比照結(jié)果就更明顯；有些情況確實(shí)需要使用函數(shù)，如：取出職員名稱為 V_name 的職員代碼，而V_na

21、me是從程序外面?zhèn)鬟M(jìn)去的參數(shù)；由于在最初增加職員記錄時(shí)， 每個(gè)存入的職員名稱沒(méi)有做大小寫轉(zhuǎn)化， 比方， 最初登 陸了一個(gè)ZhanSan'的職員，此時(shí) V_name傳進(jìn)來(lái)的是zhansan,如果用如下語(yǔ)句查詢將得不到記錄：Select employee_code from employee where employee_name=V_name ；可使用如下語(yǔ)句Select employee_code from employee where UPPER(employee_name)= UPPER (V_name)；此時(shí)由于套用了函數(shù)UPPER 而列 employee_code 上的索引將不

22、被使用，可以考慮創(chuàng)建函數(shù)索引，就是把UPPER(employee_name) 當(dāng)成一個(gè)字段去創(chuàng)建索引，當(dāng)查詢時(shí)，則會(huì)使用此函數(shù)索引；3.3.2. 防止把列放入表達(dá)式中去比較請(qǐng)看下面 2 條語(yǔ)句：Select * from A where f1/5>300 ；Select * from A where f1>300*5 ；必須使用第二種，否則，如果f1 有索引，第一種情況將不使用；3.4. 防止不需要的排序2.4 節(jié)中已說(shuō)明排序需要額外的負(fù)荷，所以，當(dāng)不需要排序時(shí)，應(yīng)該防止；3.5. 防止數(shù)據(jù)類型的隱式轉(zhuǎn)換舉例：表 A ，有一字段code ， varchar2 類型，此字段存儲(chǔ)的值都

23、是由數(shù)字組成的串；SQL 語(yǔ)句：Select * from A where code=v_number ； 其中， v_number 是由程序外面?zhèn)魅氲臄?shù)值；因?yàn)?v_number 是數(shù)值，而code 是 varchar2 類型，所以， Oracle 會(huì)進(jìn)行類型的隱式轉(zhuǎn)換，把此語(yǔ)句轉(zhuǎn)化成如下形式執(zhí)行：Select * from A where to_number(code)= v_number ；把列嵌套在函數(shù)里索引不能使用 ，這個(gè)是我們不期望的，所以正確的做法是我們應(yīng)該進(jìn)行顯式的轉(zhuǎn)換：Select * from A where code=to_char(v_number) ；3.6. 程序代

24、碼中的注意點(diǎn)3.6.1. 由多個(gè) SQL 語(yǔ)句完成的一個(gè)操作盡可能寫成一個(gè)SQL 就完成，防止分解舉個(gè)例子：需要得到 2004 年中，每個(gè)季度(1-3 月， 4-6 月， 7-9 月， 10-12 月 ) 的日?qǐng)?bào)個(gè)數(shù)；第一種方法：比較容易想到的，用 4 個(gè) SQL 語(yǔ)句分別去得到各個(gè)季度的統(tǒng)計(jì)：Select count(*) from report where day>=to_date(2004-01-01 , yyyy-mm-dd ) and day<=to_date( 2004-03-31 23:59:59 ,yyyy-mm-dd hh24:mi:ss ) ；Select co

25、unt(*) from report where day>=to_date(2004-04-01 , yyyy-mm-dd ) and day<=to_date( 2004-06-30 23:59:59 ,yyyy-mm-dd hh24:mi:ss ) ；Select count(*) from report where day>=to_date(2004-07-01 , yyyy-mm-dd ) and day<=to_date( 2004-09-30 23:59:59 ,yyyy-mm-dd hh24:mi:ss ) ；Select count(*) from re

26、port where day>=to_date(2004-10-01 , yyyy-mm-dd ) and day<=to_date( 2004-12-31 23:59:59 ,yyyy-mm-dd hh24:mi:ss ) ；第二種方法：一個(gè)SQL 語(yǔ)句完成SelectCount(case when day>=to_date( 2004-01-01 , yyyy-mm-dd ) to_date( 2004-03-31 23:59:59 ,yyyy-mm-dd hh24:mi:ss ) as one,andday<=Count(case when day>=to_

27、date( 2004-04-01 , yyyy-mm-dd ) to_date( 2004-06-30 23:59:59 ,yyyy-mm-dd hh24:mi:ss ) )as second,andday<=Count(case when day>=to_date( 2004-07-01 , yyyy-mm-dd ) to_date( 2004-09-30 23:59:59 ,yyyy-mm-dd hh24:mi:ss )as third,andday<=Count(case when day>=to_date( 2004-10-01 , yyyy-mm-dd )an

28、dday<=to_date( 2004-12-31 23:59:59 ,yyyy-mm-dd hh24:mi:ss )as forth,From report ；顯然，第二種方法可以提高4 倍的性能；要到達(dá)這種轉(zhuǎn)化，編碼人員需要對(duì)數(shù)據(jù)庫(kù)提供的 SQL 函數(shù)有充分的認(rèn)識(shí)；3.6.2. 使用數(shù)據(jù)庫(kù)提供的約束來(lái)判斷某些錯(cuò)誤，如唯一性有種情況，應(yīng)用程序中經(jīng)常出現(xiàn)，舉個(gè)例子：有張全國(guó)居民身份表resident， 其中有個(gè)字段身份證號(hào)碼ID_card ， 是主鍵， 在登記一個(gè)居民時(shí)，為了防止發(fā)生主鍵沖突，應(yīng)用程序往往保存之前用一個(gè)SqL 語(yǔ)句去查此ID_card 號(hào)是否已存在，存在則提示ID_car

29、d 沖突。上述情況完全可以防止使用寫 SQL 語(yǔ)句的方法去查，因?yàn)?oracle 的每個(gè)錯(cuò)誤都有一個(gè)唯一的錯(cuò)誤號(hào)，只要在程序中去捕捉違例 (exception) ，然后進(jìn)行比照是否是主鍵沖突即可；有些時(shí)候非主鍵的字段也要保持唯一性， 則可以使用唯一索引。 當(dāng)發(fā)生唯一性沖突時(shí)，也有一個(gè)錯(cuò)誤號(hào)；Oracle 在自身版本升級(jí)的時(shí)候，錯(cuò)誤號(hào)的含義是向后兼容的；4. SQL 語(yǔ)句知識(shí)4.1. SQL 語(yǔ)句串講4.1.1. select f1 from A這是一個(gè)最簡(jiǎn)單的 SqL 語(yǔ)句，想象一下A 表的塊讀入內(nèi)存，然后從第1 條記錄掃描到最后一條記錄且每次立刻把f1 字段值返回給用戶，這個(gè)過(guò)程是全表掃描；

30、Sql 語(yǔ)句中，有列別名和表別名，對(duì)很長(zhǎng)表名或列名可以簡(jiǎn)化操作，如：表別名： Select aa. STATUS from REGULAR_REPORT_CONTENTS aa ；表別名前不應(yīng)該使用as， SQL SERVER 支持，但 Oracle 里不支持；列別名： Select MODIFIED_STATUS_CODE_OUT as mod from TIMEBOOK_LOG;程序代碼中引用的時(shí)候就可以使用 mod ，而非長(zhǎng)串；如果在連接表中有同名表的時(shí)候，別名是必須的；4.1.2. select f1 from A where f2=v_1如果 f2 無(wú)索引，則第4 1 1 節(jié)中每次掃

31、描到一條記錄時(shí)會(huì)根據(jù)條件進(jìn)行過(guò)濾，符合條件則立即返回給用戶，否則掃描下一條；F2 有索引且使用時(shí)，則從索引開(kāi)始掃描，掃描時(shí)不象表一樣從第一條目掃描到最后一條， oracle 會(huì)根據(jù)內(nèi)部算法很快定位到符合條件的條目， 讀取 Rowid 后根據(jù)物理地址定位到源表的記錄上立即返回給用戶， 然后再在索引中定位到第二個(gè)符合條件的條目，直到全部；4.1.3. select f1 from A order by f2在 4.1.1 節(jié)中講到的每掃描的記錄立即返回給用戶，而當(dāng)有排序時(shí)，則先發(fā)送到一個(gè)排序區(qū)中進(jìn)行排序，等所有記錄掃描完后，再把排序區(qū)中的數(shù)據(jù)集返回給用戶；當(dāng) select 后帶 distinct

32、時(shí)， 內(nèi)部處理也有排序操作， 更靠近 distinct 的字段就更先排序，同時(shí)在排序過(guò)程發(fā)現(xiàn)有各個(gè)字段信息完全相同的行時(shí)， 只留一條記錄， 廢除其他相同的；排序完成后，再把排序區(qū)中的數(shù)據(jù)集返回給用戶；4.1.4. select sum(f1) from Aoracle 處理時(shí)類似這樣：使用一個(gè)變量V ，初始值0，每次掃描一個(gè)記錄時(shí)，v=v+f1 ，直到掃描到最后，把v 返回給用戶；類似的，函數(shù)avg,max,min,count 原理差不多，如果f1 有索引時(shí)，可能會(huì)掃描索引而不掃描表，因?yàn)樗饕膲K更少，而且包含了需要的全部信息；4.1.5. select f2,sum(f1) from A g

33、roup by f2上節(jié)中是把f1 值加如到一個(gè)變量中，這里多了一個(gè)字段，可以這樣理解：每掃描一條記錄時(shí)，如果f2 值未出現(xiàn)過(guò)，則定義一個(gè)數(shù)組變量A ， A1=f1 ， A2=f2 ；如果 f2值以前出現(xiàn)過(guò)，則把當(dāng)前f1 值增加到對(duì)應(yīng)的數(shù)組變量中，這樣，直到表掃描完畢，按 f2 字段分組的各個(gè)統(tǒng)計(jì)值都記錄在了一堆的數(shù)組中， 最后， oracle 把數(shù)組的各個(gè)值按記錄的方式返回個(gè)用戶；Oracle 在處理每當(dāng)一個(gè)新的 f2 的值出現(xiàn)時(shí)， 對(duì)應(yīng) f2 的數(shù)組變量都會(huì)按f2 值由小到大排序，因此，排序結(jié)果集中是按f2 排序的，用戶無(wú)須再排序；分組語(yǔ)法中，在select列表中出現(xiàn)的非統(tǒng)計(jì)字段，者B要在

34、group by后出現(xiàn)；有時(shí)候需要對(duì)統(tǒng)計(jì)的結(jié)果進(jìn)行過(guò)濾，例如 select f2,sum(f1) from A group by f2 中，用戶只需要 sum(f1)>10000 的組的統(tǒng)計(jì)，這時(shí)用 having 過(guò)濾條件，如下：select f2,sum(f1) from A group by f2 having sum(f1)>10000where 條件是針對(duì)記錄的過(guò)濾，被過(guò)濾掉的數(shù)據(jù)不參與統(tǒng)計(jì)。 having 是針對(duì)統(tǒng)計(jì)完成后，統(tǒng)計(jì)結(jié)果返回給用戶時(shí)的過(guò)濾；4.1.6. select a_f2,b_f2 from A,B where A.f1=B.f1oracle 對(duì)連接 jo

35、in 的操作的內(nèi)部處理過(guò)程，可以參考下列圖：上圖的流程只是執(zhí)行計(jì)劃當(dāng)中的一種，但可以借助于理解， 當(dāng)多于2張表時(shí)，情形是上述的連接也稱內(nèi)連接，其他連接還有左連接，右連接和全表連接的方式;左連接：select a_f2,b_f2 from A,B where A.f1=B.f1+，必須首先掃描 A 表以驅(qū)動(dòng) B掃描。此時(shí)，A標(biāo)示的地方，如果針對(duì) A表的一條記錄，在 B表中1條也沒(méi)有找到 符合條件的記錄，則會(huì)把 A.f1值和以NULL值作為B.f1的值返回給用戶；這樣， A 表中的所有記錄的且在select列表中的信息都會(huì)返回給用戶；右連接：select a_f2,b_f2 from A,B wh

36、ere A.f1+=B.f1 ,首先掃描 B 表以驅(qū)動(dòng) A 掃描，其他操作可參考左連接；全連接：select a_f2,b_f2 from A full outer join B on A.f1=B.f1 ;它的記錄集是 select a_f2,b_f2 from A,B where A.f1=B.f1的結(jié)果集加上A表中有，但在 B表中未找到符合條件的記錄的集合B表中的信息是以Null值返回，再加上B表中有，但A表中未找到符合條件的記錄的集合A表中的信息是以 Null值返回；各種連接通用寫法是:內(nèi)連接： select a_f2,b_f2 from A,B where A.f1=B.f1 或 s

37、elect a_f2,b_f2 from A inner joinB onA.f1=B.f1( 此標(biāo)準(zhǔn)在 symfoware 不支持，奇怪!)左連接：selecta_f2,b_f2 from A left outer join B on A.f1=B.f1右連接：selecta_f2,b_f2 from A right outer join B on A.f1=B.f1全連接：selecta_f2,b_f2 from A full outer join B on A.f1=B.f1 ；在symfoware 中不支持4.1.7. Select * from report where SALES_

38、CODE in (select SALES_CODE from anken where )；上節(jié)講的所有的表都在from 后的列表中，數(shù)據(jù)源都是直接的表，不含子查詢，而這條是典型的含子查詢的語(yǔ)句，內(nèi)部執(zhí)行時(shí)，可以有這種方式：首先，執(zhí)行子查詢語(yǔ)句，把結(jié)果集臨時(shí)保存，作為外層查詢的一個(gè)數(shù)據(jù)源；其次，類似4.1.6 節(jié)中的流程圖，先檢索report 表第一條記錄，然后再檢索上一個(gè)步驟得到的數(shù)據(jù)源，如果包含sales_code值，則返回report當(dāng)前檢索到的記錄，如果沒(méi)有，則 report 繼續(xù)掃描下一條記錄，重復(fù)上步操作，直到表的結(jié)尾；很多含子查詢的語(yǔ)句 oracle 內(nèi)部處理的時(shí)候會(huì)先轉(zhuǎn)換成等價(jià)

39、的不含子查詢的語(yǔ)句去執(zhí)行，如：Select A.f1,B.f1 from A,(select * from C where C.f3=V_1) B where A.f2=B.f2;轉(zhuǎn)換成：Select A.f1,B.f1 from A,C where A.f2=C.f2 and C.f3=V_1;4.1.8. Select * from report where SALES_CODE exists (select 1 from anken where )；Oracle 先掃描 report 的一個(gè)記錄， 然后去執(zhí)行里層子查詢里的語(yǔ)句， 只要能檢索到一條記錄，則立即返回給外層一個(gè)true 的值，

40、 report 的當(dāng)前記錄就可以返回給用戶，掃描一下一條記錄；如果子查詢執(zhí)行完畢都還沒(méi)有檢索到一條記錄，則返回 false 給外層，則表示report 當(dāng)前記錄不符合條件，則繼續(xù)掃描下一條記錄，直到結(jié)尾；4.1.9. in,exists 的選擇一般情況下， in 的子查詢都可以寫成exists 的子查詢，以下兩種選擇可以優(yōu)化性能：1) 當(dāng)內(nèi)層子查詢的過(guò)濾條件有很強(qiáng)的過(guò)濾作用時(shí)，考慮使用in ，因?yàn)閮?nèi)層子查詢返回的數(shù)據(jù)源比較少；2) 當(dāng)外層查詢的過(guò)濾條件有很強(qiáng)的過(guò)濾作用時(shí)，且內(nèi)存查詢可以使用索引快速定位，則考慮使用 exists ；4.1.10. select yadfw as t1,kopee

41、w as t2 From A union all select uufger as t1,poeprvcx as t2 from Bunion all 前后是兩個(gè)獨(dú)立的查詢，它的作用是合并2 個(gè)記錄集。內(nèi)部處理時(shí)， oracle先執(zhí)行 union all 之前的查詢返回記錄給用戶，執(zhí)行完畢后，接著執(zhí)行union all 后面的查詢，把查詢結(jié)果返回給用戶；應(yīng)用程序中， 有很多報(bào)表或網(wǎng)格顯示的上下行內(nèi)容來(lái)自于互不相干的表， 但由于使用綁定數(shù)據(jù)源的方式，顯示數(shù)據(jù)只能由一次SQL 語(yǔ)句檢索得到，此時(shí)union all 就派上用場(chǎng)了；Union all 兩端查詢的字段個(gè)數(shù)以及對(duì)應(yīng)的數(shù)據(jù)類型要一致；Uni

42、on 也有把多個(gè)查詢的記錄集合并的功能， 但它與 union all 的不同之處是， 當(dāng) oracle 檢索到的每條記錄時(shí)不會(huì)馬上返回給用戶， 會(huì)把它送到一個(gè)排序區(qū)中進(jìn)行排序， 排序 中以字段在select列表中更靠前的則先排序，當(dāng)不同記錄中各個(gè)字段信息全部相同時(shí)，oracle 只留下一條記錄，廢除其他相同記錄；所以，當(dāng)不需要排序且允許相同記錄存在時(shí)，使用 union all 性能比 union 好；4.2. 幾種常見(jiàn)操作或函數(shù)4.2.1. 比較符 like這是一個(gè)模糊查詢操作的字符串比較符，如 where A like Ww%',通配任意字符的符號(hào)%,如果單個(gè)字符通配是_ 。作比較的

43、串中，如果 % 或_放于最前面，oracle 將不用索引；4.2.2. is null ， is not null當(dāng)比較一個(gè)字段是否為 null 或非 null 時(shí)，應(yīng)該使用 where f1 is null 或 where f1 is not null ，而不能用=或<> 比較符；任何值與 Null 做=,>,< ， <> 比較時(shí)，都返回false ；任何值與null 進(jìn)行+-*/數(shù)字運(yùn)算操作時(shí)，得到的還是null ；字符串與null 值使用 |連接還是那個(gè)字符串值；4.2.3. to_char,to_date,to_numberto_char 可以把一個(gè)數(shù)字值轉(zhuǎn)換成字符串，這個(gè)簡(jiǎn)單，不多講；to_char 把日期型轉(zhuǎn)換成字符串時(shí)， 最通用的格式 yyyy-mm-dd hh24:mi:ss ， 其中 yyyy是 4 字符年份， mm 是 2 字符月份， dd 是 2 字符日期， hh24 是 24 小時(shí)制的時(shí)間，如果12小時(shí)制則用hh, mi是2字符分鐘，ss是2字符秒數(shù).格式可以靈活使用