请教sql优化的写法

显示全部楼层 · 2007-7-23 01:03:36

table table_a
-----------------------------------------------
idc_id dms_idvlu
0701 本年 50
0702 本年60
0703 本年30
0701 本年累计50
0702 本年累计110
-----------------------------------------------
查处所有有本年且无本年累计的记录
----预期结果是---------------table_a------
idc_id dms_idvlu
0703 本年30
-----------------------------------------------
我写的是
select * from table_a where dms_id = '本年' and idc_idnot in(select t1.idc_id from
(select * from table_a where dms_id = '本年') t1 inner join
(select * from table_a where dms_id = '本年累计') t2 on t1.idc_id = t2.idc_id)
------------------------------------------------
有更优化的写法吗？？请教一下！！！

千问 · 2007-7-23 01:03:36

这样写是不是也可以,你的语句中用了not in 好像不如exists好，你试一下
select * from table_a a where dms_id='本年'
and not exists (select * from table_a b where dms_id='本年累计' and a.idc_id=b.idc_id)
----------------------------------------
1
0703
本年
30

千问 · 2007-7-23 01:03:36

select *
from table_a
where idc_id in
(
select idc_id
from table_a
group by idc_id
having decode(dms_id,'本年',0,'本年累计',1)=0
)

千问 · 2007-7-23 01:03:36

select t.* from table_a t
WHERE t.dms_id = '本年'
AND t.ID NOT IN (SELECT a.id

FROM table_a a

WHERE a.dms_id = '本年累计'

and t.id = a.id )
[ 本帖最后由一觉于 2008-4-8 16:00 编辑 ]

千问 · 2007-7-23 01:03:36

写错了，删除
[ 本帖最后由 hotiice 于 2008-4-8 16:23 编辑 ]

千问 · 2007-7-23 01:03:36

我在执行计划中分析了一下两个语句：
select * from table_a a where dms_id='本年'
and not exists (select * from table_a b where dms_id='本年累计' and a.idc_id=b.idc_id)
执行计划：
Execution Plan--时行三次
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE
10 FILTER
21 TABLE ACCESS (FULL) OF 'TABLE_A'
31 TABLE ACCESS (FULL) OF 'TABLE_A'

Statistics
----------------------------------------------------------

0recursive calls

0db block gets
25consistent gets

0physical reads

0redo size
489bytes sent via SQL*Net to client
503bytes received via SQL*Net from client

2SQL*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)

1rows processed
select * from table_a where dms_id = '本年' and idc_idnot in(select t1.idc_id from
(select * from table_a where dms_id = '本年') t1 inner join
(select * from table_a where dms_id = '本年累计') t2 on t1.idc_id = t2.idc_id)
－执行计划
Execution Plan　　－－进行六次
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE
10 FILTER
21 TABLE ACCESS (FULL) OF 'TABLE_A'
31 MERGE JOIN
43 SORT (JOIN)
54 TABLE ACCESS (FULL) OF 'TABLE_A'
63 SORT (JOIN)
76 TABLE ACCESS (FULL) OF 'TABLE_A'

Statistics
----------------------------------------------------------

0recursive calls

0db block gets
50consistent gets

0physical reads

0redo size
489bytes sent via SQL*Net to client
503bytes received via SQL*Net from client

2SQL*Net roundtrips to/from client

6sorts (memory)

0sorts (disk)

1rows processed
从上面可以看出应该是第一句性能要好一些，consistent gets这个参数一个是25,一个是50.

千问 · 2007-7-23 01:03:36

原帖由 hotiice 于 2008-4-8 15:59 发表
select a.idc_id,... from table_a a inner join table_a b
on( a.id=b.id and a.dms_id='本年' and b.dms_ida.dms_id)
这个是错的，不能得到预期结果。
得到的是有本年且有本年累计的所有本年结果 like
-----------table_a----------------------
idc_iddms_idvlu
0701本年 50
0702本年 60

千问 · 2007-7-23 01:03:36

原帖由阿日于 2008-4-8 15:56 发表
这样写是不是也可以,你的语句中用了not in 好像不如exists好，你试一下
select * from table_a a where dms_id='本年'
and not exists (select * from table_a b where dms_id='本年累计' and a.idc_id=b.idc_id)
----------------------------------------
10703本年30

这个结果也是错的，查出来的所有有本年的记录 like
-----------------table_a -------------
idc_iddms_idvlu
0701本年
50
0702 本年 60
0703本年
30

千问 · 2007-7-23 01:03:36

原帖由 mihawk 于 2008-4-8 15:58 发表
select *
from table_a
where idc_id in
(
select idc_id
from table_a
group by idc_id
having decode(dms_id,'本年',0,'本年累计',1)=0
)

这个。。。。
not a group by expression....
晕

千问 · 2007-7-23 01:03:36

insert into z1 select 1,'bn',1 from dual union
select2,'bn',2 from dual union
select2,'bnlj',3 from dual union
select3,'bnlj',4 from dual;
SQL> create table z1(v1 number(4),v2 varchar(10),v3 number(10));
表已创建。
SQL> insert into z1 select 1,'bn',1 from dual union
2select2,'bn',2 from dual union
3select2,'bnlj',3 from dual union
4select3,'bnlj',4 from dual;
已创建4行。
SQL> select * from z1;
V1 V2
V3
---------- ---------- ----------
1 bn
1
2 bn
2
2 bnlj
3
3 bnlj
4
SQL> select * from z1 a where a.v2='bn' and a.v1 not in(select v1 from z1 b where b.v2='bnlj');

V1 V2
V3
---------- ---------- ----------
1 bn
1