May 10, 2013 11:00:00 PM by Steve Hilker
This tip sheds some insight on why the SQL optimization process can take hours to run and what you can do to shorten the process.
When the SQL statement is using Views instead of Tables the optimization process has an option for rewriting the SELECT statement from the View as well as your original SQL statement. Say you are using three Views in your SQL statement. The optimization process would then rewrite four SQL statements (the original and all three views). You can see how this would add a lot of time to the optimization process particularly if any of the views are quite complex.
To have the optimization skip this process
You can also specify to have the optimization process just transform the syntax of the SQL statement and not apply any of the Oracle optimization hints.
Written by Steve Hilker
Steve Hilker was a Product Manager for Quest Software. Steve has over 35 years technical experience spanning application development, system administration, database management and various management positions at several software companies. Steve was the founder of RevealNet, best known for its desktop knowledge bases and unique database tools such as PL/Formatter. RevealNet was acquired by Quest Software in 2001. He's had the pleasure of being the product manager for many of Quest's database tools.
In his private life, Steve enjoys oil painting and living on a small farm in a pre-Civil war manor home in Fredericksburg, Virginia.