ORACLE SQL性能調(diào)優(yōu)

1、ORACLE SQL 性能調(diào)優(yōu)性能調(diào)優(yōu)1序言序言.22影響影響 SQL PERFORMANCE 的關(guān)鍵因素和配置：的關(guān)鍵因素和配置：.22.1關(guān)于執(zhí)行計劃.22.2ORACLE 優(yōu)化器.22.2.1ORACLE優(yōu)化器的優(yōu)化方式.22.2.2優(yōu)化器的優(yōu)化模式(Optermizer Mode) .32.2.3Optimizer mode優(yōu)化模式級別的設定：.32.2.4查看對象統(tǒng)計信息(object statistics).32.3結(jié)合 BENQ ORACLE ERP.42.3.1優(yōu)化模式(Optermizer Mode) .42.3.2關(guān)于 Gather.52.4跟蹤 SQL 實際運行的 COS

2、T.52.5小結(jié).53SQL 語句的語句的 TUNING 經(jīng)驗分享：經(jīng)驗分享：.63.1絕大多數(shù)情況下NOT EXISTS比NOT IN 效率高.63.2UNION ALL 效率比 UNION 高很多.63.3一些很耗資源的 SQL 操作，在不必要的情況下不要使用 .63.4通常聯(lián)接查詢比子查詢的效率要高很多.73.5用TABLE 索引（INDEX）欄位去做 TABLE間的關(guān)聯(lián)，可避免費時的全表掃描 .73.6在 VIEW中盡量不要使用 PACKAGE/FUNCTION 來得到欄位值，.83.7通過 ROWID 訪問表 .93.8必要時，可在 ORACLE STANDARD TABLE上加索引

3、 .93.9合理排列 WHERE 子句中的連接順序.93.10用 WHERE子句替換 HAVING 子句.93.11關(guān)于使用索引（INDEX）的一些注意點.103.11.1!=,NOT操作將不使用索引.103.11.2|是字符連接函數(shù). 就象其它函數(shù)那樣, 停用了索引.103.11.3相同的索引列不能互相比較,這將會啟用全表掃描. .103.11.4避免在索引列上使用計算.113.11.5基于成本的優(yōu)化器(CBO)會對索引的選擇性進行判斷，來決定是否使用索引.113.11.6Index信息的重新統(tǒng)計.113.12識別 “低效運行”的 SQL 語句.11學習報告學習報告1序言序言BenQ Sal

4、e Office自Oracle ERP上線后，隨著資料量的日益加大，目前BQE，BQC，BQP這幾個Site的Performance的問題都表現(xiàn)得越來越明顯，Tunning得工作量也明顯增加?？紤]到影響Oracle SQL Performance的有很多方面，我們這次研究的Tunning先包括兩個大方向：Oracle DB Configuration和SQL Statement Tuning。這份文檔主要將影響SQL Performance的因素做個基礎(chǔ)的闡述，再將大家在平時寫SQL積累的經(jīng)驗做個總結(jié)。2影響影響 SQL Performance 的關(guān)鍵因素和配置：的關(guān)鍵因素和配置：Oracle

5、 Database上的設置對Performance的影響很大，如Shared Pool Size、Buffer Cache Size、SGA Structures、Database I/O Configuration、Rollback Segments等等，這些是DBA要更據(jù)實際狀況取Tunning的部分，我們暫不詳細討論，DBA可對這塊進行補充；這份文檔我們將重點放在影響SQL執(zhí)行效率的一些關(guān)鍵因素和設置上。2.1關(guān)于執(zhí)行計劃關(guān)于執(zhí)行計劃我們在平時工作中用到大量的View，View中SQL的寫法對效率的影響很大，首先有必要了解一條SQL語句是如何被執(zhí)行的。當SQL語句進入Oracle的緩存后

6、，在該語句準備執(zhí)行之前，DBMS將執(zhí)行下列步驟：1.SQL 語法檢查：檢查 SQL 語句拼寫是否正確和詞序。2.SQL 語義分析：核實所有的與數(shù)據(jù)字典不一致的表和列的名字。3.生成執(zhí)行計劃：使用優(yōu)化規(guī)則和數(shù)據(jù)字典中的統(tǒng)計表來決定最佳執(zhí)行計劃。4.建立可執(zhí)行的二進制代碼：基于執(zhí)行計劃，Oracle 生成二進制執(zhí)行代碼。5.抓取并返回需要的數(shù)據(jù)。其中第三步生成執(zhí)行計劃非常關(guān)鍵，所謂執(zhí)行計劃，就是對一個查詢?nèi)蝿?，做出一份怎樣去完成任務的詳細方案。對于查詢而言，我們提交的SQL僅僅是描述出了我們的目的，但Oracle內(nèi)部怎么去得到這些數(shù)據(jù)，是由數(shù)據(jù)庫DBMS來決定的。所以執(zhí)行計劃產(chǎn)生的好壞直接影響SQ

7、L 運行的Performance。我們平時對SQL做一些Tuning，為了得到相同的數(shù)據(jù)而去嘗試用不同的SQL寫法，目的就是能讓Oracle更據(jù)你的語句產(chǎn)生一個更好的執(zhí)行計劃，從而得到更好的效率。2.2ORACLE優(yōu)化器優(yōu)化器在不同的情況下，同一條SQL可能有多種執(zhí)行計劃。但理論上在某一時點，一定只有一種執(zhí)行計劃是最優(yōu)的、花費時間是最少的。執(zhí)行計劃的工作是由優(yōu)化器(Optimizer)來完成的， 那優(yōu)化器是依據(jù)什么訊息去創(chuàng)建出最合理的執(zhí)行計劃？回答這個問題前先要了解一下ORACLE的優(yōu)化器：2.2.1ORACLE優(yōu)化器的優(yōu)化方式優(yōu)化器的優(yōu)化方式學習報告學習報告ORACLE優(yōu)化器的優(yōu)化方式有兩大

8、類，即基于規(guī)則的優(yōu)化方式(Rule-Based Optimization,簡稱為RBO)和基于代價的優(yōu)化方式(Cost-Based Optimization,簡稱為CBO)。A、 RBO方式：優(yōu)化器在分析SQL語句時，更據(jù)數(shù)據(jù)庫中表和索引等定義信息，遵循的是Oracle內(nèi)部預定的一些規(guī)則。比如我們常見的：當一個where子句中的一列有索引時去走索引而不走全表掃描。 B、 CBO方式：依詞義可知,它是看語句的代價(Cost)了?；诖鷥r的查詢，數(shù)據(jù)庫根據(jù)搜集的表和索引的數(shù)據(jù)的統(tǒng)計信息（統(tǒng)計信息通過analyze 命令或者使用dbms_stats包來搜集）綜合來決定選取一個數(shù)據(jù)庫認為最優(yōu)的執(zhí)行計劃

9、（實際上不一定最優(yōu)） 。統(tǒng)計信息給出表的大小 、有多少行、每行的長度等信息。注意：這些統(tǒng)計信息起初在庫內(nèi)是沒有的，是根據(jù) analyze 命令或者dbms_stats包來定期搜集后才出現(xiàn)的，所以很多的時侯過期統(tǒng)計信息會令優(yōu)化器做出一個錯誤的執(zhí)行計劃，因些我們應及時更新這些信息。為了使用基于成本的優(yōu)化器(CBO) , 你必須經(jīng)常運行analyze或dbms_stats命令,以增加數(shù)據(jù)庫中的對象統(tǒng)計信息(object statistics)的準確性。在Oracle8及以后的版本,Oracle強列推薦用CBO的方式。2.2.2優(yōu)化器的優(yōu)化模式優(yōu)化器的優(yōu)化模式(Optermizer Mode)優(yōu)化模式

10、包括Rule，Choose，F(xiàn)irst rows，All rows這四種方式，先解釋一下：1.Rule：即走基于規(guī)則的方式。2.First_Rows：基于成本的方式。指執(zhí)行計劃采用最少資源盡快的返回部分結(jié)果給客戶端，它將是以最快的方式返回查詢的最先的幾行,從總體上減少了響應時間，對于排序分頁頁顯示這種查詢尤其適用。3.All_Rows：基于成本的方式。當一個表有統(tǒng)計信息時，它將以最快的方式返回表的所有的行，從總體上提高查詢的吞吐量。沒有統(tǒng)計信息則走基于規(guī)則的方式。4.Choose：這是我們應關(guān)注的，默認的情況下 Oracle 用的便是這種方式。指的是當一個表或索引有統(tǒng)計信息（指運行過analy

11、ze 命令或者使用過 dbms_stats 包來搜集） ，則走 CBO 的方式 （在 CHOOSE 模式下 ORACLE 采用的是 FIRST_ROWS） ；如果表或索引沒統(tǒng)計信息，那么走 RBO 的方式。注：Oracle ERP 11i 之前的版本，默認用 RULE；Oracle ERP 11i 之后的版本，默認用 CHOOSE。2.2.3Optimizer mode優(yōu)化模式級別的設定：優(yōu)化模式級別的設定：A、Instance 級別：我們可以通過在.ora 文件中設定 OPTIMIZER_MODE=去選用。B、Sessions 級別：通過 SQL ALTER SESSION SET OPTI

12、MIZER_MODE=;來設定。 C、語句級別，這些需要用到 Hint,比如:SELECT /*+ rule */ ordh.order_number,ordl.ordered_item FROM apps.oe_order_headers_all ordh, apps.oe_order_lines_all ordl WHERE ordh.header_id = ordl.header_id;2.2.4查看對象統(tǒng)計信息查看對象統(tǒng)計信息(object statistics)對 CBO 模式，對象統(tǒng)計信息至關(guān)重要。如何查看對象統(tǒng)計信息(object statistics)？Oracle 中關(guān)于表的

13、統(tǒng)計信息是在數(shù)據(jù)字學習報告學習報告典中的，可以下 SQL 查詢到，eg：SELECT table_name,num_rows, blocks, empty_blocks AS empty, avg_space, chain_cnt, avg_row_len FROM dba_tables WHERE owner = ONT AND table_name = OE_ORDER_LINES_ALLTABLE_NAMENUM_ROWSBLOCKSEMPTYAVG_SPACECHAIN_CNTAVG_ROW_LENOE_ORDER_LINES_ALL5344505500441可以看到數(shù)據(jù)字典中統(tǒng)計到的

14、該表有 5344 筆記錄，我們下 SQL 驗證一下：select count(*) from apps.OE_ORDER_LINES_ALL 發(fā)現(xiàn)返回是 16518 筆記錄，可見這個表的統(tǒng)計信息是比較陳舊的，真實數(shù)據(jù)與統(tǒng)計到的數(shù)據(jù)有較大的差別。在這種情況下，如果某個 View 用到此 Table，且系統(tǒng)使用 CBO 的方式，則可能導致 Oracle 的 optimizer 給出效率低下的執(zhí)行計劃。此時可以用 ANALYZE 去重新統(tǒng)計 OE_ORDER_LINES_ALL 這個表，可以下 SQL：ANALYZE TABLE ONT.OE_ORDER_LINES_ALL COMPUTE STAT

15、ISTICS;再次 Query 數(shù)據(jù)字典：TABLE_NAMENUM_ROWSBLOCKSEMPTYAVG_SPACECHAIN_CNTAVG_ROW_LENOE_ORDER_LINES_ALL1651815301035865257643發(fā)現(xiàn)此時的信息已是最新的了。有了比較正確的統(tǒng)計信息，optimizer 才能給出高效的執(zhí)行計劃。2.3結(jié)合結(jié)合BenQ Oracle ERP2.3.1優(yōu)化模式優(yōu)化模式(Optermizer Mode)我們再來看一下我們 ERP 的 DB 的設置情況：以下是從我們 ERP 中 BQE Production 環(huán)境取到的一些設置：/disk/BQE/bqedb/9.

16、2.0/dbs/ifilecbo.ora# optimizer_mode# Prior to 11i, optimizer_mode was always set to rule. For 11i,# choose is mandatory. Although Applications modules will set the# optimizer mode to either first_rows or all_rows, depending on whether# online or batch, an Applications database MUST BE STARTED with

17、the# optimizer mode set to CHOOSE. Many of the system dictionary views,in # particular export, still require the RBO.# # In general, the profile options will ensure that on-line users use# first_rows, batch jobs use all_rows. #學習報告學習報告# IMPORTANT : The CBO requires accurate table and index statistic

18、s,# and FND_STATS should be run regularly. See the FND# documentation for further details.#optimizer_mode = choose 可以看到默認 Oracle ERP11i 用的 optimizer_mode 是 choose，且 Oracle 強烈建議要定期運行 FND_STATS。2.3.2關(guān)于關(guān)于 Gather說到定期運行 FND_STATS，不知大家是否會聯(lián)想到 Oracle ERP 中的一個 Request：Gather？ Oracle ERP 中有幾個與Gather 有關(guān)的標準 Req

19、uest：Gather All Column Statistics-FND_STATS.GATHER_ALL_COLUMN_STATS()Gather Column Statistics-FND_STATS.GATHER_COLUMN_STATS()Gather Schema Statistics-FND_STATS.GATHER_SCHEMA_STATS()Gather Table Statistics-FND_STATS.GATHER_TABLE_STATS()查看 FND_STATS 這個 Package 的寫法，其實它就是在調(diào)用 Oracle DB 中 Standard 的 Packa

20、ge dbms_stats 中的某些 Function。Oracle DB 中常用的 Gather 有以下一些，DBA 也可以直接在 Database 級別上定期 Run 這些 Function，以便能讓Oracle 統(tǒng)計到最新的數(shù)據(jù)庫狀況：dbms_stats.gather_database_stats();dbms_stats.gather_schema_stats();dbms_stats.gather_table_stats(); dbms_stats.gather_index_stats();2.4跟蹤跟蹤 SQL實際運行的實際運行的Cost執(zhí)行計劃是 Oracle 更據(jù)一些統(tǒng)計信息

21、去“估計”出各個步驟所耗的 Cost，與實際的執(zhí)行過程所耗 Cost 不見得一樣。實際執(zhí)行過程耗的 CPU、Disk IO 等資源的數(shù)量可以通過 sql_trace 統(tǒng)計出來。所以 Tuning SQL 不僅要看“執(zhí)行計劃” ，有時還必須結(jié)合 trace 的 Log 去分析。For example，若我要查某段程序運行過程的所有SQL的Performance情況;可以程序邏輯開始前加上sql_trace=true，結(jié)束前結(jié)束sql_trace=false即可：alter session set sql_trace=true;程序邏輯herealter session set sql_trace

22、=false;然后去OS上去找出這個trace file，用tkprof 去轉(zhuǎn)換，然后再看Log的詳細內(nèi)容。DEV2: /disk/DEV2/dev2db/9.2.0/admin/DEV2/udumptkprof dev2_ora_13148.trc log.txt附檔是轉(zhuǎn)出來的例子, 記得 , CPU + DISK 用的比較少的, 就會比較好!學習報告學習報告2.5小結(jié)小結(jié)更據(jù)以上一些理論和我們 ERP 上的實際狀況，我們可以得到一些建議：（1）因為在 Instance Level 我們的 optimizer_mode = choose ，所以定期運行 ANALYZE 或 dbms_stat

23、s 非常重要，尤其是當上次統(tǒng)計后，數(shù)據(jù)量已發(fā)生較大變化之后。注意：統(tǒng)計操作是很耗資源的動作，要在系統(tǒng) Loading 小的時候進行。（2）因為 optimizer_mode 優(yōu)化模式可以設定 Sessions 級別和語句級別，所以必要時可以通過改 optimizer_mode 的方式讓提高 Performance。例如，某報表的 View 是 EIS 類型的，需要一次抓得所有資料，則可以使用 Hint 的方式使該 SQL 的 optimizer_mode= ALL_ROWS，讓 Oracle 優(yōu)化器產(chǎn)生更好的執(zhí)行計劃。學習報告學習報告3SQL 語句的語句的 Tuning 經(jīng)驗分享：經(jīng)驗分享：3

24、.1絕大多數(shù)情況下絕大多數(shù)情況下not exists比比not in 效率高效率高低效:（DEV2: 5秒）SELECT ordl.ordered_item FROM apps.oe_order_lines_all ordl WHERE ordl.header_id not IN (SELECT header_id FROM apps.oe_order_headers_all ordh WHERE ordh.flow_status_code = CLOSED);高效:（DEV2: 1秒）SELECT ordl.ordered_item FROM apps.oe_order_lines_all

25、ordl WHERE not EXISTS (SELECT 1 FROM apps.oe_order_headers_all ordh WHERE ordh.flow_status_code = CLOSED AND ordh.header_id = ordl.header_id);注：exists和 in 相比，效率有高有低，沒有明顯的差別。3.2UNION ALL效率比效率比UNION高很多高很多（DEV2: 5秒）SELECT BQC_ORDER, ordh.order_number FROM apps.oe_order_headers_all ordh WHERE _i

26、d = 82UNION SELECT WGQ_ORDER, ordh.order_number FROM apps.oe_order_headers_all ordh WHERE _id = 93-（DEV2: 0.5秒）SELECT BQC_ORDER, ordh.order_number FROM apps.oe_order_headers_all ordh WHERE _id = 82UNION ALLSELECT WGQ_ORDER, ordh.order_number FROM apps.oe_order_headers_all ordh WHERE

27、_id = 933.3一些很耗資源的一些很耗資源的SQL操作，在不必要的情況下不要使用操作，在不必要的情況下不要使用Select *，Order by，Group by，Distinct, UNION, MINUS，INTERSECT 操作是相當耗時的，在View中能不使用就不要使用，Eg 1: order by（DEV2:0.4秒）select * from apps.oe_order_lines_all ordl; -（DEV2:23秒）select * from apps.oe_order_lines_all ordl order by ordl.creation_dat

28、e; -Eg 2: group by（DEV2: 9秒）學習報告學習報告SELECT SUBSTR (ordh.order_number, 1, 50) AS order_number, ( NVL (SUM (ordl.ordered_quantity * ordl.unit_selling_price), 0) + NVL (SUM (ordl.tax_value), 0) AS amount FROM apps.oe_order_headers_all ordh, apps.oe_order_lines_all ordl WHERE ordh.header_id = ordl.heade

29、r_idGROUP BY SUBSTR (ordh.order_number, 1, 50); -（DEV2: 0.5秒） SELECT SUBSTR (ordh.order_number, 1, 50) AS order_number, (SELECT ( NVL (SUM (ordl.ordered_quantity * ordl.unit_selling_price), 0) + NVL (SUM (ordl.tax_value), 0) FROM apps.oe_order_lines_all ordl WHERE header_id = ordh.header_id) as amou

30、nt FROM apps.oe_order_headers_all ordh;-Eg 3: distinct（DEV2: 50秒）SELECT DISTINCT rctl.customer_trx_line_id, rctl.inventory_item_id, rctl.description item_desc, rctl.extended_amount extended_amount, itm.attribute1 AS item_type FROM apps.ra_customer_trx_lines_all rctl, apps.mtl_system_items_b itm WHER

31、E rctl.inventory_item_id = itm.inventory_item_id(+) AND rctl.line_type = LINE;-（DEV2: 0.1秒）SELECT rctl.customer_trx_line_id, rctl.inventory_item_id, rctl.description item_desc, rctl.extended_amount extended_amount, (SELECT itm.attribute1 FROM apps.mtl_system_items_b itm WHERE rctl.inventory_item_id

32、= itm.inventory_item_id AND ROWNUM = 1) item_type FROM apps.ra_customer_trx_lines_all rctl, apps.ra_customer_trx_all rcta WHERE rctl.customer_trx_id = rcta.customer_trx_id AND rctl.line_type = LINE;通常, 帶有UNION, MINUS , INTERSECT的SQL語句都可以用其它方式重寫.如果你的數(shù)據(jù)庫的SORT_AREA_SIZE調(diào)配得好, 使用UNION, MINUS, INTERSECT也是

33、可以考慮的, 畢竟它們的可讀性很強。3.4通常聯(lián)接查詢比子查詢的效率要高很多通常聯(lián)接查詢比子查詢的效率要高很多盡量在 SELECT 子句里面用聯(lián)接查詢，少子查詢。因為Subquery所得到的子Table的數(shù)據(jù)量等訊息是Oracle無法事前統(tǒng)計出來的，所以優(yōu)化器也很難得出一個優(yōu)化過的執(zhí)行計劃。子查詢在以下情況比較適合：1.適合用于小 table。也就是說, 以大tables為base做join, 小table 做 subquery. (大 tables 之間最好用直接 join)2.不想該 table 成為限制條件時也可以勉強使用。3.需要取subquery 中summary 的值時可以考慮 -

34、 因為 Group by (和 distinct)也是 SQL performance 的一大挑戰(zhàn)。3.5用用table 索引（索引（index）欄位去做）欄位去做Table間的關(guān)聯(lián)間的關(guān)聯(lián)，可避免費時的全表掃描，可避免費時的全表掃描如果 Table 上有索引，則系統(tǒng)訪問帶索引的 Field 時，可通過訪問索引中的欄位來快速獲得相對應記錄的 ROWID，而通常情況下，使用索引比全表掃描要塊幾倍乃至幾千倍。Oracle ERP 中幾乎所有的 Table 都設有 Index，盡量以 Index 中的欄位做 join，避免用我們認為值是唯一的欄位去串: 例如 sales order number;

35、transaction number; inventory org code 等eg，使用OM Sale Order的主檔表oe_order_headers_all 和fnd_lookup_values 串一個簡單的sql（這兩張table的數(shù)據(jù)量都會很大）fnd_lookup_values的index有兩個：學習報告學習報告INDEX applsys.fnd_lookup_values_u1 ON applsys.fnd_lookup_values ( lookup_type ASC, view_application_id ASC, lookup_code ASC, security_gr

36、oup_id ASC, language ASC )INDEX applsys.fnd_lookup_values_u2 ON applsys.fnd_lookup_values ( lookup_type ASC, view_application_id ASC, meaning ASC, security_group_id ASC, language ASC )SQL1:SELECT order1.order_number,flv.meaning FROM apps.fnd_lookup_values flv,APPS.oe_order_headers_all order1WHERE or

37、der1.shipping_method_code = flv.lookup_code使用時間：1.67sec，數(shù)據(jù)：22819筆（BQC環(huán)境）。這里只關(guān)聯(lián)了一個條件lookup_code，lookup_code的確是fnd_lookup_values Index：fnd_lookup_values_u1中的一個欄位，但觀察執(zhí)行計劃，它沒有去用Index。為什么？因為Oracle中，用作Index的多個COLUMN是有順序的，就剛才那個例子用lookup_code做關(guān)聯(lián)，它是在Index中第三位。所以，Oracle優(yōu)化器會去分析：如果使用INDEX的話，就要先FULL SCAN lookup_

38、type，接著是view_application_id，再找到lookup_code ，分析結(jié)果后認為這樣反而比FULL SCAN TABLE還要慢，所以執(zhí)行計劃沒有使用INDEX，直接采用全表掃描。有些文檔直接說：如果索引是建立在多個列上， 只有在它的第一個列(leading column) 被where子句引用時，優(yōu)化器才會選擇使用該索引。所以為了讓使用fnd_lookup_values的fnd_lookup_values_u1 index，用一下SQL:SQL2：SELECT order1.order_number,flv.meaningFROM apps.fnd_lookup_valu

39、es flv,APPS.oe_order_headers_all order1WHERE order1.shipping_method_code = flv.lookup_codeand flv.LOOKUP_TYPE=SHIP_METHOD時間：0.00sec（BQC環(huán)境）學習報告學習報告這個SQL中使用了LOOKUP_TYPE=SHIP_METHOD ，這個正好是INDEX的第一位。Oracle優(yōu)化器分析：執(zhí)行計劃如果使用INDEX，就馬上找到LOOKUP_TYPE=SHIP_METHOD 的INDEX記錄，COST最小，分析的結(jié)果就是使用了INDEX， performance得到提升。3

40、.6在在View中盡量不要使用中盡量不要使用 Package/function 來得到欄位值，來得到欄位值，在view中盡量不要引用function，否則會增加一定的通訊開銷。簡單的判斷盡量用decode，nvl，case when等實現(xiàn)。3.7通過通過ROWID訪問表訪問表 ORACLE 采用兩種訪問表中記錄的方式: a. 全表掃描 全表掃描就是順序地訪問表中每條記錄. ORACLE采用一次讀入多個數(shù)據(jù)塊(database block)的方式優(yōu)化全表掃描. b. 通過ROWID訪問表 如果可以，強烈采用基于ROWID的訪問方式情況以提高訪問表的效率。ROWID包含了表中記錄的物理位置信息，O

41、RACLE采用索引(INDEX)實現(xiàn)了數(shù)據(jù)和存放數(shù)據(jù)的物理位置(ROWID)之間的聯(lián)系， 通常索引提供了快速訪問ROWID的方法，因此那些基于索引列的查詢就可以得到性能上的提高。 3.8必要時，可在必要時，可在Oracle Standard Table上加索引上加索引eg:BQC 收單用到如下一個簡單的 SQL，耗時要 1 秒左右；測試后發(fā)現(xiàn)只要用到 flow_status_code 欄位，速度就很慢。SELECT ordl.ordered_item AS inventory_item, ordl.subinventory, (NVL (ordl.ordered_quantity, 0) AS

42、 qty FROM apps.oe_order_lines_all ordl WHERE ordl.flow_status_code = ENTERED于是手工在 apps.oe_order_lines_all 加上索引：CREATE INDEX ont.oe_order_lines_q1 ON ont.oe_order_lines_all (flow_status_code ASC)再次運行此 SQL，耗時基本為 0 秒。學習報告學習報告3.9合理排列合理排列WHERE子句中的連接順序子句中的連接順序ORACLE 采用自下而上的順序解析 WHERE 子句，根據(jù)這個原理，那些可以過濾掉最大數(shù)量

43、記錄的條件最好寫在 WHERE 子句的末尾。雖然對簡單 SQL，Oracle 優(yōu)化器自動會去調(diào)整順序，但還是建議將能過濾掉最多記錄的 Where 條件放在最后。3.10 用用Where子句替換子句替換HAVING子句子句 避免使用 HAVING 子句, HAVING 只會在檢索出所有記錄之后才對結(jié)果集進行過濾。 這個處理需要排序,總計等操作。 如果能通過 WHERE 子句限制記錄的數(shù)目，那就能減少這方面的開銷。 例如: 低效：SELECT ordl.ordered_item, sum(ordl.ordered_quantity*ordl.unit_selling_price) FROM app

44、s.oe_order_lines_all ordlgroup by ordl.ordered_itemhaving ordl.ordered_item like 00%;高效： SELECT ordl.ordered_item, sum(ordl.ordered_quantity*ordl.unit_selling_price) FROM apps.oe_order_lines_all ordl where ordl.ordered_item like 00%group by ordl.ordered_item;3.11關(guān)于使用索引（關(guān)于使用索引（Index）的一些注意點）的一些注意點而通常情

45、況下，使用索引比全表掃描要塊幾倍至幾千倍，所以對索引要有比較深入的了解。某些情況下 SELECT 語句中的 WHERE 子句用到索引列，但生成的執(zhí)行計劃卻不不使用索引。這里有一些例子. 3.11.1!=,NOT操作將不使用索引操作將不使用索引. 記住, 索引只能告訴你什么存在于表中, 而不能告訴你什么不存在于表中 不使用索引: SELECT ACCOUNT_NAME FROM TRANSACTION WHERE AMOUNT !=0; 3.11.2 |是字符連接函數(shù)是字符連接函數(shù). 就象其它函數(shù)那樣就象其它函數(shù)那樣, 停用了索引停用了索引. 不使用索引: SELECT ACCOUNT_NAME,AMOUNT FROM TRANSACTION 學習報告學習報告WHERE ACCOUNT_NAME|ACCOUNT_TYPE=AMEXA; 使用索引: SELECT ACCOUNT_NAME,AMOUNT FROM TRANSACTION WHERE ACCOUNT_NAME = AMEX AND ACCOUNT_TYPE= A; 3.11.3相同的索引列不能互相比較相同的索引列不能互相比較,這將會啟用全表掃描這將會啟用全表掃描. 不使用索引: SELECT ACCOUNT_NAME, AMOUNT FROM TRANSACTION WHERE ACCOUNT_NAME = NVL(:A