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

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