藉助索引+非空優化distinct操作一例

一個同事在做方案的時候，問我一個問題：如何快速從百萬行的資料表中，快速獲取到指定列的所有取定值。

這個問題的困難點在於對資料表資料列的掃描。最直接的方法無過於進行全表掃描+distinct操作。但是，因為資料量的原因，我們通常希望有更加優化的策略和方式。

Distinct+全表掃描方法

我們構建一個簡單是實驗環境，來模擬下執行計劃。

SQL> create table tt as select * from dba_objects;

Table created

SQL> select count(*) from tt;

COUNT(*)

----------

51361 //減小規模

SQL> desc tt;

Name Type Nullable Default Comments

-------------- ------------- -------- ------- --------

OWNER VARCHAR2(30) Y

OBJECT_NAME VARCHAR2(128) Y

（篇幅原因，有省略…）

TEMPORARY VARCHAR2(1) Y

GENERATED VARCHAR2(1) Y

SECONDARY VARCHAR2(1) Y

注意，owner列此時是可空列選項。我們希望知道一共有多少個owner取值存在。

SQL> explain plan for select distinct owner from tt;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 3008180766

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 24 | 144 | 174 (6)| 00:00:03 |

| 1 | HASH UNIQUE | | 24 | 144 | 174 (6)| 00:00:03 |

| 2 | TABLE ACCESS FULL| TT | 51361 | 300K| 167 (2)| 00:00:03 |

---------------------------------------------------------------------------

9 rows selected

注意這個執行計劃的細節：對資料表TT的全表掃描。Oracle CBO選擇對資料表TT的所有資料塊掃描一遍，獲取到所有的owner取值。之後，利用hash操作將相同的owner值進行合併。

優化嘗試一

一種優化的思路就是針對全表掃描最大的消耗點進行優化。預設方案中，對資料表所有資料表進行掃描，是效能和資源消耗最大的部分。思考如何在儘可能少IO塊讀取的情況下，獲取到所有owner列表。

答案是利用owner列上的索引。如果我們新增了owner列的索引，就可以將所有owner值彙集到索引的葉子節點上。這樣就在少量索引塊上集中了所有的owner值。

SQL> explain plan for select distinct owner from tt;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 3008180766

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 27 | 162 | 174 (6)| 00:00:03 |

| 1 | HASH UNIQUE | | 27 | 162 | 174 (6)| 00:00:03 |

| 2 | TABLE ACCESS FULL| TT | 51361 | 300K| 167 (2)| 00:00:03 |

---------------------------------------------------------------------------

9 rows selected

出現了一些問題，CBO沒有像我們預想的那樣掃描索引結構，而是依然掃描資料表，之後進行Hash操作。

原因是什麼呢？猜想是這樣。Oracle在最下層的階段，的確是選擇有沒有更快更高效的尋找owner所有值的方法。進行全表掃描必然是消耗資源最大，但也是最能保證全部覆蓋的方法。進行索引掃描的時候，Oracle需要保證進行葉節點掃描後，可以獲取到所有owner值（包括空值）。注意，空值null是不會進入索引樹的。所以，Oracle在這裡因為認為獲取到的葉子節點上的owner值不全，才放棄了這種方式。

優化嘗試二

那麼，如何才能讓Oracle相信該列全部值都在索引葉子節點上呢？答案就是對列屬性進行修改。之前筆者的blog：《資料列not null對索引影響一例》（http://space.itpub.net/17203031/viewspace-682684）中，已經講述過這種方法。

簡單的說，not null雖然是一個約束，但是從側面上是該列的一個屬性，會影響到優化器工作的。

SQL> alter table TT modify OWNER not null;

Table altered

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

此時，進行執行計劃獲取。

SQL> explain plan for select distinct owner from tt;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 3631459427

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 27 | 162 | 36 (23)| 00:0

| 1 | HASH UNIQUE | | 27 | 162 | 36 (23)| 00:0

| 2 | INDEX FAST FULL SCAN| IDX_TT_OWNER | 51361 | 300K| 29 (4)| 00:0

--------------------------------------------------------------------------------

9 rows selected

終於，我們看到了進行索引掃描的執行計劃。相應的執行CPU成本也下降到原來的20%。執行方法“INDEX FAST FULL SCAN”表示的就是隻進行索引葉子節點的掃描，而不進行資料塊的掃描。

這個案例，我們獲得如下的經驗：

ü 對一些關鍵資料表（海量），無論是多小的一個操作，都存在效能問題的隱患。要在開發設計階段就主動進行識別，之後密切關注。及時修正設計和開發方式。不要等待最後效能測試乃至投產之後才開始著手，此時大局已定，入手點較少；

ü 索引路徑不是隻在where條件後出現才會出現在執行計劃中。CBO優化器智慧程度很高，在統計量正常的情況下，一般是可以獲取到正確的路徑和方法的；

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/17203031/viewspace-692915/，如需轉載，請註明出處，否則將追究法律責任。

藉助索引+非空優化distinct操作一例

相關文章