如何优化这个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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-83010/