国产日韩欧美一区二区三区三州_亚洲少妇熟女av_久久久久亚洲av国产精品_波多野结衣网站一区二区_亚洲欧美色片在线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查詢語句效率的高低相比可能有上百倍之差。下文將重點介紹了Oracle數(shù)據(jù)庫SQL查詢語句的處理過程, 再對不同優(yōu)化器工作原理進行了解析, 分析若干方法去提升Oracle數(shù)據(jù)庫查詢優(yōu)化性能, 同時也能使我們進一步去了解Oracle數(shù)據(jù)庫,方便我們的工作生活。

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

0 導(dǎo)言

數(shù)據(jù)庫技術(shù)是計算機科學(xué)技術(shù)發(fā)展最快、應(yīng)用最為廣泛的信息技術(shù)之一,數(shù)據(jù)庫技術(shù)已經(jīng)從相對傳統(tǒng)的商業(yè)領(lǐng)域不斷擴大到許多新的領(lǐng)域,如醫(yī)療、多媒體、經(jīng)濟、政治、科研等領(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)化研究就是進行有目的性的優(yōu)化其組件以改善Oracle數(shù)據(jù)庫的性能,即增加數(shù)據(jù)庫的吞吐量和減少數(shù)據(jù)庫的響應(yīng)時間,因而從大多數(shù)Oracle系統(tǒng)的應(yīng)用實例來觀察,其中數(shù)據(jù)庫的查詢操作在各種操作中所占的比率是最大的,查詢效率成為了影響Oracle數(shù)據(jù)庫各項性能的關(guān)鍵因素。所以本文將介紹Oracle數(shù)據(jù)庫查詢語句的一些優(yōu)化研究方案。

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

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

1.1 分析

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

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

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

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

(2)語義分析,學(xué)過編譯原理的應(yīng)該會知道這一步是相當(dāng)重要的,Oracle主要是分析SQL語句格式正確與否,各個對象是否存在,以及它是否擁有足夠的權(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中進行表達(dá)式計算,例如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ī)避不需要的全表掃描,但是實際應(yīng)用中還是盡量不使用選擇性優(yōu)化器,而采用的是基于規(guī)則或者基于成本的優(yōu)化器。當(dāng)然,最好是能根據(jù)批量的SQL自己編制一個特定的優(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)

這六種連接方式都存在各自獨特的技術(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ù)據(jù)列,防止其他用戶進行修改。Oracle會先在數(shù)據(jù)庫緩沖中尋找是否存在所需要的數(shù)據(jù)塊,如果存在這樣的數(shù)據(jù)塊,就可以直接進行讀或者修改操作,否則數(shù)據(jù)會被從物理文件之中讀取到數(shù)據(jù)庫緩沖區(qū)中。

1.3 返回結(jié)果

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

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

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

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

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

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

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

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

3.1 共享SQL語句

Oracle在執(zhí)行每條SQL語句的時候都會先對語句進行語法分析,而這個過程是比較消耗資源的,為了能夠省略這個步驟來提高SQL查詢語句的執(zhí)行效率,Oracle采用的共享SQL語句的方式,就是把每條從用戶發(fā)出的SQL存儲到系統(tǒng)全局區(qū)(System Global Area)的共享池當(dāng)中,這個共享池當(dāng)中的所有SQL語句將會被數(shù)據(jù)庫當(dāng)中的所有有權(quán)限的用戶共享使用;所以當(dāng)你每天執(zhí)行一條SQL語句時,Oracle會先去共享池當(dāng)中查找是否存在這樣一條SQL語句的執(zhí)行記錄,如果存在則會直接得到該SQL語句的執(zhí)行計劃和執(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)于使用這個目錄快速找到所需文章的內(nèi)容,它能夠使作用于表的查詢語句執(zhí)行得更加有效率。對于一個數(shù)據(jù)庫而言,索引是必須的,但是對于現(xiàn)在存在的各種大型數(shù)據(jù)庫而言,索引強大的作用大大提升數(shù)據(jù)庫的查詢性能,使其變成了現(xiàn)在數(shù)據(jù)庫不可或缺的重要部分。索引的創(chuàng)建需要去維護, 如果對表進行更新、插入、刪去等操作的時候, 已經(jīng)創(chuàng)建好的索引也需要去更新。因而在使用索引時需要遵循以下的原則:

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

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

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

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

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

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

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

(1)如果對某個表中的兩個列進行比較時,索引必不會一直被數(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時, 索引就無效,不能被使用;

如下查詢將將不會對comm列的索引進行訪問:

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ù)時;

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ù)類型進行比較,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é)果集進行篩選。 整個的處理過程需要排序,總計等一系列操作。而WHERE條件子句限制了記錄的數(shù)量,減少資源在這方面的消耗;

例如查詢語句:

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

對表EMP中所有列項進行匯總之后, 再踢除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兩個查詢結(jié)果集合時,這兩個結(jié)果集將會以UNION ALL的方式被Oracle數(shù)據(jù)庫進行合并,然后進行排序后再輸出結(jié)果。如果使用 UNION ALL替代UNION,就不需要這樣的排序了,將會提升Oracle數(shù)據(jù)庫的查詢執(zhí)行的效率。另外特別注意的是,UNION ALL會重復(fù)輸出兩個結(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)刪除表中的記錄時,在一般情形中,回滾段存放的是能夠被恢復(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ù)庫運行在COST優(yōu)化模式下,當(dāng)對基表進行查詢時,Oracle會自動判斷是否能利用這個基表的所有包含ENABLE QUERY REWRITE關(guān)鍵字的物化視圖,如果可以且根據(jù)統(tǒng)計信息判斷通過查詢物化視圖代價更小,則Oracle數(shù)據(jù)庫的查詢語句將被重寫,通過查詢物化視圖可以得出正確的查詢結(jié)果。

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

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

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

在對EMP_SALARY(月薪), JOB(職位), DEPT_NO(部門編號)三列創(chuàng)建位圖索引, 上面的SQL查詢語句在執(zhí)行時話費時間為:00:00:00.08, 對比兩次執(zhí)行所消耗的時間,SQL執(zhí)行的效率的得到提升,若將這種方式使用到實際應(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è)計與優(yōu)化思考
民用建筑防煙排煙設(shè)計優(yōu)化探討
關(guān)于優(yōu)化消防安全告知承諾的一些思考
一道優(yōu)化題的幾何解法
提升朗讀教學(xué)效率的幾點思考
甘肅教育(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
乌鲁木齐县| 米泉市| 海门市| 泗水县| 芦溪县| 景谷| 加查县| 孟州市| 扶余县| 化隆| 微山县| 额敏县| 辽阳县| 潮州市| 阿鲁科尔沁旗| 江达县| 枣强县| 桦川县| 万年县| 黄龙县| 扎鲁特旗| 开阳县| 伊宁市| 陆良县| 耒阳市| 巨野县| 新乐市| 视频| 礼泉县| 邵阳县| 襄汾县| 确山县| 北安市| 罗江县| 鄱阳县| 洛浦县| 武平县| 北海市| 丹棱县| 富顺县| 云浮市|