ORACLE数据库数据操作优化优化器讲.doc

The Optimizer SQL processing Optimization methods Execute SQL statements SQL Processing Architecture EXPLAIN PLAN What Is The Optimizer? Choosing an Optimizer Approach and Goal Cost-Based Optimizer (CBO) CBO Parameters Extensible Optimizer Rule-Based Optimizer (RBO) Overview of Optimizer Operations Optimizing Joins Optimizing Statements that Use Common Subexpressions Evaluation of Expressions and Conditions Transforming and Optimizing Statements SQL Processing Architecture Parser Syntax analysis: checks SQL statements for correct syntax. Semantic analysis: checks if the current database objects and attributes referenced are correct. Optimizer rule-based optimizer (RBO) cost-based optimizer (CBO) Row Source Generator Inputs: optimal plan from the optimizer Outputs: execution plan for the SQL statement Execution plan: a collection of row sources(a tree) Row source: an iterative control structure SQL Execution SQL execution: a component that operates on the execution plan Output: the results of the query. What Is The Optimizer? Optimizer: the most efficient way to execute a SQL statement DML statement: SELECT, INSERT, UPDATE, or DELETE Influence the optimizer: by setting the optimizer approach and goal, by gathering statistics for the CBO, by using hints in SQL statements Execution Plan An execution plan includes: an access method for each table and an ordering of the tables (the join order). SELECT ename, job, sal, dname FROM emp, dept WHERE emp.deptno = dept.deptno AND NOT EXISTS (SELECT * FROM salgrade WHERE emp.sal BETWEEN losal AND hisal); Steps of Execution Plan A row source : a set of rows returned by a step The numbering of the steps: the order for the EXPLAIN PLAN statement Steps indicated by the shaded boxes retrieve data from an object in the database. Such steps are called access paths: Steps 3 and 6 read all the rows of the emp and salgrade tables, respectively.? Step 5 looks up each deptno value in the pk_deptno index returned by step 3. Step 4 r