Help Center> Data Warehouse Service> Troubleshooting> Database Performance and Resources> SQL Statements Contain the in Constant and No Result Is Returned After SQL Statement Execution
View PDF

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 case, you need to use the join constant 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 case, you need to use the join constant instead of the in constant for better performance.

Handling Procedure

  1. The default value of qrw_inlist2join_optmode is cost_base. You can change the constant from in to join. 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;