Select /*+use_hash(t7 t)*/
Distinct t.Taxregcode
From Tb_Ass_Improvedeclarebooklet t, Tb_Lvy_Improvedeclaredata T7, Tc_Pub_Taxtype o
Where t.Taxregcode = T7.Taxregcode
And o.Taxtypecode = t.Taxtypecode
And o.Fundtaxflag = 1
And o.Choiceflag = 1
And o.Taxtypecode Not In ('82', '96')
And t.Statmonth = '200812'
And T7.Operatortime > (To_Date('200812', 'yyyymm') - 1)
And T7.Operatortime < Add_Months(To_Date('200812', 'yyyymm'), 1)
And t.Orgdeptcode Like Substr('26101020000', 1, 7) || '%'
And T7.Negativeflag <> 9其中 t 和 t7表都又400万条左右的数据,o表几十条数据
查询条件基本上都是索引
业务需要去掉重复的Taxregcode,所以用到了Distinct
查询速度很慢,希望高手能提出优化意见,谢谢!
Distinct t.Taxregcode
From Tb_Ass_Improvedeclarebooklet t, Tb_Lvy_Improvedeclaredata T7, Tc_Pub_Taxtype o
Where t.Taxregcode = T7.Taxregcode
And o.Taxtypecode = t.Taxtypecode
And o.Fundtaxflag = 1
And o.Choiceflag = 1
And o.Taxtypecode Not In ('82', '96')
And t.Statmonth = '200812'
And T7.Operatortime > (To_Date('200812', 'yyyymm') - 1)
And T7.Operatortime < Add_Months(To_Date('200812', 'yyyymm'), 1)
And t.Orgdeptcode Like Substr('26101020000', 1, 7) || '%'
And T7.Negativeflag <> 9其中 t 和 t7表都又400万条左右的数据,o表几十条数据
查询条件基本上都是索引
业务需要去掉重复的Taxregcode,所以用到了Distinct
查询速度很慢,希望高手能提出优化意见,谢谢!
2、DISTINCT很影响速度,能否先不用distinct,把结果放到一个中间表中,从这个中间表用distinct取数
3、对表o的关联最好改为EXISTS
如Select t.Taxregcode
From Tb_Ass_Improvedeclarebooklet t JOIN Tb_Lvy_Improvedeclaredata T7
ON(t.Taxregcode = T7.Taxregcode )
Where t.Statmonth = '200812'
And t.Orgdeptcode Like Substr('26101020000', 1, 7) || '%'
And T7.Operatortime > (To_Date('200812', 'yyyymm') - 1)
And T7.Operatortime < Add_Months(To_Date('200812', 'yyyymm'), 1)
And T7.Negativeflag <> 9
AND EXISTS
(select 1 from Tc_Pub_Taxtype o
where o.Taxtypecode = t.Taxtypecode
And o.Fundtaxflag = 1
And o.Choiceflag = 1
And o.Taxtypecode Not In ('82', '96') )
analyze table Tb_Ass_Improvedeclarebooklet compute statistics;
你说的太深了,我的权限只有查询,没有其他权限了,去掉了/*+use_hash(t7 t)*/ 和
没去掉相差没影响啊,谢谢!能否将Distinct替换成别的方式啊?
2.如果 Tb_Ass_Improvedeclarebooklet 表中Taxregcode是唯一的,就用EXSITS代替DISTINCT
这种情况,不再现场,不了解各种信息,是很难解决的~~
From Tb_Ass_Improvedeclarebooklet t, Tc_Pub_Taxtype o
Where o.Taxtypecode = t.Taxtypecode
And o.Fundtaxflag = 1
And o.Choiceflag = 1
And o.Taxtypecode Not In ('82', '96')
And t.Statmonth = '200812'
And t.Orgdeptcode Like '2610102%'
and t.Taxregcode in
(select T7.Taxregcode
from Tb_Lvy_Improvedeclaredata T7
where T7.Operatortime > To_Date('20081130', 'yyyymmdd')
And T7.Operatortime < To_Date('20081230', 'yyyymm')
And T7.Negativeflag <> 9))
试一下