What is cost based optimization? Also, what is the difference between a cost based and rule based optimizer?
As you may already know, a query optimizer is a part of the relational database software which is meant to analyze a SQL query and then figure out what the best to run that query. That is why it is called a query optimizer, because it’s goal is to optimize the query for the sake of efficiency.
How does a cost based optimizer work?
A cost based optimizer will look at all of the possible ways or scenarios in which a query can be executed – and each scenario will be assigned a ‘cost’, which indicates how efficiently that query can be run. Then, the cost based optimizer will pick the scenario that has the least cost and execute the query using that scenario, because that is the most efficient way to run the query.
Cost based optimizers use statistics from the database
Cost based optimizers have to use certain statistics that they collect from the database. Some examples of the types of statistics used by cost based optimizers include the number of number of unique values an indexed column has or even just the number of rows in a table. See our article on selectivity for more details on how cost based optimizers can use database statistics to make decisions.
Cost based versus rule-based optimizers
A rule based optimizer is an optimizer that just applies a set of rules to a SQL statement instead of looking at cost estimates in order to determine what the best way is to execute that SQL statement. Oracle actually allows you to use either the rule based or cost based optimizer, although Oracle says that rule based optimization will be deprecated in a future release, so it highly recommends the use of cost based optimization.