ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 如何优化这个sql?

如何优化这个sql?

原创 Linux操作系统 作者:lfree 时间:2007-04-23 00:00:00 0 删除 编辑

上个星期发现开发人员写的一条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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2485
  • 访问量
    6291571