SQL Statements Contain the in Constant and No Result Is Returned After SQL Statement Execution

Symptom

The in constant is one of the criteria of the SQL statement for filtering large tables. There are more than 2000 constants. The base table contains a large amount of data. No result is returned after the SQL statement is executed.

Possible Causes

The in condition still exists as a common filtering condition in the execution plan. In this scenario, the performance of the join operation is better than that of the in constant. You need to use the join operation instead of the in constant for better performance.

Cause Analysis

1. Print the statement execution plan.

   

2. The in condition still exists as a common filtering condition in the execution plan. In this scenario, the performance of the join operation is better than that of the in constant. You need to use the join operation instead of the in constant for better performance.

Handling Procedure

1. The default value of qrw_inlist2join_optmodeqrw_inlist2join_optmode is cost_base. You can change the in constant to a join operation. If the number of rows estimated by the optimizer is inaccurate, the value of the parameter may not be changed in some scenarios, resulting in poor performance.
2. To solve this problem, set qrw_inlist2join_optmode to rule_base.

   1	set qrw_inlist2join_optmode to rule_base;