Shulou(Shulou.com)06/01 Report--

How to tune Oracle SQL part 4: an introduction to query optimizer concepts.

4 query optimizer concept

This chapter describes the most important concepts related to the query optimizer and its main components.

4.1 introduction to query optimizer

The query optimizer (referred to as optimizer for short) is the built-in database software used to determine the most effective way for SQL statements to access request data.

4.1.1 purpose of query optimizer

The optimizer attempts to generate the best execution plan for the SQL statement.

The optimizer selects the plan with the lowest cost among all the candidate plans under consideration. The optimizer uses available statistics to calculate costs. For a particular query in a given environment, the costing takes into account the factors of query execution, such as Imax O, CPU, and communication.

For example, query information about an employee whose position is a manager. If the optimizer statistics show that 80% of the employees are managers, the optimizer may consider a full table scan to be the most effective. However, if the statistics show that few employees are managers, it may be more efficient to return to the table based on rowid after reading the index rather than a full table scan.

Because the database has many internal statistics and tools available, the optimizer is usually better than the user in determining the best way to execute the statement. Therefore, all SQL statements use the optimizer.

4.1.2 cost-based optimization

Query optimization is the process of choosing the most efficient way to execute SQL statements.

SQL is a non-procedural language, so the optimizer is free to merge, reorganize, and process in any order. The database optimizes each SQL statement based on collected statistics about access data. The optimizer determines the best plan for SQL statements by examining multiple access methods (such as full table scan or index scan), different join methods (such as nested loops and hash joins), different join orders, and possible transformations.

For a given query and environment, the optimizer allocates a relative digital cost for each step of a possible plan, and then calculates these values together to generate an overall cost estimate of the plan. After calculating the cost of the alternative plan, the optimizer selects the plan with the lowest cost estimate. For this reason, an optimizer is sometimes called a cost-based optimizer (CBO) to compare it with a rule-based optimizer (RBO).

Note: the optimizer may not make the same decision from one version of the Oracle database to the next. In the latest version, the optimizer may make different decisions because it can get more comprehensive information about you and make more optimizer conversions. 4.1.3 execution Plan

The execution plan describes the recommended execution method for SQL statements.

The plan shows a combination of steps that the Oracle database uses to execute SQL statements. Each step either physically retrieves the data row from the database or prepares the data row for the user who issued the statement.

The execution plan shows the cost of the entire plan (in line 0) and each individual action. The cost is an internal unit, and the execution plan simply displays it for comparison with other plans. Therefore, you cannot adjust or change the cost value.

In the following figure, the optimizer generates two possible execution plans for the input SQL statement, estimates its cost using statistics, compares its cost, and then selects the plan with the lowest cost.

Figure 4-1 execution plan

4.1.3.1 query Block

The input to the optimizer is a parsed representation of the SQL statement.

Each SELECT block in the original SQL statement is represented internally by the query block. The query block can be a top-level statement, a subquery, or an unmerged view.

Example 4-1 query block

The following SQL statement consists of two query blocks. The subquery in parentheses is the internal query block. The external query block (the rest of the SQL statement) retrieves the names of employees in the department where the ID is provided by the subquery. The query form determines how query blocks relate to each other.

SELECT first_name, last_nameFROM hr.employeesWHERE department_id IN (SELECT department_id FROM hr.departments WHERE location_id = 1800); 4.1.3.2 query subplan

For each query block, the optimizer generates a query subplan.

The database optimizes the query block from bottom to top. Therefore, the database first optimizes the innermost query block and generates a subplan for it, and then generates an external query block that represents the entire query.

The number of possible plans for a query block is proportional to the number of objects in the FROM clause. The number increases exponentially with the number of objects. For example, the possible plan for a join of five tables is significantly higher than that for a join of two tables.

4.1.3.3 analogy of optimizer

The optimizer can be compared to an online travel consultant.

Cyclists want to know the most efficient bike route from point A to point B. Queries are like instructions such as "I need the most efficient route from point A to point B" or "I need the most efficient route from point A to point B through point C". The travel consultant uses an internal algorithm to determine the most efficient route, which depends on factors such as speed and difficulty. Cyclists can influence the travel consultant's decision by using instructions such as "I want to get there as soon as possible" or "I want the easiest ride".

In this analogy, the execution plan is a possible route generated by the travel consultant. Internally, the consultant can divide the entire route into several sub-routes (subplans) and calculate the efficiency of each sub-route separately. For example, a travel consultant might evaluate a moderately difficult sub-route for 15 minutes, another least difficult sub-route for 22 minutes, and so on.

The consultant selects the most efficient (lowest cost) overall route based on user-specified goals and available statistics on road and traffic conditions. The more accurate the statistics, the better the advice. For example, if consultants do not have access to information about traffic congestion, road closures and poor road conditions, the recommended route may be inefficient (costly).

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.