oracle子查询提高效率,sql优化案例：oracle子查询的or 转为 union all以提升查询效率...

select count(*) as y0_

from CMS_CONTRACT this_

left outer join CMS_SYS_USERS applier1_ on this_.APPLY_USER_ID = applier1_.id

where

(

(

this_.COST_TYPE in ('Income', 'Free', 'PayAndIncome', 'Financing') and

applier1_.BUSINESS_GROUP_ID = 0

)

or

this_.id in (select column_value from table(get_underling_contract_ids(249652)))

)

and this_.NUM_STATE in ('GENERATED');

实验区：

select sum(y0_) as y0_ from (

select count(*)  as y0_

from CMS_CONTRACT this_

left outer join CMS_SYS_USERS applier1_ on this_.APPLY_USER_ID = applier1_.id

where

(

this_.COST_TYPE in ('Income', 'Free', 'PayAndIncome', 'Financing') and

applier1_.BUSINESS_GROUP_ID = 0

)

and this_.NUM_STATE in ('GENERATED')

union all

select count(*)  as y0_

from CMS_CONTRACT this_

left outer join CMS_SYS_USERS applier1_ on this_.APPLY_USER_ID = applier1_.id

where

(

this_.id in (select column_value from table(get_underling_contract_ids(249652)))

)

and this_.NUM_STATE in ('GENERATED') and this_.COST_TYPE not in ('Income', 'Free', 'PayAndIncome', 'Financing') and

applier1_.BUSINESS_GROUP_ID = 0 );