Oct 22, 2014 1:58:49 AM by Richard To
There are mainly three problems that database SQL optimizer cannot find the best way to process your SQL statements.
The cost estimation error is not only caused outdated statistics, but lots of other factors that a SQL's cost cannot be accurately estimated.(see : Why SQL Tuning?)
SQL optimizer internal rewrite may eliminates your SQL natural join order or fails to rewrite due to the complex SQL syntax.
SQL optimizer cannot explore all possible query plans within a limit time.
For certain SQL statements with bad performance problem due to inaccurate cost estimation by database SQL optimizer, here the following are examples that try to twist the error cost estimation. The dummy "+UID*0" are add to hide the literal '123456' from being considered by database SQL optimizer during SQL optimization phase. If you find a SQL with bind variables or hardcoded literals with unexpected bad performance, you can try this rewrite. Sometimes it may give you an unexpected performance improvement. If your SQL has more than one literals or bind variables, you can try to add this dummy function one by one during benchmarking. Have fun !
select * from employee where emp_id > 123456
select * from employee where emp_id > 123456+UID*0
select * from employee where emp_id > var_id
select * from employee where emp_id > var_id+UID*0
Written by Richard To
Richard specializes in using AI technique to solve database performance problems.