Oracle與sql簡單優(yōu)化與鎖機(jī)制

Oracle與sql簡單優(yōu)化與鎖機(jī)制淺析系統(tǒng)運(yùn)營二部徐海濤oracle數(shù)據(jù)庫的基本概念與原理對象的存儲，segment、extent、blockSGA、PGA內(nèi)存域，內(nèi)存與存儲的關(guān)系事務(wù)、undo、redo與ORA-01555關(guān)于鎖機(jī)制2023/1/18對象的存儲oracle中的對象以segment的形式存儲。我們可以在dba_segment這張視圖中查詢到所有我們創(chuàng)建的表和索引。segment由extent組成。其擴(kuò)展是以extent為單位。一張表在初始化時會首先產(chǎn)生至少一個設(shè)定大小的extent，以后如果記錄數(shù)逐漸增多，則需要擴(kuò)展segment的空間，每次以設(shè)定大小擴(kuò)展一個extent(即增加一個設(shè)定大小的extent到segment中)。extent由block組成。block是oracle存儲中最基本的單位。一個block上會存儲一條或多條數(shù)據(jù)記錄，讀取一條數(shù)據(jù)記錄時至少需要讀取出這條記錄所在block。在blockheader上記錄了一些非常重要的信息，包含塊的類型(表還是索引)、關(guān)于塊上活動和過時的事務(wù)信息、塊在磁盤上的位置等等。一個segment屬于一個唯一的tablespace，而一個tablespace則可以包含一個或多個數(shù)據(jù)文件。2023/1/18oracle的內(nèi)存結(jié)構(gòu)SGA內(nèi)存域ORACLE使用的所有共享內(nèi)存空間被稱為SGA(systemglobalarea)的內(nèi)存結(jié)構(gòu)SGA主要包含下面的內(nèi)存域：databuffer：用于放置datablock，ORACLE中所有的數(shù)據(jù)操作(增、刪、查、改)都需要在databuffer中完成，讀數(shù)據(jù)時需先將數(shù)據(jù)塊從存儲讀到databuffer，修改數(shù)據(jù)的操作需在databuffer中完成修改然后在回寫存儲。優(yōu)化物理讀的一個辦法就是增大databuffer，使數(shù)據(jù)在databuffer的停留時間變長，提高buffer的命中率，減少物理讀，也就減小了I/O，不過這是不推薦的辦法，最重要的還是要優(yōu)化應(yīng)用。sharedpool：用于放置緩存的sql語句、sql語句的執(zhí)行計劃、數(shù)據(jù)字典視圖等，sql語句執(zhí)行過程中需要保持在sharedpool中的語句本身和其執(zhí)行計劃，dll操作也需要在sharedpool中鎖住相關(guān)的數(shù)據(jù)字典。javapool：用于存放java對象。largepool：用于分配一些大塊的內(nèi)存給進(jìn)程應(yīng)對一些特殊的需要，如語句的并行執(zhí)行和備份會用到largepool，weblogicconnectionpool連接ORACLE數(shù)據(jù)庫也是使用largepool存放connection的相關(guān)信息。redologbuffer：用于緩存redolog，redolog會先緩存到redologbuffer然后再寫到日志組中。2023/1/18oracle的內(nèi)存結(jié)構(gòu)在oracle中幾乎所有操作都是SGA完成的。不論增、刪、查、改都是將需要的數(shù)據(jù)取到SGA中，在SGA中完成相關(guān)的操作。oracle通過后臺進(jìn)程(DBWn)將SGA中產(chǎn)生的變化同步到儲存中，本身并不直接在存儲上進(jìn)行增、刪、查、改的操作。PGA內(nèi)存域針對每個oracle進(jìn)程(process)分配的獨(dú)占內(nèi)存空間被稱為PGA(processglobalarea)的內(nèi)存結(jié)構(gòu)，是在SGA之外獨(dú)立分配的，一般情況下，session越多也就耗用越多的PGA?？傮w而言，PGA中需要關(guān)注的地方不是太多，在9i以上的版本，使用自動內(nèi)存管理，用于hash和排序的內(nèi)存空間從SGA挪到了PGA，為PGA的上限值(pga_aggregate_target)配置一個合理的值對sql語句的效率有較大影響。(oracle中另一部分非常重要的機(jī)制就是oracle中的后臺進(jìn)程，這里我們不作討論，大家可以參看《oracleexpertone-on-one》等相關(guān)的書籍)2023/1/18事務(wù)、undo、redo事務(wù)事務(wù)：單個邏輯工作單元執(zhí)行的一系列操作。事務(wù)遵循如下的特性：原子性：一個事務(wù)要么完全發(fā)生，要么完全不發(fā)生一致性：事務(wù)把數(shù)據(jù)庫從一個一致狀態(tài)轉(zhuǎn)變到另一個狀態(tài)隔離性：在事務(wù)提交以前，其他事務(wù)察覺不到事務(wù)的影響持久性：一旦事務(wù)提交，它是永久的oracle的事務(wù)是隱式開始的，從第一條dml語句開始(第一條取得TX鎖的語句開始的，后面我們將討論oracle的鎖機(jī)制，鎖也是保證事務(wù)性的重要機(jī)制，通過鎖保證了不同事務(wù)不能同時修改同一資源)，到顯式以commit或者rollback結(jié)束。oracle缺省的事務(wù)隔離級別：readcommitted：只能讀到其他事務(wù)已提交的變更，事務(wù)中的每一條語句都遵從語句級的讀一致性(即只能讀到每條語句開始時其他事務(wù)已提交的變更，執(zhí)行過程中其他事務(wù)提交的變更不被體現(xiàn))，保證不會臟讀。2023/1/18事務(wù)、undo、redo事務(wù)需要注意的是完整性約束檢查的點(diǎn)是在語句執(zhí)行結(jié)束的時候開始的，也就是說只要有一行的修改違反完整性約束，則整體條語句失敗。在oracle中頻繁的commit并不是一個良好的習(xí)慣：oracle的所有變化都是在SGA中完成的，然后通過后臺進(jìn)程同步到存儲中；但這一同步過程并不是只在commit的時候才發(fā)生，而是有一定量的數(shù)據(jù)被修改就會發(fā)生；實(shí)際上每次commit的消耗都是比較小的，因?yàn)榇罅啃薷牡臄?shù)據(jù)其實(shí)已經(jīng)寫到存儲中了；過于頻繁的commit反而帶來冗余的checkpoint(簡單來講，檢查內(nèi)存和存儲中的信息是否完全一致，不一致則調(diào)用相關(guān)的同步操作)的消耗；只需要在應(yīng)該commit時候(需要被其他事務(wù)可見的時候)commit。2023/1/18事務(wù)、undo、redoredo所謂重做，顧名思義，就是重新做已經(jīng)做過的動作。redolog(重做日志)對于oracle數(shù)據(jù)庫是至關(guān)重要的，數(shù)據(jù)庫中的所有的改變都會記錄到redolog(比如dml、ddl操作等)，一旦數(shù)據(jù)庫出現(xiàn)故障，oracle能夠根據(jù)redolog“重做”，恢復(fù)到故障前的情況。

由于重做基本上是不能避免的、也不是浪費(fèi)，需要注意數(shù)據(jù)庫過于頻繁的dml操作會帶來大量記錄重做日志的消耗。當(dāng)然這通常只能增加redolog的日志組或者提高archivelog的效率來滿足應(yīng)用的需要。2023/1/18事務(wù)、undo、redoundo撤銷：也就是取消之前的操作，回滾到操作前的情況。oracle對于每次數(shù)據(jù)的修改，都會記錄變化前的數(shù)據(jù)，這個數(shù)據(jù)會記錄在rollbacksegment(回滾段)中。對應(yīng)的dml操作會在改變的datablock和記錄變更前數(shù)據(jù)的rollbackblock產(chǎn)生一個相對應(yīng)的transactionslot，記錄事務(wù)的相關(guān)信息。如果要回滾一個事務(wù)所做的dml操作，oracle根據(jù)該事務(wù)產(chǎn)生的所有transactionslot中的信息，在rollbacksegment中找到變更前的數(shù)據(jù)并回寫到對應(yīng)的datablock即可。(注意這個過程仍是首先在內(nèi)存中完成，然后通過后臺進(jìn)程同步到存儲上)如果事務(wù)沒有結(jié)束，那么這個事務(wù)產(chǎn)生的回滾信息就不能被清理。但是如果事務(wù)已經(jīng)提交或者回滾，那么這個事務(wù)產(chǎn)生的回滾信息就能夠被清理重用。2023/1/18事務(wù)、undo、redoORA-01555由于存在回滾段的循環(huán)使用和讀一致性的關(guān)系，這就使得open過長時間的cursor可能產(chǎn)生ORA-01555:snapshottooold的問題。ORA-01555產(chǎn)生的原因是因?yàn)椴荒茏x取到查詢開始時的數(shù)據(jù)引起。由于讀一致性，sql語句讀取的數(shù)據(jù)必須是查詢開始時的數(shù)據(jù)，在查詢過程中產(chǎn)生的變更不能被這個查詢所讀取。對于cursor而言，就是opencursor的時候?yàn)椴樵冮_始的時候，close是查詢結(jié)束。如果在查詢執(zhí)行或者opencursorfetch的過程中，原來查詢的數(shù)據(jù)有被更改，則這個查詢必須到回滾段中取相關(guān)修改前的數(shù)據(jù)。但因?yàn)榛貪L段是循環(huán)使用的，假設(shè)這個查詢執(zhí)行的時間過長或者opencursor的時間過長，就可能導(dǎo)致查詢過程中被修改的數(shù)據(jù)的回滾信息已經(jīng)被重用(因?yàn)楦倪@些數(shù)據(jù)的事務(wù)已經(jīng)提交了，顯然也不會被查詢阻塞)，不能找到需要的修改前的數(shù)據(jù)，從而發(fā)生ORA-01555。更詳細(xì)可以參見文檔《關(guān)于ORA-01555的成因和應(yīng)對措施.doc》或者其他相關(guān)的資料。2023/1/18關(guān)于于鎖鎖機(jī)機(jī)制制鎖(lock)：：oracle中用用于于保保護(hù)護(hù)資資源源的的共共享享機(jī)機(jī)制制，，對對于于任任何何資資源源、、對對象象的的訪訪問問都都需需要要對對其其進(jìn)進(jìn)行行加加鎖鎖，，用用以以保保護(hù)護(hù)對對資資源源的的并并發(fā)發(fā)訪訪問問時時用用戶戶在在存存取取同同一一數(shù)數(shù)據(jù)據(jù)庫庫對對象象時時的的正正確確性性(即即無無丟丟失失修修改改、、可可重重復(fù)復(fù)讀讀、、不不讀讀““臟臟””數(shù)數(shù)據(jù)據(jù))；；鎖鎖也也是是保保證證oracle事務(wù)務(wù)特特性性的的重重要要機(jī)機(jī)制制，，通通過過鎖鎖機(jī)機(jī)制制保保證證了了不不同同的的事事務(wù)務(wù)不不能能同同時時發(fā)發(fā)起起對對同同一一資資源源的的并并發(fā)發(fā)修修改改。。在oracle中，，鎖鎖簡簡單單來來講講有有兩兩個個維維度度：：一個個是是鎖鎖的的類類別別(lock_type)，，這個個維維度度表表示示了了是是在在哪哪種種資資源源、、對對象象上上的的鎖鎖，，比比如如JQ表示示在在job對象象上上的的鎖鎖、、TM表示示對對象象鎖鎖(表表鎖鎖)、、TX表示示事事務(wù)務(wù)鎖鎖(行行鎖鎖)、、TS表示示表表空空間間(tablespace)的的鎖鎖等等等等。。另一一個個是是鎖鎖的的模模式式(mode)，，包含含0-6。。2023/1/4關(guān)于于鎖鎖機(jī)機(jī)制制鎖的的模模式式(mode)：：0：：None1：：null2：：rowshare，，即RS、、行級級共共享享鎖鎖3：：rowexclusive，，即RX、、行級級排排它它鎖鎖4：：share，，即S、、共享享鎖鎖5：：sharerowexclusive，，即SRX、、共享享行行級級排排它它鎖鎖6：：exclusive，，即X、、排它它鎖鎖2023/1/4關(guān)于于鎖鎖機(jī)機(jī)制制不同同的的鎖鎖模模式式(lockmode)的相相容容列列表表見見下下：：2023/1/4關(guān)于鎖機(jī)機(jī)制oracle中的不同同操作需需要對不不同的對對象加不不同模式式的鎖；；通過鎖鎖的類別別來表示示對某種種對象加加鎖；而而通過不不同的鎖鎖的模式式的相容容規(guī)則，，來控制制哪些操操作可以以并行，，哪些操操作是互互斥的；；通過這這樣的鎖鎖機(jī)制來來保證每每個用戶戶訪問對對象的正正確性。。一個操作作可能需需要對多多種對象象加鎖(需要申申請一種種以上type的鎖)，，同時根根據(jù)操作作的不同同申請不不同的鎖鎖模式(lockmode)。比如：selectforupdate操作需要要對表申申請mode=3(即RX)的TM鎖(locktype=TM)，然后對選選到的行行申請mode=6(即X)的TX鎖(locktype=TX)(網(wǎng)上很多多文檔說說是加mode=2的TM鎖，是在在8i庫上，在在9i或者10g的庫實(shí)測測加的是是mode=3的TM鎖，如果果有分區(qū)區(qū)則對對對應(yīng)分區(qū)區(qū)增加的的是mode=2的TM鎖)；執(zhí)行DML操作也是是一樣，，需要對對表增加加mode=3的TM鎖，對作作dml操作的行行增加mode=6的TX鎖。那么根據(jù)據(jù)鎖相容容的模式式，mode=3的鎖是相相容的(即RX與RX是相容的的)，但mode=6的鎖是不不相容的的(即X與X是不相容容的)；因此同時時在一張張表上執(zhí)執(zhí)行dml操作和selectforupdate操作是不不阻塞的的(同時時對一張張表增加加mode=3的TM鎖是相容容的)；但如果涉涉及到相相同的行行則會阻阻塞一方方，直到到另一方方事務(wù)完完成(同同時對一一行增加加mode=6的TX鎖是不相相容的)。2023/1/4關(guān)于鎖機(jī)機(jī)制通過這個個過程，，我們可可以簡單單理解oracle的鎖機(jī)制制是如何何控制不不同操作作的相容容和互斥斥。實(shí)際際上，oracle的每種操操作都有有不同的的鎖策略略(需要要申請什什么類型型的鎖、、什么模模式的鎖鎖)，這些復(fù)雜雜的鎖策策略隨著著不同的的數(shù)據(jù)庫庫版本也也有所變變化；通通過這些些復(fù)雜的的機(jī)制，，來保證證用戶訪訪問對象象的正確確性和一一致性。。oracle的dml鎖所有鎖機(jī)機(jī)制中，，最為常常見也最最為常用用的就是是進(jìn)行各各種增、、刪、查查、改操操作中的的dml鎖機(jī)制。。dml鎖，顧名名思義，，就是在在各種dml操作中產(chǎn)產(chǎn)生的鎖鎖，這里里主要是是出現(xiàn)TX、TM兩種類型型鎖。在dml鎖機(jī)制中，TX鎖會出現(xiàn)在實(shí)實(shí)際發(fā)生改變變的部分用于于保證dml操作的正確性性。也就是我我們通常講的的事務(wù)鎖(實(shí)實(shí)際上這個事事務(wù)所真正改改變的部分)或者行鎖，用于鎖定發(fā)生生改變的行，，從而保證修修改的正確性性(不同時被被其他session修改)；就像像我們之前看看到的是用了了mode=6的鎖從而阻塞塞了其他的修修改操作。TM鎖在這里則是是一種意向鎖鎖，也就是說說需要修改某某一個對象時時，對其上層層對象增加一一個鎖，表明明修改其下級級對象意愿，，可以理解為為一種操作的的入隊；就像像我們之前看看到的，會增增加mode=3的TM鎖鎖定做dml操作的表；這這個鎖不會阻阻塞其他session對這張表同時時進(jìn)行的增刪刪查改操作，，但會阻塞對對這張表的ddl操作(大部分分，會使用獨(dú)獨(dú)占的ddl鎖定，比如addcolumn等等)，保證證對象的正確確性。2023/1/4關(guān)于鎖機(jī)制v$lock視圖v$lock視圖記錄了每每個session取得鎖或者等等待鎖的情況況：ID1和ID2標(biāo)識了鎖定的的對象，在TM和TX鎖中的含義如如下：2023/1/4關(guān)于鎖機(jī)制制通過v$lock視圖我們就就能查到session之間持有和和等待鎖以以及相互阻阻塞的情況況。更詳細(xì)的有有關(guān)dml鎖機(jī)制的說說明可以參參看轉(zhuǎn)引網(wǎng)網(wǎng)文《oracle多粒度封鎖鎖機(jī)制研究究（論壇））.doc》或其他相關(guān)關(guān)資料。本文大量內(nèi)內(nèi)容引自該該文檔和《《oracleexpertone-on-one》》相關(guān)內(nèi)容。。關(guān)于死鎖需要注意的的是，就一一般而言oracle中并不會長長期存在真真正意義上上的死鎖。。oracle會以一個很很短的時間間去輪循，，檢查是否否有死鎖，，如果發(fā)現(xiàn)現(xiàn)有死鎖出出現(xiàn)，則會會中斷掉其其中一個session以解除死鎖鎖，并拋出出ORA-00060錯誤。2023/1/4關(guān)于鎖機(jī)制制一個關(guān)于外外鍵關(guān)聯(lián)在在dml操作中鎖機(jī)機(jī)制的案例例oracle的dml鎖中，比較較復(fù)雜的情情況之一就就是涉及到到外鍵關(guān)聯(lián)聯(lián)的情況，，由于存在在完整性約約束檢查，，這里不僅僅僅會對發(fā)發(fā)生dml的表本身產(chǎn)產(chǎn)生鎖，也也會對有外外鍵關(guān)聯(lián)的的表產(chǎn)生鎖鎖。案例：locksample1.doc2023/1/4簡單的sql優(yōu)化sql語句的執(zhí)行行過程關(guān)于索引與與表掃描關(guān)于表連接接關(guān)于排序2023/1/4sql語句句的的執(zhí)執(zhí)行行過過程程sql語句句的的執(zhí)執(zhí)行行步步驟驟hardparse與softparsesoftparse也會會有有消消耗耗2023/1/4sql語句句的的執(zhí)執(zhí)行行步步驟驟1、、語法法分分析析，，分分析析語語句句的的語語法法是是否否符符合合規(guī)規(guī)范范，，衡衡量量語語句句中中各各表表達(dá)達(dá)式式的的意意義義。。2、、語語義義分分析析，，檢檢查查語語句句中中涉涉及及的的所所有有數(shù)數(shù)據(jù)據(jù)庫庫對對象象是是否否存存在在，，且且用用戶戶有有相相應(yīng)應(yīng)的的權(quán)權(quán)限限。。3、、視視圖圖轉(zhuǎn)轉(zhuǎn)換換，，將將涉涉及及視視圖圖的的查查詢詢語語句句轉(zhuǎn)轉(zhuǎn)換換為為相相應(yīng)應(yīng)的的對對基基表表查查詢詢語語句句。。4、、表表達(dá)達(dá)式式轉(zhuǎn)轉(zhuǎn)換換，，將將復(fù)復(fù)雜雜的的SQL表達(dá)達(dá)式式轉(zhuǎn)轉(zhuǎn)換換為為較較簡簡單單的的等等效效連連接接表表達(dá)達(dá)式式。。5、、選選擇擇優(yōu)優(yōu)化化器器，，不不同同的的優(yōu)優(yōu)化化器器一一般般產(chǎn)產(chǎn)生生不不同同的的"執(zhí)執(zhí)行行計計劃劃"6、、選選擇擇連連接接方方式式，，ORACLE有三三種種連連接接方方式式，，對對多多表表連連接接ORACLE可選選擇擇適適當(dāng)當(dāng)?shù)牡倪B連接接方方式式。。7、、選選擇擇連連接接順順序序，，對對多多表表連連接接ORACLE選擇擇哪哪一一對對表表先先連連接接，，選選擇擇這這兩兩表表中中哪哪個個表表做做為為源源數(shù)數(shù)據(jù)據(jù)表表。。8、、選選擇擇數(shù)數(shù)據(jù)據(jù)的的搜搜索索路路徑徑，，根根據(jù)據(jù)以以上上條條件件選選擇擇合合適適的的數(shù)數(shù)據(jù)據(jù)搜搜索索路路徑徑，，如如是是選選用用全全表表搜搜索索還還是是利利用用索索引引或或是是其其他他的的方方式式。。9、、運(yùn)運(yùn)行行"執(zhí)執(zhí)行行計計劃劃"。。2023/1/4hardparse與softparse1-8的步驟也也就是我我們通常常所說的的parse，通過parse得到一條條語句的的執(zhí)行計計劃，可可以看出出parse的過程是是一個比比較昂貴貴的消費(fèi)費(fèi)，顯然然如果每每次執(zhí)行行sql都需要進(jìn)進(jìn)行一次次完整的的parse，那么將是是非常大大的消耗耗。因此，大大部分?jǐn)?shù)數(shù)據(jù)庫都都提供了了sql的共享的的機(jī)制。。一條sql語句如果果做一次次完整的的parse并生成全全新的執(zhí)執(zhí)行計劃劃，這個個過程被被稱為hardparse；；如果已已經(jīng)parse過并仍仍然存存在于于緩存存中的的sql語句，，再次次執(zhí)行行時則則直接接使用用已經(jīng)經(jīng)在緩緩存中中的執(zhí)執(zhí)行計計劃，，不需需要再再重新新生成成執(zhí)行行計劃劃，這這個過過程稱稱為softparse。。正是因因?yàn)檫@這樣，，我們們大量量使用用綁定定變量量，使使得只只是參參數(shù)不不同的的同構(gòu)構(gòu)sql語句在在oracle為同一一條sql語句(只是是具體體執(zhí)行行時使使用的的參數(shù)數(shù)不一一樣)，由由此使使得sql語句的的執(zhí)行行計劃劃可以以得到到復(fù)用用，減減少hardparse，，盡量用用到softparse，，從而減減少parse帶來的的消耗耗。2023/1/4softparse也會有有消耗耗盡管如如此，，softparse也并非非全無無消耗耗，softparse同樣需需要在在sharedpool中取得得相關(guān)關(guān)內(nèi)存存空間間的latch(鎖住存存儲sql語句、、執(zhí)行行計劃劃以及及需要要鎖住住的相相關(guān)數(shù)數(shù)據(jù)字字典的的內(nèi)存存空間間)；；而對對latch的分配配和操操作本本身就就是一一個比比較耗耗cpu的動作作，latch的數(shù)量量也是是有限限的，，因此此過量量的并并發(fā)執(zhí)執(zhí)行，，即使使都是是softparse依然會會造成成很大大的消消耗。。案例：：實(shí)際上上如果果能夠夠在pga空間中中的cursorcache找到同同樣的的語句句，則則不需需要再再到sharedpool中查找找，這這個過過程是是消耗耗最小小的。。默認(rèn)認(rèn)情況況下，，oracle并不會會去為為session緩存存cursor，，需要要我我們們?nèi)トピO(shè)設(shè)置置session_cashed_cursor來指指定定oracle為session緩存存的的cursor數(shù)量量(當(dāng)當(dāng)然然這這會會消消耗耗pga內(nèi)存存空空間間)。2023/1/4sql語句的的執(zhí)行過程接下來，運(yùn)運(yùn)行“執(zhí)行行計劃”，，就是通常常sql性能最重要要的部分；；選擇了怎怎樣的執(zhí)行行計劃、如如何做表連連接、如何何進(jìn)行表的的掃描、是是否使用索索引、使用用什么索引引，等等問問題。應(yīng)該選擇什什么樣的執(zhí)執(zhí)行計劃，，一個比較較基本的看看法，首先先應(yīng)關(guān)注那那些直接的的查詢條件件(也就是是表的列直直接和帶入入?yún)?shù)進(jìn)行行比較的查查詢條件)，這些查查詢條件中中哪些能夠夠首先篩選選掉較多的的記錄從而而有效的降降低結(jié)果集集，那么應(yīng)應(yīng)當(dāng)優(yōu)先執(zhí)執(zhí)行這些查查詢條件，，降低整個個sql執(zhí)行過程中中需要處理理的結(jié)果集集。當(dāng)然實(shí)實(shí)際上sql的執(zhí)行計劃劃必須全盤盤考慮整個個查詢過程程怎樣才是是較優(yōu)的查查詢路徑，，包括每個個環(huán)節(jié)步驟驟選擇什么么索引、什什么掃描方方式、什么么表連接方方式。下面我們依依次看看這這些問題。。2023/1/4關(guān)于索引與與表掃描BTree索引的數(shù)據(jù)據(jù)結(jié)構(gòu)判斷是否適適合使用索索引索引使用不不合理的常常見問題2023/1/4BTree索引的數(shù)據(jù)據(jù)結(jié)構(gòu)索引，正如如其名稱一一樣，就好好像字典中中的索引，，通過它數(shù)數(shù)據(jù)庫能夠夠根據(jù)一些些特定的信信息很快的的定位到所所需要的數(shù)數(shù)據(jù)而并不不需要察看看全部的數(shù)數(shù)據(jù)才能得得到想要的的結(jié)果。BTree索引的數(shù)據(jù)據(jù)結(jié)構(gòu)是一一個根據(jù)關(guān)關(guān)鍵字排序序的B+樹結(jié)構(gòu)(一一個多層的的N叉樹)，由由一群(關(guān)關(guān)鍵字、值值)對組成成；關(guān)鍵字字就是索引引列的列值值(如果是是復(fù)合索引引，則是多多個列值)，值就是是對應(yīng)記錄錄的rowid。其中，根節(jié)節(jié)點(diǎn)存儲1-N個關(guān)鍵字和和2-N+1個指針，其其指針指向向內(nèi)層節(jié)點(diǎn)點(diǎn)或者葉結(jié)結(jié)點(diǎn)(如果果索引足夠夠小)；內(nèi)內(nèi)層節(jié)點(diǎn)存存儲(N+1)/2-1-N個關(guān)鍵字和和(N+1)/2-N+1個指指針針，，其其指指針針指指向向葉葉節(jié)節(jié)點(diǎn)點(diǎn)或或其其他他內(nèi)內(nèi)層層節(jié)節(jié)點(diǎn)點(diǎn)；；葉葉節(jié)節(jié)點(diǎn)點(diǎn)存存儲儲(N+1)/2-N個關(guān)關(guān)鍵鍵字字和和(N+1)/2-N+1個指指針針，，其其最最后后一一個個指指針針指指向向下下一一個個葉葉節(jié)節(jié)點(diǎn)點(diǎn)；；其其余余的的指指針針指指向向?qū)?yīng)應(yīng)的的行行記記錄錄(也也就就是是上上面面說說的的rowid)，，關(guān)鍵鍵字字保保存存對對應(yīng)應(yīng)記記錄錄索索引引列列的的列列值值。。2023/1/4BTree索引引的的數(shù)數(shù)據(jù)據(jù)結(jié)結(jié)構(gòu)構(gòu)根節(jié)節(jié)點(diǎn)點(diǎn)和和內(nèi)內(nèi)層層節(jié)節(jié)點(diǎn)點(diǎn)的的關(guān)關(guān)鍵鍵字字表表示示一一個個范范圍圍，，其其指指針針分分別別指指向向了了小小于于該該關(guān)關(guān)鍵鍵字字或或者者大大于于等等于于該該關(guān)關(guān)鍵鍵字字的的節(jié)節(jié)點(diǎn)點(diǎn)群群，，如如下下圖圖：：葉節(jié)節(jié)點(diǎn)點(diǎn)的的關(guān)關(guān)鍵鍵字字為為對對應(yīng)應(yīng)的的記記錄錄索索引引列列的的列列值值，，除除最最后后一一個個指指針針指指向向下下一一個個葉葉結(jié)結(jié)點(diǎn)點(diǎn)外外其其余余指指針針則則指指向向了了對對應(yīng)應(yīng)的的記記錄錄(rowid)，，如下下圖圖：：2023/1/4BTree索引的數(shù)數(shù)據(jù)結(jié)構(gòu)構(gòu)如上假設(shè)設(shè)我們要要查找索索引列值值為75的記錄錄，只需需要在根根節(jié)點(diǎn)中中找到57到81這個個范圍的的節(jié)點(diǎn)群群，然后后依次根根據(jù)范圍圍最終在在葉節(jié)點(diǎn)點(diǎn)中找到到索引列列為75的記錄錄的rowid。2023/1/4判斷是否否適合使使用索引引索引之所所以能夠夠起到優(yōu)優(yōu)化查詢詢的作用用，就在在于它將將查詢用用到的條條件(列列)作為為關(guān)鍵字字(其對對應(yīng)值指指向?qū)?yīng)應(yīng)的記錄錄)并組組織為一一個排序序的結(jié)構(gòu)構(gòu)，這樣樣我們能能在這個個排序結(jié)結(jié)構(gòu)中快快速的定定位到要要查找的的記錄而而不需要要去遍歷歷全部的的數(shù)據(jù)(就好像像查字典典一樣，，根據(jù)拼拼音或者者筆畫就就能很快快的查到到一個字字，而不不需要把把整個字字典翻一一遍)。。相對通過過全表掃掃描找到到一條記記錄，通通過索引引避免了了很多冗冗余數(shù)據(jù)據(jù)的掃描描(我們們不需要要把整個個字典中中不是我我們要查查找的字字的頁也也翻看一一遍)。。但同時時我們也也看到，，對于單單獨(dú)的一一條記錄錄而言通通過索引引掃描在在讀取這這條記錄錄的花費(fèi)費(fèi)上增加加了掃描描索引和和通過rowid定位的操操作。因此不是是所有情情況下，，都適合合使用的的索引。。假設(shè)一一個字典典記錄了了1000個字字，而我我們需要要查找其其中的900個個字，這這種情況況下如果果還先查查索引在在找到對對應(yīng)的字字就不如如直接把把整個字字典翻看看一遍來來的要快快。同樣的道道理，并并不是所所有的字字段都適適合建立立BTree索引，如如果一個個字段的的獨(dú)立列列值非常常少，比比如100萬的的記錄卻卻只有10個獨(dú)獨(dú)立列值值，那么么任意查查詢其中中一個列列值都會會查詢出出10萬萬條記錄錄(10%)，，那么這這個索引引就算使使用效率率也很低低，這個個字段不不適合建建立單列列的BTree索引。。2023/1/4判斷是是否適適合使使用索索引而實(shí)際際上的的經(jīng)驗(yàn)驗(yàn)數(shù)據(jù)據(jù)，當(dāng)當(dāng)通過過索引引掃描描access的記錄錄數(shù)<=總總記錄錄數(shù)的的6%的時時候，，使用用索引引是有有效率率的，，可見見掃描描索引引的數(shù)數(shù)據(jù)結(jié)結(jié)構(gòu)本本身和和通過過索引引多次次的去去accesstable也有著著相當(dāng)當(dāng)?shù)南摹！?實(shí)實(shí)際上上計算算索引引掃描描的成成本是是用需需要accesstable的block數(shù)來計計算access的次數(shù)數(shù)，也也就是是說，，假設(shè)設(shè)索引引的順順序和和表存存儲的的順序序完全全一致致(比比如sequence作的主主鍵索索引)，則則這個個比例例可以以擴(kuò)大大一些些；但但實(shí)際際上這這個假假設(shè)成成立的的情況況比較較少，，而且且即便便如此此這個個比例例也不不會很很大)另外需需要注注意的的是，，BTree索引并不記記錄null值，也就是是說是用isnull或者isnotnull這樣的條件件是不可能能用到BTree索引的。2023/1/4判斷是否適適合使用索索引判斷是否應(yīng)應(yīng)該使用索索引或者說說是否使用用到合適的的索引，主主要在于下下面幾種情情況：tab.a=:1，，這種情況主主要看:1在整個a的獨(dú)立列值值中占了多多少百分比比(也就是是a列取值為:1的記錄錄數(shù)占到整整個記錄數(shù)數(shù)的比例)；這個比比例很低的的話則適合合使用a列的索引，，反之oracle就會傾向于于使用全表表掃描。tab.a>=:1andtab.a<=:2，這種情況主主要看:1-:2之之間這個范范圍的記錄錄數(shù)占到總總記錄數(shù)的的比例；范范圍太大(比例比較較高)的話話則不適合合使用a列的索引tab.ain(list)，這種情況主主要看inlist中的列值包包含的記錄錄數(shù)占到總總記錄數(shù)的的百分比，，這個百分分比較大的的話就不適適合使用a的索引。假假設(shè)一個列列有10個個獨(dú)立列值值，而inlist中就有5個個列值，那那么平均計計算可能就就是50%，顯然這這里并不適適合使用a的索引。tab.a=table.b，通過表table作為驅(qū)動表表與表tab做表連接，，連接條件件是table表的b列=tab表的a列，這里主主要看表table用于表連接接的結(jié)果集集其每條記記錄的b列值對應(yīng)在在tab表的a列能夠選取取到的記錄錄數(shù)的總和和占tab表記錄數(shù)的的百分比(這里用tab表a列的索引指指的是使用用nestedloop表連接方式式的情況下下，使用hashjoin或其他的表表連接方式式，這個比比例的計算算并不適用用，關(guān)于表表連接的方方式，我們們在后面討討論)，如如果表table用于作為驅(qū)驅(qū)動表的結(jié)結(jié)果集比較較小、且結(jié)結(jié)果集中b列的列值對對應(yīng)tab表中a列的列值能能夠選取的的到的記錄錄數(shù)比較低低，則適合合使用tab表上a列的索引。。(這里指指使用nestedloop的情況，涉涉及到表連連接索引的的使用要跟跟表連接的的方式一起起考慮，在在表連接的的部分我們們再做討論論)2023/1/4索引引使使用用不不合合理理的的常常見見問問題題缺少少合合適適的的索索引引可可用用(選選擇擇更更加加優(yōu)優(yōu)化化的的字字段段或或者者合合理理的的復(fù)復(fù)合合索索引引首首列列)案例例1：：indexsample1.doc在這這個個案案例例中中：：語句句(1)存存在在一一個個日日期期范范圍圍查查詢詢可可以以使使用用在在日日期期字字段段上上的的索索引引，，但但是是如如果果時時間間范范圍圍跨跨度度過過大大，，這這個個索索引引的的效效率率也也就就不不高高了了；；語句句(2)能能夠夠有有查查詢詢條條件件的的字字段段當(dāng)當(dāng)中中只只有有一一個個區(qū)區(qū)分分度度很很低低的的字字段段建建了了索索引引(千千萬萬條條數(shù)數(shù)據(jù)據(jù)只只有有幾幾十十個個獨(dú)獨(dú)立立列列值值)，，這個個字字段段是是不不適適合合建建立立單單列列索索引引的的，，查查詢詢使使用用這這個個索索引引的的效效率率也也非非常常低低；；這兩兩個個語語句句我我們們通通過過分分析析語語句句，，都都發(fā)發(fā)現(xiàn)現(xiàn)了了有有區(qū)區(qū)分分度度比比較較高高且且適適用用的的查查詢詢條條件件字字段段，，只只要要在在這這些些字字段段建建立立索索引引，，就就能能優(yōu)優(yōu)化化語語句句的的執(zhí)執(zhí)行行效效率率。。這里里我我們們看看到到，，過過大大的的范范圍圍查查詢詢會會影影響響索索引引的的效效率率；；而而過過低低的的區(qū)區(qū)分分度度的的列列則則并并不不適適合合建建立立單單列列索索引引。。2023/1/4索引引使使用用不不合合理理的的常常見見問問題題缺少少合合適適的的索索引引可可用用(選選擇擇更更加加優(yōu)優(yōu)化化的的字字段段或或者者合合理理的的復(fù)復(fù)合合索索引引首首列列)案例例2：：indexsample2.doc在這這個個案案例例中中：：表cjk上原原來來有有一一個個復(fù)復(fù)合合索索引引(FZJZH,FBMDM,FSCDM,FGDDM,FZQDM,FHTXH,FMMLB,FCJSJ)，，這個個索索引引的的區(qū)區(qū)分分度度很很高高，，本本來來是是很很好好用用的的。。但但問問題題就就出出來來這這兩兩個個查查詢詢語語句句中中，，前前面面幾幾列列使使用用的的都都是是模模糊糊查查詢詢，，而而根根據(jù)據(jù)實(shí)實(shí)際際情情況況，，往往往往傳傳入入的的都都是是百百分分號號，，導(dǎo)導(dǎo)致致索索引引掃掃描描的的時時候候無無法法根根據(jù)據(jù)關(guān)關(guān)鍵鍵字字的的范范圍圍快快速速的的定定位位到到需需要要的的索索引引結(jié)結(jié)點(diǎn)點(diǎn)，，在在這這里里反反而而使使用用這這個個索索引引效效率率比比全全表表掃掃描描還還要要低低得得多多(實(shí)實(shí)際際情情況況是是幾幾個個小小時時)。。分析析這這條條兩兩條條語語句句的的查查詢詢條條件件，，發(fā)發(fā)現(xiàn)現(xiàn)FCJSJ這個個查查詢詢條條件件，，實(shí)實(shí)際際操操作作中中基基本本上上都都是是查查詢詢一一天天的的數(shù)數(shù)據(jù)據(jù)，，這這里里只只需需要要建建立立一一個個以以FCJSJ作為首首列的的復(fù)合合索引引(fbdsj,fbmdm,fzjzh,fgddm,fscdm,fzqdm)，就可以以優(yōu)化化語句句的效效率。。由于BTree索引是是關(guān)鍵鍵字排排序，，如果果復(fù)合合索引引的首首列不不能根根據(jù)查查詢條條件有有效的的篩選選，就就需要要掃描描大量量冗余余的索索引結(jié)結(jié)點(diǎn)；；在這這個案案例中中由于于前面面幾列列都出出現(xiàn)了了%號號的情情況，，導(dǎo)致致幾乎乎是將將整個個索引引結(jié)點(diǎn)點(diǎn)掃描描了一一遍才才得到到結(jié)果果，效效率非非常低低。所所以復(fù)復(fù)合索索引要要特別別注意意首列列的選選擇。。2023/1/4索引使使用不不合理理的常常見問問題不均勻勻分布布的列列值在在bindpeeking和histogram的影響響下，影響響索引的使用用首先解解釋下下相關(guān)關(guān)名詞詞：bindpeeking：：sql語句中中使用用到綁綁定變變量，，在第第一次次執(zhí)行行時會會peeking其綁定定變量量的值，，就相相當(dāng)于于常量量語句句一樣樣，并并根據(jù)據(jù)這個個具體體值解解析計計算成成本，，解釋釋出執(zhí)執(zhí)行計計劃。。這一一特性性是在在oracle9i以后引引入的的。histogram：直方圖圖，對對于不不同列列值更更加準(zhǔn)準(zhǔn)確的的數(shù)據(jù)據(jù)量的的統(tǒng)計計。對對于列列值分分布不不均勻勻的列列來說說，通通過直直方圖圖，就就能準(zhǔn)準(zhǔn)確計計算出出不同同列值值的數(shù)數(shù)據(jù)量量，而而不僅僅僅簡簡單的的根據(jù)據(jù)(總記記錄數(shù)數(shù)/獨(dú)立列列值數(shù)數(shù))來來計算算其數(shù)數(shù)據(jù)量量(平平均情情況)。2023/1/4索引引使使用用不不合合理理的的常常見見問問題題不均均勻勻分分布布的的列列值值在在bindpeeking和histogram的影影響響下下，影影響響索引引的使使用用以下下引引用用《《ORACLE數(shù)據(jù)據(jù)庫庫優(yōu)優(yōu)化化案案例例簡簡報報(第第一一期期)》的的相相關(guān)關(guān)內(nèi)內(nèi)容容：：由于8i還沒有bindpeeking技術(shù)，使使用綁定定變量以以后無法法使用histogram，，所以最好在編程程時對具具有skew值的列不不使用bind變量,這這樣,生生成計計劃時,其可可根據(jù)histogram的值來估估算返回回的數(shù)據(jù)據(jù)量,并并生成成合適的的計劃。。9i引入了bindpeeking技術(shù)，使使用綁定定變量以以后可以以用到histogram，，但是如果果第一次執(zhí)行帶帶入的變變量值失失誤，很很可能產(chǎn)產(chǎn)生的執(zhí)執(zhí)行計劃劃對以后后的多次次查詢不不適合而而帶來性能問題題。2023/1/4索引使用用不合理理的常見見問題不均勻分分布的列列值在bindpeeking和histogram的影響下下，影響索引的使用我們看看看《ORACLE數(shù)據(jù)庫優(yōu)優(yōu)化案例例簡報(第一期期)》所舉的的這個案案例：語句：selectpolicy_cert_no,apply_personnel_numfromacc_policy_certwherepolicy_no=:1andcert_type='1'selectpolicy_cert_nofromacc_policy_certwhereinsurance_card_no=:1andpolicy_no=:22023/1/4索引使用不合合理的常見問問題不均勻分布的的列值在bindpeeking和histogram的影響下，影響索引的使用這兩條語句都都是對表acc_policy_cert進(jìn)行查詢，在在policy_no字段上有主鍵鍵索引(復(fù)合合索引的首列列)，正常的情況下下，應(yīng)該走這這個索引而不不是全表掃描描。但在policy_no的列值分布并并不均勻，比比如policy_no列共有100個不同的值值，其中為70的占了99％，為其其他值的數(shù)據(jù)據(jù)行僅占1％％（即選擇性性很高），則則如果不使用用綁定變量，，借助histogram，oracle能夠知道，查查詢policy_no=70的時候應(yīng)該走走全表掃描效效率更高，查查詢policy_no為其他值的語語句應(yīng)該走索索引效率更高高。問題就出現(xiàn)了了，假設(shè)第一一次執(zhí)行時帶帶入的是70的這個值，，顯然執(zhí)行計計劃會走全表表掃描，但是是由于使用了了綁定變量，，以后即使是是帶入其他值值，執(zhí)行計劃劃依然會走全全表掃描。2023/1/4索引使用不合合理的常見問問題不均勻分布的的列值在bindpeeking和histogram的影響下，影響索引的使用解決辦法有有3個：1)不使用用綁定變量量，但是這這樣就會導(dǎo)導(dǎo)致大量的的hardparse，對sharedpool也會產(chǎn)生大大量的消耗耗。2)修改程程序，針對對不同情況況使用不同同執(zhí)行計劃劃(不同的的語句)3)不收集集直方圖(這樣就會會按照平均均情況來計計算數(shù)據(jù)量量)或者使用hint綁定執(zhí)行計計劃，使其其總能使用用到索引，，這樣就可可以使大部部分情況得得到較好的的效率，但但對于比如如70這樣樣的值就會會效率低下下。關(guān)于這個問問題詳細(xì)的的解釋和說說明可以參參見《ORACLE數(shù)據(jù)庫優(yōu)化化案例簡報報(第一期期)》中相關(guān)內(nèi)內(nèi)容。2023/1/4關(guān)于表連接接三種主要的的表連接方方式何時使用哪哪種表連接接方式2023/1/4三種主要的的表連接方方式nestedloopjoin循環(huán)嵌套連連接：行源源1的每一一條記錄，，依次去匹匹配行源2的每條記記錄，將符符合連接條條件的記錄錄放在結(jié)果果集中，直直到行源1的所有記記錄都完成成這個操作作。循環(huán)嵌嵌套連接是是最基本也也是最古老老的表連接接方式。sortmergejoin排序合并連連接：行源源1和行源源2的數(shù)據(jù)據(jù)分別排序序，然后將將兩個排序序的源表合合并，符合合連接條件件的記錄放放到結(jié)果集集中。由于于排序需要要內(nèi)存空間間，sortmergejoin對內(nèi)存有比比較大的消消耗，如果果內(nèi)存空間間(8i為sort_area_size，9i及以上使用用PGA)不足，則會會使用臨時時表空間，，這樣會降降低排序合合并連接的的效率。排排序合并連連接是最古古老的表連連接方式之之一。hashjoin哈希連接：：將行源1計算成一一張基于連連接鍵的hash表，行源2的每條記記錄依次掃掃描這張hash表，找到匹匹配的記錄錄放到結(jié)果果集。計算算hash表需要內(nèi)存存空間，hashjoin同樣對于內(nèi)內(nèi)存有比較較大的消耗耗，如果內(nèi)內(nèi)存空間(8i為hash_area_size，9i及以上使用用PGA)不足足，，則則會會使使用用臨臨時時表表空空間間，，這這樣樣會會降降低低哈哈希希連連接接的的效效率率。。2023/1/4三種種主主要要的的表表連連接接方方式式nestedloopjoin2023/1/4三種種主主要要的的表表連連接接方方式式sortmergejoin2023/1/4三種主主要的的表連連接方方式hashjoin2023/1/4何時使使用哪哪種表表連接接方式式nestedloopjoin表連接接方式式的適適用情情況nestedloopjoin適合于于：作作為表表連接接的驅(qū)驅(qū)動表表(也也就是是之前前的行行源1，也也稱為為外部部表)記錄數(shù)數(shù)比較較少或或者通通過直直接的的查詢詢條件件能篩篩選出出比較較少的的記錄錄數(shù)，，被連連接表表(也也就是是之前前的行行源2，也也稱為為內(nèi)部部表)在連連接條條件上上有區(qū)區(qū)分度度很高高的索索引；；驅(qū)動動表上上的每每條記記錄通通過被被連接接表在在連接接條件件上的的索引引能快快速的的匹配配到少少量的的記錄錄；整整體的的結(jié)果果集比比較小小，這這樣就就比較較適合合使用用nestedloopjoin。。nestedloopjoin選擇驅(qū)驅(qū)動表表時應(yīng)應(yīng)優(yōu)先先選擇擇記錄錄數(shù)比比較少少的、、通過過直接接查詢詢條件件能夠夠篩選選出比比較少少記錄錄的表表作為為驅(qū)動動表，，這樣樣能夠夠有效效的減減少匹匹配次次數(shù)。。例如如這樣樣的查查詢語語句：：select*froma,bwherea.col1=:1anda.col2=b.col2；這里a表有100條記記錄，，通過過col1=:1的條件件能夠夠篩選選出50條條記錄錄，b表只有有10條記記錄；；這里里如果果以a表作驅(qū)驅(qū)動表表的話話，則則匹配配次數(shù)數(shù)是50*10；如如果用用b表作為為驅(qū)動動表的的話，，則匹匹配次次數(shù)是是10*100；顯顯然應(yīng)應(yīng)該使使用a表作為為驅(qū)動動表。。如果連連接條條件沒沒有很很好的的索引引、或或者作作為表表連接接的兩兩張表表結(jié)果果集都都相當(dāng)當(dāng)大，，則并并不適適合使使用nestedloopjoin。。2023/1/4何時使使用哪哪種表表連接接方式式sortmergejoin表連接接方式式的適適用情情況sortmergejoin適用于于：當(dāng)當(dāng)表連連接的的兩張張表的的結(jié)果果集都都比較較大，，或沒沒有很很好的的條件件可以以篩選選，連連接條條件缺缺少很很好的的索引引時，，可以以選擇擇使用用sortmergejoin。由于sortmergejoin需要對對作連連接的的兩張張表都都作排排序，，實(shí)際際上如如果語語句中中沒有有排序序需求求，oracle更加傾傾向于于選擇擇hashjoin。但如果果語句句中本本身就就有排排序的的需求求，sortmergejoin則有可可能省省去單單獨(dú)的的排序序。sortmergejoin對內(nèi)存存消耗耗比較較大，，如果果內(nèi)存存空間間不足足以完完成排排序，，則需需要用用到臨臨時表表空間間，效效率會會有較較大的的降低低。sortmergejoin只能用用于等等價連連接。。2023/1/4何時使使用哪哪種表表連接接方式式hashjoin表連接接方式式的適適用情情況hashjoin適用于于：當(dāng)當(dāng)表連連接的的兩張張表的的結(jié)果果集都都比較較大，，或沒沒有很很好的的條件件可以以篩選選，連連接條條件缺缺少很很好的的索引引時，，使用用hashjoin能夠取取得比比較好好的效效率。hashjoin雖然也也需要要將一一張表表的所所有記記錄依依次和和hash表中的的記錄錄進(jìn)行行匹配配，但但掃描描hash表的速速度要要比掃掃描BTree索引快快的多多，所所以在在大結(jié)結(jié)果集集和缺缺少良良好索索引的的情況況下，，使用用hashjoin能得到到比較較好的的效率率。與sortmergejoin相比，，hashjoin只需要要進(jìn)行行一次次排序序，因因此大大部分分情況況下，，oracle會更傾傾向于于選擇擇hashjoin。hashjoin需要計計算一一張hashtable，，與sortmergejoin一樣，，需要要消耗耗大量量的內(nèi)內(nèi)存空空間，如果內(nèi)內(nèi)存空空間不不足則則需要要用到到臨時時表空空間，，效率率會有有較大大的降降低。。hashjoin只能CBO優(yōu)化器器下使使用，，只能能用于于等價價連接接。例如語語句：：select*froma,bwherea.col1=:1andb.col1=:2anda.col2=b.col2在這個個語句句中，，a表通過過col1=:1和b表通過過col1=:2篩選后后的記記錄數(shù)數(shù)依然然比較較大，，雖然然col2在a表和b表上都都是很很不錯錯的索索引，，但是是因?yàn)闉樽鬟B連接的的結(jié)果果集比比較大大，使使用nestedloop效率不不高，，這時時使用用hashjoin就能得得到較較好的的效率率。2023/1/4何時使使用哪哪種表表連接接方式式案例1：《joinsample1.doc》在這個個案例例中，，原來來的執(zhí)執(zhí)行計計劃，，選擇擇了首首先掃掃描兩兩張小小表SELECT_CONDITION_TMP，，然后與與大表表ASSET_COMBINATION進(jìn)行nestedloopjoin，，兩張小小表結(jié)結(jié)合起起來的的條件件大概概會形形成一一個幾幾百條條的結(jié)結(jié)果集集，而而與表表ASSET_COMBINATION進(jìn)行nestedloopjoin也是一一個具具有一一定區(qū)區(qū)分度度的索索引，，看起起來似似乎選選擇nestedloopjoin并沒有有錯。。但仔細(xì)細(xì)分析析：首首先這這里在在nestedloopjoin時使用的的索引是是字段the_date上的索引引，也就就是說連連接條件件curno、cmbno并沒有起起到快速速查找定定位的作作用；其其次由于于兩張小小表SELECT_CONDITION_TMP只是一些些查詢條條件的組組合本身身數(shù)據(jù)量量非常少少，但依依然會使使得通過過the_date=:1這個條件件掃描ASSET_COMBINATION表會重復(fù)復(fù)很多次次；再次次，ASSET_COMBINATION這個表上上并沒有有以curno或cmbno為首列的的索引，，而以這這兩個列列作為條條件區(qū)分分度并不不是很高高。分析之下下，作為為直接條條件the_date=:1能夠篩過過濾掉大大量的記記錄，且且有不錯錯的索引引，有效的減減小結(jié)果果集，因因此這里里應(yīng)該首首先掃描描ASSET_COMBINATION減少冗余余的掃描描(不必必像原執(zhí)執(zhí)行計劃劃一樣重重復(fù)掃描描很多次次)；然后與兩兩張小表表SELECT_CONDITION_TMP進(jìn)行連接接時，因因?yàn)檫@兩兩張表的的數(shù)據(jù)比比ASSET_COMBINATION通過the_date=:1選出來的的結(jié)果集集還要小小很多，，這里適適合使用用hashjoin有比較好好的效率率。2023/1/4何時使用用哪種表表連接方方式案例2：：《joinsample2.msg》在這個案案例，原原語句因因?yàn)槲ㄒ灰荒軌蚴资紫冗^濾濾掉較多多結(jié)果集集的條件件TASK_STATUS_ID=‘12’本身仍然然會篩選選出數(shù)量量較大的的結(jié)果集集，而PA_TASK_TLR_ADMIN本身是個個小表，，因此優(yōu)優(yōu)先使用用直接條條件篩選選掉一些些結(jié)果集集，然后后再與小小表使用用hashjoin；；看起來這這似乎并并沒有什什么問題題。但仔細(xì)分分析：表表PA_CUSTOMER_CAMPAIGN是個大表表，而TASK_STATUS_ID又是一個個區(qū)分度度很低條條件，首首先使用用條件TASK_STATUS_ID=‘12’查詢表PA_CUSTOMER_CAMPAIGN就是一個個效率不不高的動動作。發(fā)發(fā)現(xiàn)在表表PA_CUSTOMER_CAMPAIGN上字段MGR_QUEUE_ID具有相對對好一些些的區(qū)分分度(當(dāng)當(dāng)然就這這個表數(shù)數(shù)據(jù)量來來講，不不算太好好)，而而表PA_TASK_TLR_ADMIN又是一個個小表，，雖然沒沒有首先先使用TASK_STATUS_ID=‘12’過濾掉較較多的結(jié)結(jié)果集，，但是這這里使用用小表PA_TASK_TLR_ADMIN通過在MGR_QUEUE_ID上的索引引nestedloopjoin表PA_CUSTOMER_CAMPAIGN，因?yàn)檫@個個索引的的效率要要好得多多了，反反而能夠夠取得比比較好的的效率。。2023/1/4關(guān)于排排序排序是是數(shù)據(jù)據(jù)庫中中一個個比較較常見見的操操作，，使用用orderby是一個個很司司空見見慣的的東西西；但但排序序的發(fā)發(fā)生并并不只只是在在orderby的時候候，排排序產(chǎn)產(chǎn)生的的消耗耗有時時也會會產(chǎn)生生很大大的影影響，，對于于排序序也有有一些些需要要注意意的地地方。一個關(guān)關(guān)于排排序的的sql：投資數(shù)數(shù)據(jù)庫庫有一一個很很簡單單的sql，是一個個統(tǒng)計計報表表類的的sql，只是將將一張張數(shù)據(jù)據(jù)表中中某一一段時時間的的數(shù)據(jù)據(jù)做一一個按按照一一些統(tǒng)統(tǒng)計條條件groupby的操作作；通通常會會計算算一年年的數(shù)數(shù)據(jù)，，雖然然數(shù)據(jù)據(jù)量比比較大大，但但因?yàn)闉槭莻€個統(tǒng)計計操作作，執(zhí)執(zhí)行次次數(shù)很很低，，在生生產(chǎn)環(huán)環(huán)境大大概每每次執(zhí)執(zhí)行需需要10幾幾秒。。但在測測試環(huán)環(huán)境的的一次次測試試中，，這條條語句句發(fā)現(xiàn)現(xiàn)執(zhí)行行得很很慢，，幾分分鐘才才能跑跑出結(jié)結(jié)果。。對此跟跟蹤之之下，，發(fā)現(xiàn)現(xiàn)這條條語句句執(zhí)行行時有有大量量的directpathread/write等待(后面面我們們會討討論)，也也就是是說排排序使使用臨臨時表表空間間產(chǎn)生生了很很大消消耗。。經(jīng)檢查查，這這張表表有百百萬級級的數(shù)數(shù)據(jù)量量，占占了幾幾百M(fèi)的空間間。根根據(jù)查查詢條條件約約會查查詢出出1/3的的記錄錄數(shù)來來作統(tǒng)統(tǒng)計，，算下下來大大約是是100M的數(shù)據(jù)