解决方案 »
- Oracle字典缓存命中率问题
- 跨平台数据库交互问题,开发接口问题
- 怎么样封装一个已写好的 SQL 语句来统计这个sql 的条数?
- 请教大师:Oracle存储过程的写法
- 请高手帮看看,优化一条简单查询的sql语句
- 触发器的问题 高手请进
- 有两个表T1,T2, T1的字段(T1_K1,T1_K2,T1_K3,T1_F1,T1_F2….)T2的字段(T2_K1,T2_K2,T2_K3,T2_F1,T2_F2….)K1,K2,K3都是KEY,两个表用这两
- oracle与oracle plus是一个东西吗?有什么区别呀。
- 请问如何获得表的字段名?
- 如何实现RNU A.SQL 后把它的结果集存入一个文本文件中
- 我想要在存储过程中接收一个集合的数据怎么整?
- 值更新问题
可以试着将语句中的in改为exists的写法,对于大表的查询,exists要快一点
另外查询中子查询用得较多,可以试着改为表连接的方式,查询会快一点
个人建议你先收集一下统计信息,如果还不行.
你对比一下 和使用标红部分的查询结果 的执行计划有什么不同,用hint或sql profile调整就行了.
这些都是不改语句的情况下,如果可以改语句,基本上也就是改是exists或join的形式,当然这要测试才知道那个好.
and q.quotasource not like '%政府专项%'
感觉这两个这样写会走全表扫描,有索引也不起作用呀。
参考http://www.cnblogs.com/fqw1987815/archive/2010/08/18/1802657.html
2、where aa.gidl_doc_numb in (select distinct q.quotanotitle || q.quotanonum
from quota_quotadata q
where q.quotasource like '%年初预算%'
and q.quotasource not like '%政府专项%'
and q.year = 2014
and q.quotastatus = 7) 这里in操作不用distinct消耗
FROM (SELECT SUM(t1.debit_source - nvl(t1.credit_source, 0)) / 10000 金额,
t1.gidl_doc_numb
FROM Ga_t_Acc_Voucher_Static t1
WHERE t1.acst_code = 1001
AND t1.bdgt_subj IN (SELECT t.subjectcode
FROM busmgr_funnew t
WHERE t.subjecttype LIKE '一般预算支出科目'
AND t.year = 2014)
AND t1.yearmonth BETWEEN '201401' AND '201407'
AND t1.sub_voucher_id IN
(SELECT asv.sub_voucher_id
FROM ga_t_acc_sub_voucher asv
WHERE asv.acst_code =
(SELECT t.acst_id
FROM GA_T_ACCOUNT_SET t
WHERE t.acst_name = '预算内')
AND asv.voucher_id IN
(SELECT amv.voucher_id
FROM ga_t_acc_main_voucher amv
WHERE amv.status = 3))
GROUP BY t1.gidl_doc_numb) aa
WHERE aa.gidl_doc_numb IN (SELECT /* no_merge */
DISTINCT q.quotanotitle || q.quotanonum
FROM quota_quotadata q
WHERE q.quotasource LIKE '%年初预算%'
AND q.quotasource NOT LIKE '%政府专项%'
AND q.year = 2014
AND q.quotastatus = 7)
SELECT aa.*
FROM (SELECT SUM(t1.debit_source - nvl(t1.credit_source, 0)) / 10000 金额,
t1.gidl_doc_numb
FROM Ga_t_Acc_Voucher_Static t1
WHERE t1.acst_code = 1001
AND t1.bdgt_subj IN (SELECT t.subjectcode
FROM busmgr_funnew t
WHERE t.subjecttype LIKE '一般预算支出科目'
AND t.year = 2014)
AND t1.yearmonth BETWEEN '201401' AND '201407'
AND t1.sub_voucher_id IN
(SELECT asv.sub_voucher_id
FROM ga_t_acc_sub_voucher asv
WHERE asv.acst_code =
(SELECT t.acst_id
FROM GA_T_ACCOUNT_SET t
WHERE t.acst_name = '预算内')
AND asv.voucher_id IN
(SELECT amv.voucher_id
FROM ga_t_acc_main_voucher amv
WHERE amv.status = 3))
GROUP BY t1.gidl_doc_numb) aa
WHERE aa.gidl_doc_numb IN (SELECT /*+ no_merge */
DISTINCT q.quotanotitle || q.quotanonum
FROM quota_quotadata q
WHERE q.quotasource LIKE '%年初预算%'
AND q.quotasource NOT LIKE '%政府专项%'
AND q.year = 2014
AND q.quotastatus = 7)