GBASE分享：SQL改写

原SQL：

select count(‘x’) rs

from ab01 a, ac02 b

where a.aab001 = b.aab001

and b.aae140 = ‘110’

and a.aab301 = ‘1001’

and exists(select ‘x’ from ic10 c where b.aac001 = c.aac001 and c.aae140=’110′)

and exists(select ‘x’ from ac01 d where b.aac001 = d.aac001 and d.aac004 = ‘2’ ) ;

修改后：

select count(*) from (select b.aac001 as aac001 from ab01 a, ac02 b

where a.aab001 = b.aab001

and b.aae140 = ‘110’

and a.aab301 = ‘1001’

and exists(select ‘x’ from ic10 c where b.aac001 = c.aac001 and c.aae140=’110′)) tt

join ac01_1 d  where tt.aac001 = d.aac001 and d.aac004 = ‘2’;

rsSQL1：

select

distinct cert.emp_id

from

cm_log cl

inner join

(

select

emp.id as emp_id,

emp_cert.id as cert_id

from

employee emp

left join

emp_certificate emp_cert

on emp.id = emp_cert.emp_id

where

emp.is_deleted=0

) cert

on (

cl.ref_table=’Employee’

and cl.ref_oid= cert.emp_id

)

or (

cl.ref_table=’EmpCertificate’

and cl.ref_oid= cert.cert_id

)

where

cl.last_upd_date >=’2013-11-07 15:03:00′

and cl.last_upd_date<=’2013-11-08>

基本思想是如果cm_log的ref_table是EmpCertificate就关联emp_certificate表，如果ref_table是Employee就关联employee表，我们完全可以拆成两部分，并用union连接起来，注意这里用union，而不用union all是因为原语句有“distinct”来得到唯一的记录，而union恰好具备了这种功能。如果原语句中没有distinct不需要去重，我们就可以直接使用union all了，因为使用union需要去重的动作，会影响SQL性能。

优化过的语句如下

select

emp.id

from

cm_log cl

inner join

employee emp

on cl.ref_table = ‘Employee’

and cl.ref_oid = emp.id

where

cl.last_upd_date >=’2013-11-07 15:03:00′

and cl.last_upd_date<=’2013-11-08>

and emp.is_deleted = 0

union

select

emp.id

from

cm_log cl

inner join

emp_certificate ec

on cl.ref_table = ‘EmpCertificate’

and cl.ref_oid = ec.id

inner join

employee emp

on emp.id = ec.emp_id

where

cl.last_upd_date >=’2013-11-07 15:03:00′

and cl.last_upd_date<=’2013-11-08>

and emp.is_deleted = 0;