SELECT ABALPH,SUM(SDSOQS/100) AS SDSOQS,SUM(SDAEXP/100) AS SDAEXP,SUM(SDECST/100) AS SDECST
FROM
((SELECT SDSHAN,SDSOQS,SDAEXP,SDECST
FROM PRODDTA.F4211
WHERE SDDCTO IN ('SO','SX','DZ','SL','SF','DF','CB','CO','CA','CQ','CV','CU')
AND SDNXTR >'560' AND SDNXTR <'999'
AND SDKCOO NOT IN ('02500','05100','05200','06000','06100','08000') AND (1=1))
UNION ALL (SELECT SDSHAN,SDSOQS,SDAEXP,SDECST
FROM PRODDTA.F42119
WHERE SDDCTO IN ('SO','SX','DZ','SL','SF','DF','CB','CO','CA','CQ','CV','CU')
AND SDKCOO NOT IN ('02500','05100','05200','06000','06100','08000')
AND SDLTTR < '980' AND (1=1))
)F4211
LEFT JOIN PRODDTA.F0101 ON F4211.SDSHAN=F0101.ABAN8
GROUP BY ABALPH
ORDER BY SDSOQS DESC-------------------------
执行这段SQL要100+秒!单从这段语句看 我怎么样优化提高效率呢????????请求高人!!
FROM
((SELECT SDSHAN,SDSOQS,SDAEXP,SDECST
FROM PRODDTA.F4211
WHERE SDDCTO IN ('SO','SX','DZ','SL','SF','DF','CB','CO','CA','CQ','CV','CU')
AND SDNXTR >'560' AND SDNXTR <'999'
AND SDKCOO NOT IN ('02500','05100','05200','06000','06100','08000') AND (1=1))
UNION ALL (SELECT SDSHAN,SDSOQS,SDAEXP,SDECST
FROM PRODDTA.F42119
WHERE SDDCTO IN ('SO','SX','DZ','SL','SF','DF','CB','CO','CA','CQ','CV','CU')
AND SDKCOO NOT IN ('02500','05100','05200','06000','06100','08000')
AND SDLTTR < '980' AND (1=1))
)F4211
LEFT JOIN PRODDTA.F0101 ON F4211.SDSHAN=F0101.ABAN8
GROUP BY ABALPH
ORDER BY SDSOQS DESC-------------------------
执行这段SQL要100+秒!单从这段语句看 我怎么样优化提高效率呢????????请求高人!!
解决方案 »
- 怎样获得Oracle中的具体数据类型,如varchar char blob...
- MS SQL 的存储过程 改写 ORACLE 的
- 代码解释
- 怎么样Select 到一个数据列表,然后在下面的Select 语句中使用In (上次检索到的记录列表)
- 关于SQL语句报错的问题!高手请来帮忙!解决马上给分
- pfile和spfile大小不一样??
- 看书看到数据库进程如database writer,LOG writer等,感觉晕晕的~~这个地方是了解就可以,还是属于可人工干预操控部分
- 请问再oracle中怎样实现某个字段的自增长
- create table 的问题??急~~~~~~~~~~~~~~~~
- 新手上路,求一个触发器的写法
- rac中几个节点时间不同步了,直接修改时间使他们一致,对数据库会不会造成影响啊
- 怎么增加SGA_MAX_SIZE?
最好能够用关联查询代替in
where条件后面的索引,考虑下.
看执行计划.
如果那个SQL语句慢,在优化那条SQL
用EXISTS替代IN、用NOT EXISTS替代NOT IN.
使用EXISTS(或NOT EXISTS)通常将提高查询的效率
in和not in的执行效率很慢,可以用exsit 和notexsit来代替,这是个常识!
如果要查看更详细的,就要用sql语句优化工具了,这个一句两句说不清,请楼主在网上找工具
大部分工具在生成执行计划后,还要学会分析执行计划,这样才可以知道消耗资源的地方,
才能找到根结!
但是楼主用到的in 和 not in 后面都是 罗列出来的字符串
如果这些 字符串能用 SQL 语句实现,比如
select *** from *** where ***
就可以把 in 后者 not in 前面的字段名用在查询语句里
============================================================================
WHERE SDDCTO IN ('SO','SX','DZ','SL','SF','DF','CB','CO','CA','CQ','CV','CU')
============================================================================
这个可以改成
where exists (select count(*) from *** where ***)
就是这样来实现啊
应该是例 select a.* from tableA a where a.id in(select b.id from tableB b);
--改用exists为
select a.* from talbeA a where exists(select 1 from tableB b where a.id=b.id);
select * from a where a.col not in (1, 2, 3, 4, 5)用不到索引