Case: Rewriting SQL Statements and Deleting in-clause

Symptom

in-clause/any-clause is a common SQL statement constraint. Sometimes, the clause following in or any is a constant. For example:

1

select count(1) from calc_empfyc_c1_result_tmp_t1 where ls_pid_cusr1 in ('20120405', '20130405');

Or

1

select count(1) from calc_empfyc_c1_result_tmp_t1 where ls_pid_cusr1 in any('20120405', '20130405');

Sometimes, the in or any clause is used as follows:

1

SELECT ls_pid_cusr1,COALESCE(max(round((current_date-bthdate)/365)),0)FROM calc_empfyc_c1_result_tmp_t1 t1,p10_md_tmp_t2 t2WHERE t1.ls_pid_cusr1 = any(values(id),(id15))GROUP BY ls_pid_cusr1;

id and id15 are columns in p10_md_tmp_t2, and t1.ls_pid_cusr1 = any(values(id),(id15)) is equivalent to t1.ls_pid_cusr1 = id or t1.ls_pid_cusr1 = id15.

Therefore, join-condition is essentially an inequality, and nestloop must be used for this unequal join operation. The corresponding execution plan is as follows.

Optimization

The test result shows that both result sets are too large. As a result, nestloop is time-consuming with more than one hour to return results. Therefore, the key to performance optimization is to eliminate nestloop, using more efficient hash join. From the perspective of semantic equivalence, the SQL statements can be written as follows:

1

selectls_pid_cusr1,COALESCE(max(round(ym/365)),0)from(         (                   SELECT                             ls_pid_cusr1,(current_date-bthdate) as ym                   FROM calc_empfyc_c1_result_tmp_t1 t1,p10_md_tmp_t2 t2                   WHERE t1.ls_pid_cusr1 = t2.id and t1.ls_pid_cusr1 != t2.id15         )         union all         (                   SELECT                             ls_pid_cusr1,(current_date-bthdate) as ym                   FROM calc_empfyc_c1_result_tmp_t1 t1,p10_md_tmp_t2 t2                   WHERE t1.ls_pid_cusr1 = id15         ))GROUP BY ls_pid_cusr1;

The optimized SQL query consists of two equivalent join subqueries, and each subquery can be used for hash join in this scenario. The optimized execution plan is as follows.

Before the optimization, no result is returned for more than 1 hour. After the optimization, the result is returned within 7s.