select round(decode(sum(te1+te2),0,0,sum(te1)/sum(te1+te2)*100),2),ny from sg t
where data in
(select distinct t1.data from kj t1,hl t2
where t1.data =t2.data and qy='dba' and jb='l' and t1.hsj='qj' or t1.fdf='ju')
and ny between 200902 and 201106 group by ny 上面这个在oracle数据库中,花费时间大约为5秒,用户实在受不了,其中,in 中传入的data数量有70左右,
我试过用一些表连接,时间也没有提高。
请问各位大神如何优化这个sQL语句,十分感激!!!
where data in
(select distinct t1.data from kj t1,hl t2
where t1.data =t2.data and qy='dba' and jb='l' and t1.hsj='qj' or t1.fdf='ju')
and ny between 200902 and 201106 group by ny 上面这个在oracle数据库中,花费时间大约为5秒,用户实在受不了,其中,in 中传入的data数量有70左右,
我试过用一些表连接,时间也没有提高。
请问各位大神如何优化这个sQL语句,十分感激!!!
解决方案 »
- 两个数据库之间进行EXP/IMP的问题
- 上面的SQL语句,我想让括号里的 "2021,2023,2024,2028......"内容作为动态查询的条件,
- Procedure 中insert插入问题
- 哪位高手帮下小弟,何实现oracle每天自动备份数据!!
- 如何控制游标
- 单个表怎样实现多级删除?
- 为什么用用户登陆会出现:访问 PRODUCT_USER_PROFILE 时出现错误?
- sqlplus不能用了!!!
- 请问oracle flow是什么东西 干什么用的?谁有相关的介绍资料?
- 谁能提供 oracel 7.3.2 下载。急!!!!!!!!
- oracle 定时器
- 现在使用9i,希望通过闪回实现数据的恢复,请给出具体的方法和代码!谢谢
红色部分要不要加括号呀?感觉怪怪的
好像要被选择的表比子查询中表的结果个数少很多,exists的查询效率才会高于in啊!
不知是否正确?
select round(decode(sum(te1 + te2), 0, 0, sum(te1) / sum(te1 + te2) * 100),
2),
ny
from sg t
where data not in (select data
from sg
minus
select distinct t1.data
from kj t1, hl t2
where t1.data = t2.data
and qy = 'dba'
and jb = 'l'
and t1.hsj = 'qj'
or t1.fdf = 'ju')
and ny between 200902 and 201106
group by ny
2),
ny
from sg t
where data not in (select data
from sg
where ny between 200902 and 201106
minus
select distinct t1.data
from kj t1, hl t2
where t1.data = t2.data
and qy = 'dba'
and jb = 'l'
and t1.hsj = 'qj'
or t1.fdf = 'ju')
and ny between 200902 and 201106
group by ny可以将 where 条件执行的优先级很高, 可以将where 条件放到not in的 子查询里面来提高效率
select round(decode(sum(te1+te2),0,0,sum(te1)/sum(te1+te2)*100),2),ny from sg t
where ny between 200902 and 201106
and data in
(select distinct t1.data from kj t1,hl t2
where t1.data =t2.data and qy='dba' and jb='l' and t1.hsj='qj' or t1.fdf='ju')
group by ny
此执行计划是PL/SQL生成的,我看不明白啊,求解释!
或者哪位大侠有完整的关于执行计划的介绍!
给你举个例子SQL> conn scott/tiger
已连接。
SQL> show user;
USER 为 "SCOTT"
SQL> explain plan for
2 select * from emp;已解释。SQL> select * from table(dbms_xplan.display());PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
--------------------
Plan hash value: 3956160932--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 444 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 12 | 444 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------已选择8行。SQL>
where data in
(select distinct t1.data from kj t1,hl t2
where t1.data =t2.data and qy='dba' and jb='l' and t1.hsj='qj' or t1.fdf='ju')
and ny between 200902 and 201106 group by ny
1、红色标记:and和or并行用,只有and有效。这里你可能写出了一个逻辑错误,这回导致inlist列表很大。会影响性能
2、黄色 inlins 不用 distince排序。最后你要评估取出的数据是全部,还是部分,如果是少部分,是否有索引。