What may not be obvious is that the RBO looks at rule #1. If that rule can be used, it uses that rule to execute the query and does not look any further. If Rule #1 fails, rule #2 is checked…and so on. The last rule is only used if all rules above it fail.
It shouldn't take too long to figure out why the RBO can sometimes make bad decisions. For instance, an index on the gender column (assuming even distribution) would take longer to satisfy the query than a full table scan. Yet such an index would always be used according to RBO. This is why the cost-based optimization (CBO) is preferred for today's Oracle implementations.
Dig Deeper on Oracle database administration
Related Q&A from Brian Peasland
Oracle expert Brian Peasland answers one reader's question about common pitfalls when connecting Oracle to outside programs. Continue Reading
One reader asks expert Brian Peasland a question about datafile sizes with the Oracle RMAN duplicate 10g command. Continue Reading
Managing parent table-child table relations in Oracle SQL environments is key to efficient programming. Continue Reading