亚洲免费av电影一区二区三区,日韩爱爱视频,51精品视频一区二区三区,91视频爱爱,日韩欧美在线播放视频,中文字幕少妇AV,亚洲电影中文字幕,久久久久亚洲av成人网址,久久综合视频网站,国产在线不卡免费播放

        ?

        Oracle數(shù)據(jù)庫查詢語句的優(yōu)化研究

        2016-03-30 14:04:19吳健
        科技視界 2016年1期
        關(guān)鍵詞:Oracle數(shù)據(jù)庫效率優(yōu)化

        吳健

        【摘 要】相比較一般數(shù)據(jù)庫,Oracle的性能更加優(yōu)秀,存儲的級別更大,使用的范圍更加廣泛,因而對Oracle數(shù)據(jù)庫的研究才更顯得有必要。對于Oracle數(shù)據(jù)庫性能的研究中, 查詢語句的執(zhí)行效率十分重要, 特別對于大型數(shù)據(jù)庫系統(tǒng), SQL查詢語句效率的高低相比可能有上百倍之差。下文將重點(diǎn)介紹了Oracle數(shù)據(jù)庫SQL查詢語句的處理過程, 再對不同優(yōu)化器工作原理進(jìn)行了解析, 分析若干方法去提升Oracle數(shù)據(jù)庫查詢優(yōu)化性能, 同時(shí)也能使我們進(jìn)一步去了解Oracle數(shù)據(jù)庫,方便我們的工作生活。

        【關(guān)鍵詞】Oracle數(shù)據(jù)庫;SQL語句;優(yōu)化;效率

        0 導(dǎo)言

        數(shù)據(jù)庫技術(shù)是計(jì)算機(jī)科學(xué)技術(shù)發(fā)展最快、應(yīng)用最為廣泛的信息技術(shù)之一,數(shù)據(jù)庫技術(shù)已經(jīng)從相對傳統(tǒng)的商業(yè)領(lǐng)域不斷擴(kuò)大到許多新的領(lǐng)域,如醫(yī)療、多媒體、經(jīng)濟(jì)、政治、科研等領(lǐng)域。其中Oracle數(shù)據(jù)庫是應(yīng)用最為廣泛的數(shù)據(jù)庫,其中在通信領(lǐng)域范圍內(nèi),全球20家排名頂尖的通信公司都是使用Oracle產(chǎn)品;在IT服務(wù)公司中,中國前100大的IT公司(HP、IBM、凱建、埃森哲、東軟等)全部都用Oracle相關(guān)技術(shù)。對Oracle數(shù)據(jù)庫的優(yōu)化研究就是進(jìn)行有目的性的優(yōu)化其組件以改善Oracle數(shù)據(jù)庫的性能,即增加數(shù)據(jù)庫的吞吐量和減少數(shù)據(jù)庫的響應(yīng)時(shí)間,因而從大多數(shù)Oracle系統(tǒng)的應(yīng)用實(shí)例來觀察,其中數(shù)據(jù)庫的查詢操作在各種操作中所占的比率是最大的,查詢效率成為了影響Oracle數(shù)據(jù)庫各項(xiàng)性能的關(guān)鍵因素。所以本文將介紹Oracle數(shù)據(jù)庫查詢語句的一些優(yōu)化研究方案。

        1 Oracle數(shù)據(jù)庫SQL語句處理過程

        一般來說,數(shù)據(jù)庫處理SQL語句都要經(jīng)過三個(gè)過程:分析、執(zhí)行、返回結(jié)果,比如Congnos ReportNet通過拖放完成表現(xiàn)層后,能夠自動地生成SQL語句,然后將生成的SQL語句傳遞到Oracle數(shù)據(jù)庫中進(jìn)行數(shù)據(jù)處理。

        1.1 分析

        分析是處理SQL語句的第一步,它包含了以下幾個(gè)方面:

        (1)語法分析:Oracle是采用數(shù)據(jù)庫常用的自下向上的分析方法,包含檢驗(yàn)語法規(guī)范,命名規(guī)范,它是處理SQL語句中消耗時(shí)間做多且代價(jià)最高的步驟;增加Oracle數(shù)據(jù)庫的查詢效率可以主要表現(xiàn)在綁定變量和存儲過程兩個(gè)方面:

        A.綁定變量:如果使用SQL語句查詢的頻率越高,系統(tǒng)硬件消耗的資源也將會變大,以至于了減少系統(tǒng)的資源使用空間,導(dǎo)致用戶的訪問數(shù)量降低,另外同時(shí)其它查詢語句也將被從共享池中踢出, 而使用綁定變量,提交給相同對象的同樣的查詢的用戶唯一使用就能夠被重復(fù)使用,其效率能大大地增強(qiáng)。

        B.存儲過程:存儲過程只會是在創(chuàng)建時(shí)編譯,然后的每次執(zhí)行都再也不需要進(jìn)行編譯,而普通的SQL語句執(zhí)行一次都需要進(jìn)行編譯執(zhí)行,相比較,盡量使用存儲過程就會增強(qiáng)Oracle數(shù)據(jù)庫的執(zhí)行效率。

        (2)語義分析,學(xué)過編譯原理的應(yīng)該會知道這一步是相當(dāng)重要的,Oracle主要是分析SQL語句格式正確與否,各個(gè)對象是否存在,以及它是否擁有足夠的權(quán)限執(zhí)行。

        SQL>SELECT DEPT_NO,DEPT_NAME,DEPT_LOC FROM DEPT;

        提示:ERROR at line 1:

        ORA-00942: table or view does not exist

        由于查詢沒有可供訪問的DEPT(部門表)對象,因此該SQL語句無法通過語義檢測,不能查詢結(jié)果。

        (3)表達(dá)式轉(zhuǎn)換,將復(fù)雜的SQL表達(dá)式轉(zhuǎn)換成為相對應(yīng)的對于基表的SQL查詢語句,寫出SQL的標(biāo)準(zhǔn)。

        A.不要在WHERE中進(jìn)行表達(dá)式計(jì)算,例如SELECT * FROM EMP WHERE DEPT_NO>=10*10 AND DEPT_NO >(10+1)*10是不被允許的查詢語句。

        B.數(shù)據(jù)類型的匹配

        SELECT EMP_SALARY FROM EMP WHERE EMP_SALARY>100 沒有SELECT EMP_SALARY FROM EMP WHERE EMP_SALARY>100.00好了。

        (4)選擇優(yōu)化器。Oracle的優(yōu)化器共有3種:

        1)RULE (基于規(guī)則 2)COST (基于成本 3)CHOOSE (選擇性)

        在默認(rèn)的情況下,Oracle數(shù)據(jù)庫一般選用的是選擇性優(yōu)化器, 目的就是規(guī)避不需要的全表掃描,但是實(shí)際應(yīng)用中還是盡量不使用選擇性優(yōu)化器,而采用的是基于規(guī)則或者基于成本的優(yōu)化器。當(dāng)然,最好是能根據(jù)批量的SQL自己編制一個(gè)特定的優(yōu)化器來幫助自己優(yōu)化SQL查詢。

        (5)選擇連接方式。優(yōu)化器使用了六種不同的連接方式(不做詳解):

        1 嵌套循環(huán)連接(NESTED LOOP JOIN)

        2 群集連接 (CLUSTER JOIN)

        3 排序連接(SORT MERGE JOIN)

        4 笛卡爾連接(CARTESIAN JOIN)

        5 哈希連接(HASH JOIN)

        6 索引連接(INDEX JOIN)

        這六種連接方式都存在各自獨(dú)特的技術(shù)特性,在特定的條件下,各自高效的性能都能夠得到充分的發(fā)揮。

        (6)選擇連接順序。在Oracle SQL查詢語句中最好的方法是手工指定表的連接順序。為了盡快創(chuàng)建最小的解決方案集,這里所遵循的規(guī)則是將表結(jié)合起來,通常優(yōu)先使用限制最嚴(yán)格的WHERE子句來連接表。

        選擇數(shù)據(jù)搜索路徑。根據(jù)以上條件選擇合適的數(shù)據(jù)搜索路徑,能使SQL語句執(zhí)行的更加有效率。

        1.2 執(zhí)行

        主要在于使用更新和刪除的SQL語句時(shí),必須要鎖定數(shù)據(jù)列,防止其他用戶進(jìn)行修改。Oracle會先在數(shù)據(jù)庫緩沖中尋找是否存在所需要的數(shù)據(jù)塊,如果存在這樣的數(shù)據(jù)塊,就可以直接進(jìn)行讀或者修改操作,否則數(shù)據(jù)會被從物理文件之中讀取到數(shù)據(jù)庫緩沖區(qū)中。

        1.3 返回結(jié)果

        對SELECT 語句需要返回結(jié)果的語句,看返回的結(jié)果集是否需要按照實(shí)際應(yīng)用中排序,若需要,則排序后輸出。

        2 Oracle優(yōu)化器工作原理

        Oracle優(yōu)化器分為三類:

        1)RULE優(yōu)化器:基于規(guī)則的優(yōu)化器相對而言比較簡單,工作原理概括為檢查數(shù)據(jù)庫當(dāng)中的所有的可用路徑并且將這些可用的路徑與已經(jīng)存在的路徑表進(jìn)行比較,來確定SQL語句具體的執(zhí)行過程。RULE優(yōu)化的過程中不需要任何表或索引的統(tǒng)計(jì)信息,而且也會忽略任何表或索引的統(tǒng)計(jì)信息 。

        2)COST優(yōu)化器:基于開銷的優(yōu)化器相對比較復(fù)雜,通過使用數(shù)據(jù)庫的結(jié)構(gòu)和數(shù)據(jù)等信息來選擇最優(yōu)的執(zhí)行計(jì)劃。優(yōu)化的過程中需要相關(guān)表和索引的統(tǒng)計(jì)信息。

        3)CHOOSE優(yōu)化器:在存在相關(guān)統(tǒng)計(jì)信息的情況下采用基于開銷的優(yōu)化器,在不存在相關(guān)統(tǒng)計(jì)信息的情況下才用基于規(guī)則的優(yōu)化器。這是8I中默認(rèn)的優(yōu)化器工作方式。

        同樣的查詢語句可以有很多不同的執(zhí)行計(jì)劃, Cost優(yōu)化器將自動計(jì)算不同執(zhí)行計(jì)劃所消耗的系統(tǒng)資源, 接著就會選出消耗最低的執(zhí)行計(jì)劃。一條查詢的消耗大致被分為3個(gè)基本成分:I/O消耗、CPU消耗、Network消耗。

        3 Oracle查詢優(yōu)化方法

        3.1 共享SQL語句

        Oracle在執(zhí)行每條SQL語句的時(shí)候都會先對語句進(jìn)行語法分析,而這個(gè)過程是比較消耗資源的,為了能夠省略這個(gè)步驟來提高SQL查詢語句的執(zhí)行效率,Oracle采用的共享SQL語句的方式,就是把每條從用戶發(fā)出的SQL存儲到系統(tǒng)全局區(qū)(System Global Area)的共享池當(dāng)中,這個(gè)共享池當(dāng)中的所有SQL語句將會被數(shù)據(jù)庫當(dāng)中的所有有權(quán)限的用戶共享使用;所以當(dāng)你每天執(zhí)行一條SQL語句時(shí),Oracle會先去共享池當(dāng)中查找是否存在這樣一條SQL語句的執(zhí)行記錄,如果存在則會直接得到該SQL語句的執(zhí)行計(jì)劃和執(zhí)行路徑,這么就大大的節(jié)省了系統(tǒng)內(nèi)存并且提高了SQL語句的執(zhí)行效率。

        如果某一查詢語句需要匹配共享池之中的SQL, 都必須遵照以下三條規(guī)則。

        (1)SQL語句之間字符之間必須滿足相互匹配,另外字符的大小寫也需要相互匹配。

        (2)SQL語句中的對象必須完全一致,對于如下查詢:

        SELECT MAX(EMP_SALARY) FROM EMP LIMIT;

        (3)SQL語句中使用命名的綁定變量必須相同。

        3.2 創(chuàng)建和使用索引

        在關(guān)系型數(shù)據(jù)庫中,索引是數(shù)據(jù)庫之中的一種結(jié)構(gòu)。簡而言之,索引的作用類似于于圖書的目錄,使用索引相當(dāng)于使用這個(gè)目錄快速找到所需文章的內(nèi)容,它能夠使作用于表的查詢語句執(zhí)行得更加有效率。對于一個(gè)數(shù)據(jù)庫而言,索引是必須的,但是對于現(xiàn)在存在的各種大型數(shù)據(jù)庫而言,索引強(qiáng)大的作用大大提升數(shù)據(jù)庫的查詢性能,使其變成了現(xiàn)在數(shù)據(jù)庫不可或缺的重要部分。索引的創(chuàng)建需要去維護(hù), 如果對表進(jìn)行更新、插入、刪去等操作的時(shí)候, 已經(jīng)創(chuàng)建好的索引也需要去更新。因而在使用索引時(shí)需要遵循以下的原則:

        (1)對于查詢操作時(shí)較少需要的列或者列的重復(fù)值比較多,則不需要建立索引。

        (2)對于按范圍查詢的列,最好建立索引。

        (3)如果一個(gè)表中包含了主鍵和外鍵,則必須要建立索引。

        (4)對于一些特殊的數(shù)據(jù)類型,不要建立索引。

        (5)如果對數(shù)據(jù)庫經(jīng)常執(zhí)行一些插入、刪除等操作時(shí), 不能建立太多索引。

        (6)索引可以跟Where語句的集合融為一體。

        當(dāng)創(chuàng)建完索引后, 若SQL查詢語句存在如下情況, 優(yōu)化器生成的執(zhí)行計(jì)劃也會對創(chuàng)建好的所有進(jìn)行使用, 因而盡量不要發(fā)生以下狀況。

        (1)如果對某個(gè)表中的兩個(gè)列進(jìn)行比較時(shí),索引必不會一直被數(shù)據(jù)庫使用;

        如查詢操作:SELECT EMP_NO,EMP_SALARY FROM EMP WHERE EMP_NO>MGR;

        (2)數(shù)據(jù)庫的表中存在NULL值。一般索引中并不會有NULL值。如果WHERE條件語句中出現(xiàn)is null或者is not null時(shí), 索引就無效,不能被使用;

        如下查詢將將不會對comm列的索引進(jìn)行訪問:

        SELECT EMP_NAME,EMP_NO,EMP_SALARY FROM EMP WHERE COMM IS NOT NULL;

        (3)在WHERE條件語句中使用了不等于操作符號,如!=、<>;

        SELECT EMP_NAME, EMP_SALARY,EMP_NO,DEPT_NO FROM EMP WHERE DEPT_NO<>20 GROUP BY EMP_NO;

        可以改寫為:

        SELECT EMP_NAME, EMP_SALARY,EMP_NO,DEPT_NO FROM EMP WHERE DEPT_NO<20 OR DEPT_NO>20 GROUP BY EMP_NO;

        (4)當(dāng)對操作的列使用函數(shù)時(shí);

        SELECT EMP_NAME, EMP_NO, JOB,EMP_SALARY FROM EMP WHERE UPPER(DEPT_NO)=10 GROUP BY EMP_NO;

        可以創(chuàng)建基于函數(shù)的索引來優(yōu)化此查詢。

        Create index DEPT_NO_idx on emp(upper(DEPT_NO));

        (5)對相互不匹配的數(shù)據(jù)類型進(jìn)行比較,DEPT_NO字段類型是VARCHAR(2), 如查詢:

        SELECT EMP_NAME, EMP_NO, EMP_SALARY FROM EMP WHERE DEPT_NO=30;

        Oracle數(shù)據(jù)庫可以自動把where條件子句變成TO_NUMBER(DEPT_NO)=30, 這樣就限制了DEPT_NO列上索引的使用。

        3.3 重寫SQL語句

        3.3.1 使用WHERE代替HAVING

        盡量不要使用HAVING條件子句, HAVING在檢索出所有滿足條件的結(jié)果集之后,再對這些結(jié)果集進(jìn)行篩選。 整個(gè)的處理過程需要排序,總計(jì)等一系列操作。而WHERE條件子句限制了記錄的數(shù)量,減少資源在這方面的消耗;

        例如查詢語句:

        SELECT EMP_NAME,EMP_NO, AVG(EMP_SALARY) FROM EMP GROUP BY DEPT_NO HAVING EMP_NO<1000;

        對表EMP中所有列項(xiàng)進(jìn)行匯總之后, 再踢除EMP_NO小于1000的記錄, 可以將上列查詢語句改寫為如下語句:

        SELECT EMP_NAME,EMP_NO, AVG(EMP_SALARY) FROM EMP WHERE EMP_NO<1000 GROUP BY DEPT_NO ;

        原因是在匯總前排除掉了EMP_NO<1000的記錄,從而提升了查詢語句的執(zhí)行效率。

        3.3.2 UNION ALL代替UNION

        當(dāng)我們需要UNION兩個(gè)查詢結(jié)果集合時(shí),這兩個(gè)結(jié)果集將會以UNION ALL的方式被Oracle數(shù)據(jù)庫進(jìn)行合并,然后進(jìn)行排序后再輸出結(jié)果。如果使用 UNION ALL替代UNION,就不需要這樣的排序了,將會提升Oracle數(shù)據(jù)庫的查詢執(zhí)行的效率。另外特別注意的是,UNION ALL會重復(fù)輸出兩個(gè)結(jié)果集中相同的記錄。

        3.3.3 使用DECODE函數(shù)

        使用DECODE函數(shù)能夠避免重復(fù)掃描表中相同的數(shù)據(jù)。

        例如:

        SELECT COUNT(EMP_NO),SUM(EMP_SALARY) FROM EMP WHERE DEPT_NO = 20 AND EMP_NAME LIKE‘JORDAN%;

        SELECT COUNT(EMP_NO),SUM(EMP_SALARY) FROM EMP WHERE DEPT_NO = 30 AND EMP_NAME LIKE‘JORDAN%;

        可以使用DECODE函數(shù)高效的得出查詢結(jié)果:

        SELECT COUNT(DECODE(DEPT_NO,20,X,NULL))

        D20_COUNT,COUNT(DECODE(DEPT_NO,30,X,NULL))

        D30_COUNT,SUM(DECODE(DEPT_NO,20,EMP_SALARY,NULL))

        D20_EMP_SALARY,SUM(DECODE(DEPT_NO,30,EMP_SALARY,NULL))

        D30_EMP_SALARY FROM EMP WHERE EMP_NAME LIKE‘JORDAN%;

        3.3.4 用TRANCATE代替DELETE

        當(dāng)刪除表中的記錄時(shí),在一般情形中,回滾段存放的是能夠被恢復(fù)的信息。如果事務(wù)沒有被提交,Oracle數(shù)據(jù)庫之中的數(shù)據(jù)將會恢復(fù)到最初沒有被刪除的的狀態(tài),而使用TRUNCATE,被恢復(fù)的信息不再存放于回滾段之中。因而調(diào)用的資源將變得很少,SQL語句執(zhí)行的效率也將變得越快。

        3.3.5 物化視圖查詢重寫

        所謂物化視圖查詢重寫就是,如果初始化參數(shù)query_rewrite_ enabled設(shè)置為TRUE,并且數(shù)據(jù)庫運(yùn)行在COST優(yōu)化模式下,當(dāng)對基表進(jìn)行查詢時(shí),Oracle會自動判斷是否能利用這個(gè)基表的所有包含ENABLE QUERY REWRITE關(guān)鍵字的物化視圖,如果可以且根據(jù)統(tǒng)計(jì)信息判斷通過查詢物化視圖代價(jià)更小,則Oracle數(shù)據(jù)庫的查詢語句將被重寫,通過查詢物化視圖可以得出正確的查詢結(jié)果。

        3.3.6 創(chuàng)建位圖索引

        由于索引是位圖,所以很多時(shí)候可以對這些索引中的位圖進(jìn)行位運(yùn)算(and 和 or),在某些情況下這樣的速度明顯比b樹快;由于位圖索引可以存儲null,所以可以直接通過位圖索引計(jì)數(shù)。

        SELECT EMP_NAME FROM EMP WHERE EMP_SALARY>10000 and JOB=SALES AND DEPT_NO=10;已用時(shí)間:00:00:01.13

        在對EMP_SALARY(月薪), JOB(職位), DEPT_NO(部門編號)三列創(chuàng)建位圖索引, 上面的SQL查詢語句在執(zhí)行時(shí)話費(fèi)時(shí)間為:00:00:00.08, 對比兩次執(zhí)行所消耗的時(shí)間,SQL執(zhí)行的效率的得到提升,若將這種方式使用到實(shí)際應(yīng)用當(dāng)中,數(shù)據(jù)庫性能的提升等級不言而喻。

        4 備注

        本文所用的表為EMP(員工表),表中字段結(jié)構(gòu)為EMP_NO(員工編號)、EMP_NAME(員工姓名)、DEPT_NO(所屬部門編號)、EMP_SALARY(工資)、JOB(職位)等;DEPT(部門表),表中字段結(jié)構(gòu)為DEPT_NAME(部門名稱),DEPT_NO(部門編號),DEPT_LOC(部門所在城市)等。

        【參考文獻(xiàn)】

        [1]張學(xué)義,王觀玉,黃雋.基于Oralce數(shù)據(jù)庫SQL查詢優(yōu)化研究[J].制造業(yè)自動化,2011(02).

        [2]劉星.Oracle數(shù)據(jù)庫的性能優(yōu)化與調(diào)整[J].科技資訊,2011(04).

        [3]張學(xué)琴. 基于Oracle數(shù)據(jù)庫的SQL語句優(yōu)化[J].電腦知識與技術(shù),2010(01).

        [責(zé)任編輯:湯靜]

        猜你喜歡
        Oracle數(shù)據(jù)庫效率優(yōu)化
        超限高層建筑結(jié)構(gòu)設(shè)計(jì)與優(yōu)化思考
        民用建筑防煙排煙設(shè)計(jì)優(yōu)化探討
        關(guān)于優(yōu)化消防安全告知承諾的一些思考
        一道優(yōu)化題的幾何解法
        提升朗讀教學(xué)效率的幾點(diǎn)思考
        甘肅教育(2020年14期)2020-09-11 07:57:42
        Oracle數(shù)據(jù)庫安全問題分析及策略
        Oracle數(shù)據(jù)庫的存儲結(jié)構(gòu)
        一種Oracle數(shù)據(jù)庫表空間監(jiān)控方法
        《Oracle數(shù)據(jù)庫》課程教學(xué)模式探究 
        跟蹤導(dǎo)練(一)2
        神马不卡影院在线播放| 亚洲av有码精品天堂| 国产免费视频一区二区| 日本一区二区午夜视频| av免费资源在线观看| 风韵丰满妇啪啪区老老熟女杏吧| 国产午夜精品综合久久久| 一本色道久久亚洲综合| 草草地址线路①屁屁影院成人 | 中文字幕人妻少妇精品| 91色综合久久熟女系列| 精品一区二区三区芒果| 国产精品a免费一区久久电影| 国产成人综合久久亚洲精品| 亚洲AV永久无码制服河南实里| 精品九九视频| 亚洲一区精品一区在线观看| 虎白m粉嫩小在线播放| 97精品人人妻人人| 老师粉嫩小泬喷水视频90| 国内露脸中年夫妇交换| 精品九九视频| 亚洲精彩av大片在线观看| 国产内射一级一片内射视频| 亚洲免费网站观看视频| 亚洲av无码成人yellow| 中文字幕一区二区三区.| 日韩美腿丝袜三区四区| 免费观看a级毛片| 国产免费av片在线观看| 亚洲aⅴ无码日韩av无码网站| 男女上床视频免费网站| 日韩av一区二区蜜桃| 曰韩无码无遮挡a级毛片| 亚洲av鲁丝一区二区三区| 偷拍女厕尿尿在线免费看| 国产精品一区二区三区在线蜜桃| 女局长白白嫩嫩大屁股| 亚洲xxxx做受欧美| 91国在线啪精品一区| 蜜桃视频网站在线免费观看|