GBase 8a Mpp Cluster集群产品性能优化篇之减少嵌套-join连接

嵌套sql改为一个join连接

原sql：

SELECT sn_id

,flt_date

,flt_number

,seg_dept_no

,seg_dept_code

,flt_airlcode

,cert_no

,psr_brdno

,psr_sbyno

FROM table1

WHERE cert_no IN (

SELECT s1.cert_no

FROM table1 s1

,(

SELECT DISTINCT flt_date

,flt_airlcode

,flt_number

FROM table1

WHERE cert_no = ‘XXXXXXXXXXX’

AND flt_date BETWEEN ‘2011-01-01 00:00:00’

AND ‘2011-03-31 23:59:59’

) s2

WHERE s1.cert_no <> ‘XXXXXXXXXXX’

AND s1.flt_date = s2.flt_date

AND s1.flt_airlcode = s2.flt_airlcode

AND s1.flt_number = s2.flt_number

AND s1.flt_date BETWEEN ‘2011-01-01 00:00:00’

AND ‘2011-03-31 23:59:59’

GROUP BY cert_no

HAVING count(*) >= 2

)

AND flt_date BETWEEN ‘2011-01-01 00:00:00’

AND ‘2011-03-31 23:59:59’

改写后sql：

SELECT sn_id

,flt_date

,flt_number

,seg_dept_no

,seg_dept_code

,flt_airlcode

,cert_no

,psr_brdno

,psr_sbyno

FROM table1

WHERE cert_no IN (

SELECT s1.cert_no

FROM table1 s1

,table1 s2

WHERE s2.cert_no = ‘XXXXXXXXXXX’

AND s2.flt_date BETWEEN ‘2011-01-01 00:00:00’

AND ‘2011-03-31 23:59:59’

AND s1.cert_no <> ‘XXXXXXXXXXX’

AND s1.flt_date = s2.flt_date

AND s1.flt_airlcode = s2.flt_airlcode

AND s1.flt_number = s2.flt_number

GROUP BY s1.cert_no

HAVING count(*) >= 2

)

AND flt_date BETWEEN ‘2011-01-01 00:00:00’

AND ‘2011-03-31 23:59:59’;