How a database query optimizer functions

8.7.2014 Fabian Rendon
A database query optimizer is a built-in database program that is designed to find the most effective way for an SQL statement to get access to data. It will optimize queries written on the databases. The level of optimization depends on the amount of information about the databases that is known to the query processor and the way that they are organized.

The optimizer’s purpose is to create the ideal execution method for an SQL statement. It will be a plane that has the lowest cost and the cost is computed on the basis of factors like communication, CPU and Input/Output. The creation of the ideal technique will depend on a number of conditions such as the way the query gets written, the data layout, the data set size, and the existing access structures.

It will find the best-possible method for a statement by inspecting different access methods. This can include index scanning, full table scanning, and multiple join methods like hash joins and nested loops. The database already has several tools and stats available to it; this gives the optimizer an edge over the user when it comes to finding the ideal method for executing a statement. This is the reason almost all SQL statements will use the optimizer.

For example, consider that a user queries records to find employees who hold the designation of managers. The database statistics can point out that around 70% of the employees hold this position in the organization the optimizer may use the full table scan for highest level of efficiency. But if the stats indicate that not many employees are managers, the optimizer may use the method of reading an index and then accessing a table by row ID.

The database query optimizer will generate a query sub plan for every query block. The number of potential methods for a query block will depend on the number of objects found in the “From” clause. The number will rise at an exponential rate with the number of objects. For instance, the number of potential techniques for a join of 5 tables will be higher than those for a join of 2 tables.

Do you think this article is useful?

Register Box

Login Box