Performance Optimization-SQL statement Rewriting
There is such an SQL statement. In the system performance view, the execution time is 30 seconds, and the execution time is 6 seconds.
Select *
From tablea
Where cond1 and (A. field2 <> 6 and a. field1 not in (select Replace (field1, 'y', '') from tableb where ...)
Or
A. field2 = 6 and A. field1 not in (select field1 from tableb where ...)
)
Order by 1
Optimization steps
First, take out the statement before cond1 and execute it for about 0 seconds.
The statement for executing each separate subquery (subquery in not in) is also 0 s.
Execute the or statement separately. The execution speed is 0 s.
The analysis problem may occur in the merge of two subqueries. Simply rewrite the SQL statement into two independent statements and try the union operation.
Select * from
(
Select * From tablea where cond1 and A. field2 <> 6 and a. field1
Not in (select Replace (field1, 'y', '') from tableb where ...)
Union
Select * From tablea where cond1 and A. field2 = 6 and A. field1 not in (select field1 from tableb where ...)
)
Order by 1
0 seconds after execution, OK!