Query processing - transformation of SQL statement into an efficient execution plan to return the requested data from the database.
Query optimization - process of choosing the most efficient execution plan. The goal is to achieve the result with the least cost in terms of resource usage.
SQL statement goes through 3 stages -
Parsing - Primarily consists of checking the syntax and semantics of SQL statements. End product of the parse stage of query optimization is the creation of the parse tree, which represents the query's structure. The query undergoes semantic checking. The data dictionary is consulted to ensure that the tables and the individual columns that are referenced in the query do exist, as well as the object privileges. Column types are checked to ensure that the data matches the column definitions. The query is rejected if it is incorrectly formulated. Once the parse tree passes all the syntactic and semantic checks, it's considered a valid parse tree, and it's sent to the logical query plan generation stage. All these operations take place in the library cache portion of the SGA.
Optimizing - Oracle uses its optimizer - the Cost-Based Optimizer (CBO) - to choose the best access method for retrieving data for the tables and indexes referred to in the query. Using statistics that you provide and hints specified in the SQL queries, the CBO produces an optimal execution plan for the SQL statement.
Query Rewrite Phase - the parse tree is converted into an abstract logical query plan. This is an initial pass at an actual query plan, and it contains only a general algebraic reformulation of the initial query.
Execution Plan Generation Phase - Oracle transforms the logical query plan into a physical query plan. The optimizer needs to choose the most efficient algorithm to answer a query, and it needs to determine the most efficient way to implement the operations. For example, the optimizer may decide that a join between table A and table B is called for.
Executing - During the final stage of query processing, the optimized query (the physical query plan that has been selected) is executed. If it's a SELECT statement, the rows are returned to the user. If it's an INSERT, UPDATE, or DELETE statement, the rows are modified. The SQL execution engine takes the execution plan provided by the optimization phase and executes it. The optimization process is most important because it determines how fast data will be retrieved.
Comments