如何優化這個sql？

上個星期發現開發人員寫的一條sql，語句如下：

0。
SELECT DISTINCT a.dept_code, a.dept_name, a.input_code
FROM dept_dict a, pat_visit b
WHERE a.dept_code = b.dept_admission_to

如何優化這個sql？

上個星期發現開發人員寫的一條sql，語句如下：

0。
SELECT DISTINCT a.dept_code, a.dept_name, a.input_code
FROM dept_dict a, pat_visit b
WHERE a.dept_code = b.dept_admission_to

這樣寫明視訊記憶體在效能問題，使用DISTINCT，a.dept_code是主鍵，而b表很大
（當前50M，索引大小3M使用壓縮）。

我修改如下幾種形式：

1。SELECT a.dept_code, a.dept_name, a.input_code
FROM dept_dict a
WHERE a.dept_code IN (SELECT DISTINCT dept_admission_to
FROM pat_visit
WHERE dept_admission_to IS NOT NULL)

2。SELECT a.dept_code, a.dept_name, a.input_code
FROM dept_dict a
WHERE EXISTS (
SELECT distinct dept_admission_to
FROM pat_visit
WHERE dept_admission_to = a.dept_code
AND dept_admission_to IS NOT NULL)

3、SELECT a.dept_code, a.dept_name, a.input_code
FROM dept_dict a
WHERE EXISTS (
SELECT distinct dept_admission_to
FROM pat_visit
WHERE dept_admission_to = a.dept_code
AND dept_admission_to IS NOT NULL
AND ROWNUM = 1)

在表b不是太大的時候，1，2執行效率較好（包括開發人員寫的sql），但是當b表很大的時候，
1，2的執行效率會越來越好。

執行計劃：

0。
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 138K| 4462K| | 1316 (2)| 00:00:16 |
| 1 | HASH UNIQUE | | 138K| 4462K| 10M| 1316 (2)| 00:00:16 |
|* 2 | HASH JOIN | | 138K| 4462K| | 69 (6)| 00:00:01 |
| 3 | TABLE ACCESS FULL | DEPT_DICT | 357 | 9639 | | 8 (0)| 00:00:01 |
| 4 | INDEX FAST FULL SCAN| I_PAT_VISIT_DEPT_ADMISSION_TO | 138K| 811K| | 59 (4)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------

1。
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 33 | 69 (6)| 00:00:01 |
|* 1 | HASH JOIN SEMI | | 1 | 33 | 69 (6)| 00:00:01 |
| 2 | TABLE ACCESS FULL | DEPT_DICT | 357 | 9639 | 8 (0)| 00:00:01 |
|* 3 | INDEX FAST FULL SCAN| I_PAT_VISIT_DEPT_ADMISSION_TO | 138K| 811K| 59 (4)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

2。
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 33 | 69 (6)| 00:00:01 |
|* 1 | HASH JOIN SEMI | | 1 | 33 | 69 (6)| 00:00:01 |
| 2 | TABLE ACCESS FULL | DEPT_DICT | 357 | 9639 | 8 (0)| 00:00:01 |
|* 3 | INDEX FAST FULL SCAN| I_PAT_VISIT_DEPT_ADMISSION_TO | 138K| 811K| 59 (4)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

3。
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 27 | 187 (0)| 00:00:03 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| DEPT_DICT | 357 | 9639 | 8 (0)| 00:00:01 |
|* 3 | COUNT STOPKEY | | | | | |
|* 4 | INDEX RANGE SCAN| I_PAT_VISIT_DEPT_ADMISSION_TO | 2 | 12 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

在B表不是太大的情況下，0，1，2 的執行計劃都是276邏輯讀，但是0計劃存在一個 HASH UNIQUE ，消耗大量的臨時空間（10M）。
3計劃的邏輯讀是744。

現在將表B加大到4倍。
create table pat_visit1 as select * from pat_visit ;
insert into pat_visit1 select * from pat_visit1 ;
insert into pat_visit1 select * from pat_visit1 ;

CREATE INDEX I_PAT_VISIT1_DEPT_ADMISSION_TO ON PAT_VISIT1(DEPT_ADMISSION_TO) COMPRESS 1;

分析表後在重複測試，發現執行計劃如下：
0。
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 63725 | 2053K| | 4476 (4)| 00:00:54 |
| 1 | HASH UNIQUE | | 63725 | 2053K| 43M| 4476 (4)| 00:00:54 |
|* 2 | HASH JOIN | | 1066K| 33M| | 662 (3)| 00:00:08 |
| 3 | TABLE ACCESS FULL | DEPT_DICT | 357 | 9639 | | 8 (0)| 00:00:01 |
| 4 | INDEX FAST FULL SCAN| I_PAT_VISIT1_DEPT_ADMISSION_TO | 1066K| 6246K| | 641 (1)| 00:00:08 |
-----------------------------------------------------------------------------------------------------------------

1。
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 37 | 1221 | 365 (0)| 00:00:05 |
| 1 | NESTED LOOPS SEMI | | 37 | 1221 | 365 (0)| 00:00:05 |
| 2 | TABLE ACCESS FULL| DEPT_DICT | 357 | 9639 | 8 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | I_PAT_VISIT1_DEPT_ADMISSION_TO | 110K| 647K| 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

2.
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 37 | 1221 | 365 (0)| 00:00:05 |
| 1 | NESTED LOOPS SEMI | | 37 | 1221 | 365 (0)| 00:00:05 |
| 2 | TABLE ACCESS FULL| DEPT_DICT | 357 | 9639 | 8 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | I_PAT_VISIT1_DEPT_ADMISSION_TO | 110K| 647K| 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

3.
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 27 | 901 (0)| 00:00:11 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| DEPT_DICT | 357 | 9639 | 8 (0)| 00:00:01 |
|* 3 | COUNT STOPKEY | | | | | |
|* 4 | INDEX RANGE SCAN| I_PAT_VISIT1_DEPT_ADMISSION_TO | 10661 | 63966 | 5 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

邏輯讀分別是：2943，853，853，1106。

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