Execution of SQL Statements with NOT IN and NOT EXISTS Is Slow Due to Nested Loops in Execution Plans

Symptom

The customer's SQL statement execution is slow, and the execution plan contains nested loops.

Possible Causes

• Nested loop is the main cause of slow statement execution.
• Hash joins can be used only for equi-join, and the nested loop conditions contain OR. Therefore, hash joins cannot be used to solve the problem.
• This occurs due to the NOT IN syntax. For details, see the description of NOT IN and NOT EXISTS on the Internet.

Cause Analysis

1. Run explain verbose to print the statement execution plan and check whether the SQL statement contains the NOT IN syntax.

   

2. Nested loop exists in the execution plan.

   

Handling Procedure

1. In most scenarios, the required result set can be obtained using NOT EXISTS. Therefore, you can change NOT IN to NOT EXISTS in the preceding statement.