Several years ago, I made a presentation at the Independent Oracle Users Group conference, IOUG Live 2005, with the title “The Extensible Optimizer.” It’s been quite a long time since that presentation, but many people, especially developers, still are not aware that the cost-based optimizer (CBO) needs some extra input whenever you use a custom PL/SQL function in the WHERE clause of a SQL statement. I have seen so many applications performing poorly because they were using PL/SQL functions in SQL statements without arming the CBO with additional knowledge about those functions. Hence this chapter, in which I will show you how to help the CBO prepare optimal execution plans when using PL/SQL functions as part of your query conditions.
KeywordsExecution Plan Query Plan Domain Index Equality Predicate Clause Default
Unable to display preview. Download preview PDF.