SQL Rewrite to Twist Cost Estimation

There are mainly three problems that database SQL optimizer cannot find the best way to process your SQL statements.

Cost estimation error

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?)

Internal rewrite limitation

SQL optimizer internal rewrite may eliminates your SQL natural join order or fails to rewrite due to the complex SQL syntax.

Limited size of plan space

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
Rewite to:
select * from employee where emp_id > 123456+UID*0
 
select * from employee where emp_id > var_id
Rewrite to:
select * from employee where emp_id > var_id+UID*0