軟件測試中Oracle 10g中新的SQL優化之索引篇

軟件測試中Oracle 10g中新的SQL優化之索引篇
Oracle使用的hints調整機制一直很復雜，Oracle Technical Network對使用hints調整Oracle SQL的過程有很好的全面評述。根據對10g數據庫的介紹，可使用更多新的optimizer hints來控制優化行為?，F在讓我們迅速了解一下這些強大的新hints:

　　spread_min_analysis

　　使用這一hint，你可以忽略一些關于如詳細的關系依賴圖分析等電子表格的編譯時間優化規則。其他的一些優化，如創建過濾以有選擇性的定位電子表格訪問結構并限制修訂規則等，得到了繼續使用。

　　由于在規則數非常大的情況下，電子表格分析會很長。這一提示可以幫助我們減少由此產生的數以百小時計的編譯時間。

　　例如:SELECT /*+ SPREAD_MIN_ANALYSIS */ ...

　　spread_no_analysis

　　通過這一hint，可以使無電子表格分析成為可能。同樣，使用這一hint可以忽略修訂規則和過濾產生。如果存在一電子表格分析，編譯時間可以被減少到最低程度。

　　例如:SELECT /*+ SPREAD_NO_ANALYSIS */ ...

　　use_nl_with_index

　　這項hint使CBO通過嵌套循環把特定的表格加入到另一原始行。只有在以下情況中，它才使用特定表格作為內部表格:如果沒有指定標簽，CBO必須可以使用一些標簽，且這些標簽至少有一個作為索引鍵值加入判斷;反之，CBO必須能夠使用至少有一個作為索引鍵值加入判斷的標簽。

　　例如:SELECT /*+ USE_NL_WITH_INDEX (polrecpolrind) */ ...

　　CARDINALITY

　　此hint定義了對由查詢或查詢部分返回的基數的評價。注意如果沒有定義表格，基數是由整個查詢所返回的總行數。

　　例如:SELECT /*+ CARDINALITY ( [tablespec] card ) */

　　SELECTIVITY

　　此hint定義了對查詢或查詢部分選擇性的評價。如果只定義了一個表格，選擇性是在所定義表格里滿足所有單一表格判斷的行部分。如果定義了一系列表格，選擇性是指在合并以任何順序滿足所有可用判斷的全部表格后，所得結果中的行部分。

　　例如:SELECT /*+ SELECTIVITY ( [tablespec] sel ) */

　　然而，注意如果hints CARDINALITY 和 SELECTIVITY都定義在同樣的一批表格，二者都會被忽略。

　　no_use_nl

　　Hint no_use_nl使CBO執行循環嵌套，通過把指定表格作為內部表格，把每個指定表格連接到另一原始行。通過這一hint，只有hash join和sort-merge joins會為指定表格所考慮。

　　例如:SELECT /*+ NO_USE_NL ( employees ) */ ...

　　no_use_merge

　　此hint使CBO通過把指定表格作為內部表格的方式，拒絕sort-merge把每個指定表格加入到另一原始行。

　　例如:SELECT /*+ NO_USE_MERGE ( employees dept ) */ ...

　　no_use_hash

　　此hint使CBO通過把指定表格作為內部表格的方式，拒絕hash joins把每個指定表格加入到另一原始行。

　　例如:SELECT /*+ NO_USE_HASH ( employees dept ) */ ...

　　no_index_ffs

　　此hint使CBO拒絕對指定表格的指定標簽進行fast full-index scan。

　　Syntax: /*+ NO_INDEX_FFS ( tablespecindexspec ) */

　　no_index_ss

　　此hint使CBO拒絕對指定表格的指定標簽進行skip scan。

　　Syntax: /*+ NO_INDEX_SS (tablespecindexspec ) */

　　no_star_transformation

　　此hint使CBO忽略star 詢問信息。

　　Syntax: /*+ NO_STAR_TRANSFORMATION */

　　index_ss

　　此hint明確地為指定表格選擇index skip scan。如果語句使用index range scan，Oracle將以對其索引值的升序排列來檢查索引入口。在被分割的索引中，其結果為對每個部分內部的升序排列。

　　Syntax: /*+ INDEX_SS (tablespecindexspec) */

　　index_ss_asc

　　此hint明確地為指定表格選擇index skip scan。如果語句使用index range scan，Oracle將以對其索引值的升序排列來檢查索引入口。在被分割的索引中，其結果為對每個部分內部的升序排列。

　　Syntax: /*+ INDEX_SS_ASC (tablespecindexspec) */

　　index_ss_desc

　　此hint明確為指定表格選擇index skip scan。如果語句使用index range scan，Oracle將以對其索引值的降序排列來檢查索引入口。在被分割的索引中，其結果為對每個部分內部的降序排列。

　　Syntax: /*+ INDEX_SS_DESC (tablespecindexspec) */

　　cpu_costing

　　此hint為SQL語句打開CPU costing。這是對優化器的默認評估模式。優化器評估當執行給定查詢時，數據庫需要運行的IO操作數、IO操作種類、以及CPU周期數。

　　Syntax: /*+ CPU_COSTING (tablespecindexspec) */

　　no_cpu_costing

　　此hint為SQL語句關閉CPU costing。然后CBO使用IO cost模式，此模式忽略CPU花費，僅測量在single-block reads中的所有指標。

　　Syntax: /*+ NO_CPU_COSTING */

隨著Oracle優化器越來越成熟，Oracle專家必須不斷增加自己對調整SQL語句的工具儲備。當然，討論所有復雜的Oracle10g SQL新hints遠遠超出了本文的范圍，你可以從Mike Ault的新書Oracle Database 10g New Features中獲得關于Oracle10g的更多信息。

網站速度變慢時，你會考慮到什么解決方法？筆者最近公司網站的速度一起很不理想，文章頁面基本都要 10s 以上才能打開。數據庫用的是Oracle10g Express Edtion，存放文章的表超過了 10 萬數據量，類似執行一個 select count(1) from res where class_id=1 這樣的語句就經常在 10s 以上。然而，今天我在 class_id 上加了一個索引，這條語句的執行時間就幾乎變成了 0s。效果之顯著，完全出乎我的預料。下面是Oracle 10g sql優化相關的總結：

1. 監控 select 語句

（“數據庫主頁”從開始菜單里“Oracle Database 10g Express Edition”的“轉至數據庫主頁”進入）

進入“數據庫主頁>管理>數據庫監視器>會話>SQL”頁面，在“搜索”里輸入“select”，點擊“開始”。這樣就可以看到最近執行的 select 語句。點擊“SQL”列里的 SQL 語句鏈接，查看執行情況。其中，“CPU時間”即為執行所用的時間，“已處理的行數”為查詢結果的行數。下面的“SQL文本”是 SQL 語句的完整內容，“索引”區域顯示該查詢使用了哪個索引。

找到“CUP時間”超過 1 秒的查詢，看是否需要添加索引。

2. 添加索引

進入“數據庫主頁>對象瀏覽器”頁面，在右邊偏上方有一個“創建”按鈕。點擊“創建” 按鈕，在出現的菜單里選擇“索引”，然后輸入“表名”，“索引類型”使用“常規”，再點擊“下一步”。

在新出現的頁面，輸入索引的名稱和索引列。如果是不唯一的字段，最好將“單性值”設置為唯一，可能有助提高檢索速度。再點擊“下一步”，點擊“完成”按鈕。

照我的理解，創建索引時選擇什么索引列，應該是由 select 語句的 where 部分決定的。比如 where id=1 ，只需選擇 id 一個索引列；如果是 where id=1 and name=2 ，則需要選擇 id 和 name 兩個索引列來創建索引。只對主鍵進行索引，因為是唯一的，所以將“單性值”設置為“唯一”。

數據量比較大的表，都應在其主鍵上創建一個索引，其它需要的另加。

order by 語句里的字段，一般無法使用索引，所以，盡量在 order by 之前將查詢結果的行數縮到最少。

原文轉自：http://www.anti-gravitydesign.com