How Oracle Optimizer chooses best plans?

sakthikumar used Ask the Experts™

I have following question related to oracle optimizer.

When a query is given to optimizer,
1. How it creates different plans without executing it.?
2. I believe only if the results are less than certain % of rows it uses index, for identifying this,
    it has to execute query right?

Please help me in understanding the above by giving some relevant examples or links.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012
1 - that's a huge question.
   If you really want to understand it I recommend reading Jonathan Lewis' book "Cost-Based Oracle Fundamentals"

As a quick list of some of the large factors-
the expected cardinalties of joins and filtering conditions
the availability of indexes, existence or absence of hints
the existence or absence of sql profiles
various init parameters
available temp space, memory and cpu
Parallel options on tables, indexes, partitions and subpartitions, etc.
ordering criteria

2 - no that is not correct.  Number of rows has almost nothing to do with it except incidentally.  Indexes are chosen based on the number anticipated blocks that will be read.  Of course, if you have few rows then the blocks will likely be smaller and if many rows the number of blocks will likely be larger; but the actual row count isn't particularly important.  It's the blocks.
Geert GOracle dba
Top Expert 2009

yeah, huge question
1. basically it comes down to statistics
most of the items sdstuber indicates are gathered in statistics
based on those statistics the optimizer doesn't have to execute anything to build an execution plan

2. again ... statistics

the oracle performance tuning guide contains a few chapters, here is one

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial