基于ERP數(shù)據(jù)中Oracle查詢優(yōu)化技術(shù)研究

摘 要：隨著信息化技術(shù)的快速發(fā)展，人們獲取信息的求知欲也在水漲船高，而oracle系統(tǒng)只側(cè)重于通過開發(fā)來實現(xiàn)企業(yè)業(yè)務(wù)流程，匹配企業(yè)管理，關(guān)注開發(fā)質(zhì)量和后期的維護(hù)。所以為了在實踐中高效、準(zhǔn)確、快速地獲得數(shù)據(jù)，筆者研究了0racle的查詢優(yōu)化技術(shù)，通過對oracle索引性能的闡述，重點就oracle查詢優(yōu)化技術(shù)做了探討和研究。

關(guān)鍵詞：0racle；物資數(shù)據(jù)；查詢優(yōu)化技術(shù)

Oracle ERP整個企業(yè)一個“控制中心”為最好地利用企業(yè)的資源，這一“控制中心”將收集不同部門的狀態(tài)信息和進(jìn)度報表，然后提供給其他部門。信息由用戶實時更新，且有需要的用戶可在任何時候訪問。模塊化方法許多企業(yè)并不需要典型 ERP 套件中提供的所有功能。因此這些套件通常以模塊化形式提供，允許公司僅實施其所需的模塊，以后需要的話再添加更多模塊。Oracle 還提供一系列行業(yè)特定的模塊，納入了用于特定行業(yè)的最佳實踐功能和流程。Oracle采用自下而上的順序解析WHERE子句，根據(jù)這個原理，表之間的連接必須寫在其他Where條件之前。

一、Oracle 數(shù)據(jù)庫系統(tǒng)

Oracle 數(shù)據(jù)庫系統(tǒng)是目前最流行的客戶/服務(wù)器（CLIENT/SERVER）或B/S體系結(jié)構(gòu)的數(shù)據(jù)庫之一。Oracle數(shù)據(jù)庫是目前世界上使用最為廣泛的數(shù)據(jù)庫管理系統(tǒng)，作為一個通用的數(shù)據(jù)庫系統(tǒng)，它具有完整的數(shù)據(jù)管理功能；作為一個關(guān)系數(shù)據(jù)庫，它是一個完備關(guān)系的產(chǎn)品；作為分布式數(shù)據(jù)庫它實現(xiàn)了分布式處理功能。但它的所有知識，只要在一種機(jī)型上學(xué)習(xí)了ORACLE知識，便能在各種類型的機(jī)器上使用它。Oracle數(shù)據(jù)庫最新版本為Oracle Database 12c。Oracle數(shù)據(jù)庫12c 引入了一個新的多承租方架構(gòu)，使用該架構(gòu)可輕松部署和管理數(shù)據(jù)庫云。

二、Oracle中索引性能及優(yōu)化器選用

（一）Oracle索引性能分析

索引是表的一個概念部分，用來提高檢索數(shù)據(jù)的效率，ORACLE使用了一個復(fù)雜的自平衡B-tree結(jié)構(gòu)。通常，通過索引查詢數(shù)據(jù)比全表掃描要快。當(dāng)ORACLE找出執(zhí)行查詢和Update語句的最佳路徑時， Oracle優(yōu)化器將使用索引。同樣在聯(lián)結(jié)多個表時使用索引也可以提高效率，另一個使用索引的好處是，它提供了主鍵（primary key）的唯一性驗證。那些LONG或LONG RAW數(shù)據(jù)類型，可以索引幾乎所有的列，通常在大型表中使用索引特別有效。在掃描小表時，使用索引同樣能提高效率。影響SQL性能的因素很多，如初始化參數(shù)設(shè)置不合理、導(dǎo)入了不準(zhǔn)確的系統(tǒng)統(tǒng)計數(shù)據(jù)從而影響優(yōu)化程序的正確判斷等，這些與DBA密切相關(guān)。

（二）Oracle優(yōu)化器的選用

Oracle的優(yōu)化器共有3種： RULE （基于規(guī)則）、COST （基于成本）、CHOOSE （選擇性）。設(shè)置缺省的優(yōu)化器，可以通過對init.ora文件中OPTIMIZER_MODE參數(shù)的各種聲明，如Rule，Cost，Choose，ALL_ROWS，F(xiàn)irst_ROWS. 你當(dāng)然也在SQL句級或是會話（session）級對其進(jìn)行覆蓋。為了使用基于成本的優(yōu)化器（CBO， Cost-Based Optimizer）， 你必須經(jīng)常運行analyze 命令，以增加數(shù)據(jù)庫中的對象統(tǒng)計信息（object statistics）的準(zhǔn)確性。如果數(shù)據(jù)庫的優(yōu)化器模式設(shè)置為選擇性（Choose），那么實際的優(yōu)化器模式將和是否運行過analyze命令有關(guān)。如果table已經(jīng)被analyze過， 優(yōu)化器模式將自動成為Cbo。

三、oracle查詢優(yōu)化技術(shù)分析

（一）避免使用IS NOT NULL

避免在索引中使用任何可以為空的列，ORACLE性能上將無法使用該索引。對于單列索引，如果列包含空值，索引中將不存在此記錄。 對于復(fù)合索引，如果每個列都為空，索引中同樣不存在此記錄。如果至少有一個列不為空，則記錄存在于索引中。如果唯一性索引建立在表的A列和B列上， 并且表中存在一條記錄的A，B值為（123，1）， ORACLE將不接受下一條具有相同A，B值（123，1）的記錄（插入）。然而如果所有的索引列都為空，ORACLE將認(rèn)為整個鍵值為空而空不等于空。因此可以插入1000 條具有相同鍵值的記錄。

代碼如下：

低效：（索引失效） SELECT … FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL；

高效：（索引有效） SELECT … FROM DEPARTMENT WHERE DEPT_CODE >=0；

（二）避免改變索引列的類型

當(dāng)比較不同數(shù)據(jù)類型的數(shù)據(jù)時， ORACLE自動對列進(jìn)行簡單的類型轉(zhuǎn)換。假設(shè) EMPNO是一個數(shù)值類型的索引列。SELECT … FROM EMP WHERE EMPNO = ‘123' 實際上，經(jīng)過ORACLE類型轉(zhuǎn)換， 語句轉(zhuǎn)化為： SELECT … FROM EMP WHERE EMPNO = TO_NUMBER（‘123'）。類型轉(zhuǎn)換沒有發(fā)生在索引列上，索引的用途沒有被改變。假設(shè)EMP_TYPE是一個字符類型的索引列，SELECT … FROM EMP WHERE EMP_TYPE = 123 這個語句被ORACLE轉(zhuǎn)換為： SELECT … FROM EMP WHERETO_NUMBER（EMP_TYPE）=123 因為內(nèi)部發(fā)生的類型轉(zhuǎn)換， 這個索引將不會被用到。

（三）避免使用耗費資源的操作

帶有DISTINCT， UNION， MINUS， INTERSECT的SQL語句會啟動SQL引擎執(zhí)行耗費資源的排序（SORT）功能，DISTINCT需要一次排序操作， 而其他的至少需要執(zhí)行兩次排序。通常， 帶有UNION， MINUS ， INTERSECT的SQL語句都可以用其他方式重寫。如果數(shù)據(jù)庫的SORT_AREA_SIZE調(diào)配得好，使用UNION， MINUS， INTERSECT也是可以考慮的， 畢竟它們的可讀性很強(qiáng)。

（四）不同功能的替換操作分析

通常情況下， 用UNION替換Where子句中的OR將會起到較好的效果， 對索引列使用OR將造成全表掃描。此方法只針對多個索引列有效，如果有column沒有被索引， 查詢效率可能會因為你沒有選擇OR而降低，在下面的例子中， LOC_ID 和REGION上都建有索引。如果堅持要用OR， 那就需要返回記錄最少的索引列寫在最前面。當(dāng)SQL語句需要UNION兩個查詢結(jié)果集合時，這兩個結(jié)果集合會以UNION-ALL的方式被合并， 然后在輸出最終結(jié)果前進(jìn)行排序。如果用UNION ALL替代UNION，效率就會因此得到提高，需要注意的是，UNION ALL 將重復(fù)輸出兩個結(jié)果集合中相同記錄。因此各位還是要從業(yè)務(wù)需求分析使用UNION ALL的可行性，UNION 將對結(jié)果集合排序，這個操作會使用到SORT_AREA_SIZE這塊內(nèi)存，對于這塊內(nèi)存的優(yōu)化也是相當(dāng)重要的。

代碼如下：

高效： SELECT LOC_ID ， LOC_DESC ， REGION FROM LOCATION WHERE LOC_ID = 10 UNION ALL SELECT LOC_ID ， LOC_DESC ， REGION FROM LOCATION WHERE REGION = “MELBOURNE”

低效： SELECT LOC_ID ， LOC_DESC ， REGION FROM LOCATION WHERE LOC_ID = 10 OR REGION = “MELBOURNE”

當(dāng)提交一個包含一對多表信息（比如部門表和雇員表）的查詢時，避免在SELECT子句中使用DISTINCT。一般可以考慮用EXIST替換， EXISTS 使查詢更為迅速，因為RDBMS核心模塊將在子查詢的條件一旦滿足后，立刻返回結(jié)果。

（低效）： SELECT DISTINCT DEPT_NO，DEPT_NAME FROM DEPT D， EMP E

WHERE D.DEPT_NO = E.DEPT_NO

And E.sex =man

（高效）： SELECT DEPT_NO，DEPT_NAME FROM DEPT D

WHERE EXISTS

（ SELECT ‘X' FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO

And E.sex =man）；

優(yōu)化GROUP BY： 提高GROUP BY 語句的效率， 可以通過將不需要的記錄在GROUP BY 之前過濾掉，用Where替代Having。下面兩個查詢返回相同結(jié)果但第二個明顯就快了許多。

低效：

SELECT JOB ， AVG（SAL）

FROM EMP GROUP JOB HAVING JOB = ‘PRESIDENT'AND AVG（SAL）>XXX

高效：

SELECT JOB ， AVG（SAL）

FROM EMP

WHERE JOB = ‘PRESIDENT'

OR JOB = ‘MANAGER' GROUP JOB Having AND AVG（SAL）>XXX

綜上所述，復(fù)雜的SQL往往犧牲了執(zhí)行效率，能夠運用函數(shù)解決問題的方法在實際工作中是非常有意義的。數(shù)據(jù)庫最基本的任務(wù)是存儲、管琿數(shù)據(jù)，而終端用戶唯一能看到的數(shù)據(jù)庫特性就是其性能。以ERP項目的物資數(shù)據(jù)的查詢和統(tǒng)計為背景和依據(jù)，分析導(dǎo)致SQL查詢語句性能低下的原因以及SQL優(yōu)化的方法，重點闡述在查詢語句中如何避免對查詢結(jié)果的高成本操作以及在對多表連接時如何提高查詢效率。

參考文獻(xiàn)：

[1]楊德江.數(shù)據(jù)庫SQL查詢技術(shù)的優(yōu)化策略[J].計算機(jī)與現(xiàn)代化，2014.

[2]付忠和.0mcle數(shù)據(jù)庫中大型表查詢優(yōu)化的研究[J].現(xiàn)代電子技術(shù)，2015.