SlideServe
Télécharger Obtenir Pro
Menu
Accueil Pro
Préférences
ApparenceLight Aide et commentaires
© SlideServe · v2026

SQL Statement Optimization: The Optimizer's Job and Execution Plans

jfink avatar
jfink
31 diapositives · 357 Views · Jan 04, 2020

Learn about how ORACLE optimizes SQL statements, including the evaluation of expressions, statement transformation, and optimizing complex statements and views. Explore the different optimization approaches and the architecture of the optimizer.

#sql statement optimization: the optimizer's job and execution plans #sql statement optimization: the optimizer's job and execution plans ppt #powerpoint #presentation
SQL Statement Optimization: The Optimizer's Job and Execution Plans - slide 1
01 / 31SQL Statement Optimization: The Optimizer's Job and Execution Plans
1 / 17

Télécharger la présentation

SQL Statement Optimization: The Optimizer's Job and Execution Plans
Une image / un lien ci-dessous est fourni (tel quel) pour télécharger la présentation

Politique de téléchargement : le contenu du site vous est fourni TEL QUEL pour votre information et votre usage personnel ; il ne peut être vendu, concédé sous licence ni partagé sur d’autres sites sans l’accord de son auteur.Le contenu vous est fourni TEL QUEL, pour votre information et votre usage personnel uniquement. Téléchargez la présentation en cliquant sur ce lien. Lors du téléchargement, si vous ne parvenez pas à télécharger une présentation, l’éditeur a peut-être supprimé le fichier de son serveur.Pendant le téléchargement, si vous ne parvenez pas à obtenir une présentation, le fichier a peut-être été supprimé par l’éditeur.

F I N

Plus de contenu associé

Transcription de la présentation

  1. The Optimizer How ORACLE optimizes SQL statements David Konopnicky 1997, Revised by Mordo Shalom 2004

  2. What is optimization? • Whenever a DML statement is issued, ORACLE must determine how to execute it. • There may be different ways to execute the statement. • The optimizer’s job is to choose one of these ways.

  3. Execution Plans • To execute a DML statement, ORACLE may have to perform many steps. • Each step may: • retrieve rows from the DB. • prepare rows for the user in some way.

  4. Example SELECT ename,job,sal,dname FROM emp,dept WHERE emp.deptno=dept.deptno AND NOT EXISTS (SELECT * FROM salgrade WHERE emp.sal BETWEEN lowsal AND hisal)

  5. Filter the results Join rows from emp and dept Read all the rows Use the ROWID to find the row For each emp, use the deptno value to search the index. It returns a ROWID Physical access to the DB (Access Paths) An Execution Plan

  6. Return the row (or not) Select the rows Join the rows Find the row Get all the rows and return them, one at a time to step 2 Find the ROWID of the dept Order of Execution For each emp...

  7. The explain plan command

  8. Rule-based: Choose an execution plan based on the access path available and choose the access path using a heuristic ranking. Cost-based Generate a set of possible access paths. Evaluate the cost of each access path based on the data distribution and statistics. Choose the plan with the smallest cost. Two approaches to optimization

  9. How the optimization is done • Evaluation of expression and conditions • Statement transformation • View merging • Choice: rule-based or cost-based • Choice of access paths • Choice of join orders • Choice of join operation

  10. SQL Processing Architecture

  11. Optimizer Architecture

  12. Choosing an Optimization Approach and Goal • OPTIMIZER_MODE: • CHOOSE: If there are some statistics then cost-based else rule-based. • ALL_ROWS (best throughput) • RULE: rule-based approach (for backward compatibility) • FIRST_ROWS_n (best response time) • Hints in the query.

  13. Evaluating Expressions and conditions • Constants: • sal > 24000/12  sal > 2000 • sal*12 > 2000  No evaluation • LIKE: • ename LIKE ‘SMITH’ • ename = ‘SMITH’ (if variable length) • BETWEEN: • sal BETWEEN 2000 AND 3000 • sal >= 2000 AND sal <= 3000

  14. dept.deptno=20 Evaluating...(cont) • Transitivity: Select * From emp, dept Where emp.deptno = 20 and emp.deptno = dept.deptno

  15. Selectivity Cardinality Estimator’s Tools

  16. UNION ALL SELECT * FROM EMP WHERE deptno = 10 Transforming statements • OR’s into Compound Queries: SELECT * FROM EMP WHERE job = ‘Clerk’ OR deptno = 10

  17. Optimizing Complex Statement • Transform the complex statement in a join statement and optimize the join statement • Optimize the complex statement as it is

  18. Example customer.custno must be a primary key • SELECT * FROM accounts WHERE custno IN (SELECT custno FROM customers) • SELECT accounts.* FROM accounts,customers WHERE account.custno = customers.custno

  19. The corresponding execution plan

  20. If it cannot be transformed • Optimize and execute the query and the subqueries independently. • For example: SELECT * FROM accounts WHERE accounts.balance > ( SELECT AVG(balance) FROM accounts);

  21. Transforming statements that access views • Merge the view’s query into the statement • Predicate Pushing • Then optimize the resulting statement.

  22. View: CREATE VIEW emp_10 AS SELECT * FROM emp WHERE deptno = 10; Statement: SELECT empno FROM emp_10 WHERE empno > 7800 SELECT empno FROM emp WHERE empno>7800 and deptno = 10; Merging the view’s query

  23. Complex View Merging if the view’s query contains(and enabled by parameter): • Set operator • Group by • DISTINCT • Group Function

  24. View: CREATE VIEW group AS SELECT AVG(sal) avg_sal, MIN(sal) min_sal, MAX(sal) max_sal FROM emp GROUP BY deptno; Statement: SELECT * FROM group WHERE deptno=10 Example Select AVG(sal) avg_sal, MIN(sal) min_sal, MAX(sal) max_sal FROM emp WHERE deptno = 10 GROUP BY deptno

  25. The execution plan

  26. View: CREATE VIEW emp AS SELECT * FROM emp1 UNION SELECT * FROM emp2; Statement: SELECT empno,ename FROM emp WHERE deptno=20; Predicate Pushing

  27. We will execute... SELECT * FROM emp1 WHERE deptno=20 UNION SELECT * FROM emp2 WHERE deptno=20

  28. The execution plan

  29. Optimizing other statements that access views • ORACLE cannot always merge views’ queries and statements. • In these cases, ORACLE issues the view’s query, collects the rows and then access this set of rows with the original statement as thought it was a table.

  30. View: CREATE VIEW group AS SELECT deptno, AVG(sal) avg_sal, MIN(sal) min_sal, MAX(sal) max_sal FROM emp GROUP BY deptno Statement: SELECT group.deptno, avg_sal, min_sal, max_sal, dname,loc FROM group,dept WHERE group.deptno=dept.deptno Example

  31. Execution Plan