在實際開發中，我們會遇到各種訪問資料表的需求。簡單、高效一直是我們編寫SQL語句的一個重要標準。如何用最少的系統開銷，實現功能需求是我們需要關注的一個重要方面。

一個朋友問筆者：判斷符合條件記錄是否存在？這樣的SQL語句如何書寫最好。筆者感覺很有意思，這裡將思考分析過程加以記錄，供有類似需要的朋友備查。

1、環境準備

實驗在Oracle 10gR2上進行。為了更凸顯出效果，我們構建一張20萬記錄的資料表作為實驗物件。

SQL> select * from v$version;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE 10.2.0.1.0 Production

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 – Production

--構建實驗資料表

SQL> create table t as select * from dba_objects where 1=0;

Table created

SQL> insert into t select * from dba_objects;

53346 rows inserted

（多次重複insert過程，篇幅原因省略……）

SQL> commit;

Commit complete

SQL> select count(*) from t;

COUNT(*)

----------

213384

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

PL/SQL procedure successfully completed

--注意：本篇中涉及的所有select操作，之前都進行buffer cache清理工作，用於保證條件相同；

SQL> alter system flush buffer_cache;

System altered

要求構建SQL為：判斷owner列是否存在取值為’SCOTT’的記錄，如果有則返回‘Y’，否則返回空。

下面幾種處理思路和場景，分別進行介紹。

2、無索引新增情況下，各種備選SQL分析

索引index是我們經常使用到的一種最佳化手段。但是，索引對應用系統最佳化而言，絕對不是萬靈藥。使用索引是需要付出時間和空間上的成本的，而能否取得預計的最佳化效果是需要評估的。所以，筆者認為，只有在明確收益大於支出的情況下，我們才會主動使用索引。

首先，我們看一下不使用索引的情況下，幾條備選SQL的效能。

ü Count計數

Count計數應該是容易想到的一種直觀解決。如果能判斷出符合條件的記錄數量，是否存在不久顯而易見了嗎？

SQL> select count(*) from t where wner='SCOTT';

已用時間: 00: 00: 01.28

執行計劃

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

Plan hash value: 2966233522

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

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

| 0 | SELECT STATEMENT | | 1 | 7 | 655 (2)| 00:00:08 |

| 1 | SORT AGGREGATE | | 1 | 7 | | |

|* 2 | TABLE ACCESS FULL| T | 145 | 1015 | 655 (2)| 00:00:08 |

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

Predicate Information (identified by operation id):

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

2 - filter("OWNER"='SCOTT')

統計資訊

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

168 recursive calls

0 db block gets

2960 consistent gets

2946 physical reads

0 redo size

408 bytes sent via SQL*Net to client

400 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

4 sorts (memory)

0 sorts (disk)

1 rows processed

注意上面語句的幾個細節：

首先，由於無索引可用，所以在訪問資料表上，使用的是全表掃描（FTS）。應用owner=’scott’條件在FTS過程。

其次，將條件篩選過的結果進行aggregate操作，聚合成計數值count。這個過程要消耗pga乃至temp表空間的排序空間和cpu成本。

最後，在成本消耗上，合計執行計劃成本為655，主要體現在大規模IO讀取和sort排序操作上。

Count計數法從效果上，完全可以滿足需求要求。但是，給我們的感覺總有些“大炮打蚊子”之感。我只需要SQL告訴我們是否存在這樣的記錄，而不是告訴有多少條符合條件記錄。

那麼，我們從存在exists角度進行最佳化。

ü Exists語句最佳化

借用SQL中的exists語句，我們可以構造SQL如下。

SQL> select 'Y' from dual where exists(select * from t where wner='SCOTT');

已用時間: 00: 00: 01.76

執行計劃

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

Plan hash value: 1060005908

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

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

| 0 | SELECT STATEMENT | | 1 | | 8 (0)| 00:00:01 |

|* 1 | FILTER | | | | | |

| 2 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |

|* 3 | TABLE ACCESS FULL| T | 1 | 7 | 6 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - filter( EXISTS (SELECT /*+ */ 0 FROM "T" "T" WHERE

"OWNER"='SCOTT'))

3 - filter("OWNER"='SCOTT')

統計資訊

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

1 recursive calls

0 db block gets

685 consistent gets

688 physical reads

0 redo size

402 bytes sent via SQL*Net to client

400 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

注意，使用該種方法後，由於沒有索引，所以不能解決FTS的問題。但是，使用exists子句，可以有效減少發生物理邏輯讀塊的數量，減少recursive call的次數。更進一步，將原有的sort aggravate操作轉化為了filter操作，消除了sort area的使用。

從總成本上看，執行計劃中也從原有的655下降到8。應該說，應用exists在這種場景上，效果是比較好的。

ü Rownum語句最佳化

我們還可以從rownum的角度進行最佳化。對SQL語句來說，其實只需要訪問到一條符合條件的記錄，就可以返回結果了，不需要進行額外的任何操作。此時，我們可以藉助rownum來進行動作控制。

SQL> select 'Y' from t where wner='SCOTT' and rownum<2;

已用時間: 00: 00: 00.42

執行計劃

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

Plan hash value: 508354683

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

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

| 0 | SELECT STATEMENT | | 1 | 7 | 6 (0)| 00:00:01 |

|* 1 | COUNT STOPKEY | | | | | |

|* 2 | TABLE ACCESS FULL| T | 1 | 7 | 6 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - filter(ROWNUM<2)

2 - filter("OWNER"='SCOTT')

統計資訊

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

1 recursive calls

0 db block gets

685 consistent gets

688 physical reads

0 redo size

402 bytes sent via SQL*Net to client

400 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

使用rownum在SQL執行計劃中，對應的動作是count stopkey。該動作的含義是對返回的結果行數進行計數，數到指定的記錄數目就返回。這個和我們的希望動作相似。

從執行計劃和各種統計量來看，該語句是相對較好的一種。總成本下降到6左右。

3、有索引新增情況下，各種備選SQL分析

有索引情況下，我們的SQL語句如何呢？如果我們可以在owner列上新增索引，並且執行計劃中出現index，那麼在owner條件選取的問題上，索引葉子節點本身就可以提供有序的結構序列。

SQL> create index idx_t_owner on t(owner);

Index created

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

PL/SQL procedure successfully completed

ü Count語句

當有索引的情況下，Oracle可以根據排序好的葉子節點，直接定位到符合條件的記錄條目。

SQL> select count(*) from t where wner='SCOTT';

已用時間: 00: 00: 00.17

執行計劃

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

Plan hash value: 1232703844

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

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

| 0 | SELECT STATEMENT | | 1 | 7 | 3 (0)| 00:00:01

| 1 | SORT AGGREGATE | | 1 | 7 | |

|* 2 | INDEX RANGE SCAN| IDX_T_OWNER | 1 | 7 | 3 (0)| 00:00:01

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

Predicate Information (identified by operation id):

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

2 - access("OWNER"='SCOTT')

統計資訊

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

138 recursive calls

0 db block gets

20 consistent gets

12 physical reads

0 redo size

408 bytes sent via SQL*Net to client

400 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

3 sorts (memory)

0 sorts (disk)

1 rows processed

在有索引的情況下，count語句效率提升是顯著的。主要體現在成本下降（3）和IO訪問量減少上。

從執行計劃上，可以理解這種變化主要在於原來的FTS操作變化為Index Range Scan。減少了資料訪問塊讀取操作。

那麼，剩下的兩種方案效率如何呢？

ü Exists方案

SQL> select 'Y' from dual where exists(select * from t where wner='SCOTT');

已用時間: 00: 00: 00.05

執行計劃

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

Plan hash value: 1016071138

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

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

| 0 | SELECT STATEMENT | | 1 | | 5 (0)| 00:00:01

|* 1 | FILTER | | | | |

| 2 | FAST DUAL | | 1 | | 2 (0)| 00:00:01

|* 3 | INDEX RANGE SCAN| IDX_T_OWNER | 1 | 7 | 3 (0)| 00:00:01

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

Predicate Information (identified by operation id):

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

1 - filter( EXISTS (SELECT /*+ */ 0 FROM "T" "T" WHERE

"OWNER"='SCOTT'))

3 - access("OWNER"='SCOTT')

統計資訊

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

1 recursive calls

0 db block gets

3 consistent gets

3 physical reads

0 redo size

402 bytes sent via SQL*Net to client

400 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

在有索引的情況下，exists方案的IO量比count方案減少。成本有所上升。

ü Rownum方案

SQL> select 'Y' from t where wner='SCOTT' and rownum<2;

已用時間: 00: 00: 00.08

執行計劃

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

Plan hash value: 1415695426

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

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

| 0 | SELECT STATEMENT | | 1 | 7 | 3 (0)| 00:00:01

|* 1 | COUNT STOPKEY | | | | |

|* 2 | INDEX RANGE SCAN| IDX_T_OWNER | 1 | 7 | 3 (0)| 00:00:01

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

Predicate Information (identified by operation id):

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

1 - filter(ROWNUM<2)

2 - access("OWNER"='SCOTT')

統計資訊

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

1 recursive calls

0 db block gets

3 consistent gets

3 physical reads

0 redo size

402 bytes sent via SQL*Net to client

400 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

在使用rownum的情況下，IO量較count有所下降，但是成本cost估算相似。

4、結論

“魚有千種，網有萬條”，對Oracle最佳化方案的制定來說，同樣如此。本篇不僅僅是介紹了一個實現判斷記錄存在的SQL語句，更告訴我們：在不同的情況，包括業務、技術和最佳化環境，採用不同的語句，效果是有很大的差異的。作為最佳化人員的我們，要從業務特點出發，分析出資料表的主要訪問方式和關鍵服務用例，制定最合適的最佳化方案。

判斷符合條件記錄是否存在SQL若干

相關文章