select *
from PDC_OS_JOBpdcosjob0_,
BYR_NAMEbyrname1_,
CUS_NAME_ADDR cusnameadd2_,
PDC_MST pdcmst3_
where pdcosjob0_.PD_NO = pdcmst3_.PD_NO
and byrname1_.BYR_CTRY = pdcmst3_.BYR_CTRY
and byrname1_.BYR_CODE = pdcmst3_.BYR_CODE
and byrname1_.NAME_TYPE = 'M'
and cusnameadd2_.CUS_NO = pdcmst3_.PCY_NO
and cusnameadd2_.REC_TYPE = 'C'
and cusnameadd2_.NAME_TYPE = 'N'
and (pdcosjob0_.STS in ('N', 'I'))
and (pdcosjob0_.JOB_TYPE in ('PDOB'))
94行记录，28.251秒
select *
from PDC_OS_JOBpdcosjob0_,
BYR_NAMEbyrname1_,
CUS_NAME_ADDR cusnameadd2_,
PDC_MST pdcmst3_
where pdcosjob0_.PD_NO = pdcmst3_.PD_NO
and byrname1_.BYR_CTRY = pdcmst3_.BYR_CTRY
and byrname1_.BYR_CODE = pdcmst3_.BYR_CODE
and byrname1_.NAME_TYPE = 'M'
and cusnameadd2_.CUS_NO = pdcmst3_.PCY_NO
and cusnameadd2_.REC_TYPE = 'C'
------and cusnameadd2_.NAME_TYPE = 'N'
and (pdcosjob0_.STS in ('N', 'I'))
and (pdcosjob0_.JOB_TYPE in ('PDOB'))
去掉一个条件，519行，3.5秒，
cus_name_addr的name_type纪录情况
select t.name_type, count(*) from cus_name_addr t
group by t.name_type
1J386
2N4360
3T6
4
12999
可以看到大部分是空值。
建立索引
cusnameadd2_.NAME_TYPE建立bitmap索引， 94行记录，28.3秒
cusnameadd2_.NAME_TYPE建立normal(不是唯一)索引， 94行记录，14.5秒
请问大家，如何再次优化这个sql?
thanks.
|
