Access Path Selection in a Relational Database Management System

1. Access Path Selection in a Relational Database Management System P. Griffiths Selinger M. M. Astrahan D. D. Chamberlin R. A. Lorie T. G. Price Presentation : Ercan Çalışkan - 2000701034

2. Introduction In a high level query and data manipulation language such as SQL, requests are stated non-procedurally, without reference to access paths. This paper describes how System R chooses access paths for both simple and complex queries. System R : Experimental database management system based on the relational model of data which has been under development at the IBM San Jose Reasearch laboratory since 1975.

3. Processing of an SQL statement 1. Parsing : SQL statments are checked for correct syntax. 2. Optimization : Optimizer accumulates the names of tables and columns referenced in the query and looks them up in the System R catalogs to verify their existence and to retrieve information about them. Performs access path selection by using existing statistics. Forms the execution plan in the Access Specification Language (ASL) 3. Code Generation : Code Generator is a table-driven program which translates ASL trees into machine language code. 4. Execution

4. The Research Storage System (RSS) • RSS is the storage subsystem of System R responsible for maintaining: • Physical storage of relations • Access paths of these relations • Locking • Logging • Recovery facilities Relations are stored in the RSS as a collection of tuples whose columns are physically contiguous. Tuples are stored on 4 Kbyte pages. No tuple spans a page. Pages are organized into logical units called segments. Segments may contain one or more relations, but no relation may span a segment.

5. RSS Scans OPEN, NEXT, and CLOSE are the principal commands on scan. 1. Segment Scan examines all pages of the segment which contains tuples from any relation, and returns those tuples belonging to the given relation. 2. Index Scan searches for desired tuples using indexes. SARGS (Search arguments) Both scans may take a set of predicates called SARGS which are applied to a tuple before it is returned to the RSI. RSI : User Interface of System R

6. Cost for single relation access paths COST = PAGE FETCHES + W*(RSI CALLS) PAGE FETCHES : I/O operations W : Adjustable weighting factor between I/O and CPU RSI CALLS : The predicted number of tuples returned from RSS. Since most of System R’s CPU time is spent in the RSS, the number of RSI calls is a good approximation for CPU utilization.

7. Statistics kept in System R’s calalog For each relation T NCARD(T) : The cardinality of relation T TCARD(T) : The number of pages in the segment that holds tuples of relation T. P(T) : The fraction of data pages in the segment that holds tuples of relation T. P(T) = TCARD(T) / No. of non-empty pages in the segment. For each index I on relation T ICARD(T) : The number of distinct keys in index I. NINDX(T) : The number of pages in index I

8. Selectivity Factor F Using the statistics the Optimizer assigns a selectivity factor F for each boolean factor in the predicate list. This selectivity factor corresponds to the expected fraction of tuples which will satisfy the predicate. e.g. Column = value F = 1 / ICARD(column index) if there is an index on column F = arbitrary otherwise e.g. Column BETWEEN value1 AND value2 F = (value2-value1) / (high key value – low key value) A ratio of the BETWEEN value range to the entire key value range

9. Some cost formulas for single relation access paths SITUATION Unique index matching an equal predicate Clustered index I matching one or more boolean factors Non clustered index I matching one or more boolean factors Clustered index I not matching any boolean factors Segment scan COST 1 + 1 + W F(preds)* (NINDX(I)+TCARD) + W*RSICARD F(preds)* (NINDX(I)+NCARD) + W*RSICARD F(preds)* (NINDX(I)+TCARD) + W*RSICARD if this number fits in the System R buffer (NINDX(I) + TCARD) + W*RSICARD TCARD/P + W*RSICARD

10. Access path selection for joins Outer relation : Relation from which a tuple will be retrieved first Inner relation : Relation from which tuples will be retrieved, depending on the values obtained in the outer relation tuple. Join predicate : A predicate which relates columns of two tables to be joined. Join column: The column referenced in a join predicate JOIN METHODS Nested Loops : The scan on the outer relation is opened and the first tuple is retrieved. For each outer tuple obtained, a scan is opened on the inner relation to retrieve one at a time all tuples of inner relation that satisfy the join Predicate. Merging Scan : Relations are scanned in join column order. Merging Scans method is only applied to equi-joins

11. Order of join N-way joins can be visualized as a sequence of 2-way joins. If there are n relations then there are n! ways of joining. In joining relations t1, t2, t3, ..., tn only those orderings ti1, ti2, ti3, ..., tin are examined in which for all j (j=2, ..., n) either (1) tij has at least one join predicate with some relation tik, where k<j or (2) for all k > j, tik has no join predicate with ti1, ti2, ..., or ti(j-1) Example: Let T1, T2, T3 be relations such that there join predicates between T1 and T2 and between T2 and T3 on different columns than on T1-T2 join then T1 T2 T3 T2 T1 T3 T3 T1 T2 (exclude) T1 T3 T2 (exclude) T2 T3 T1 T3 T2 T1

12. Computation of costs Let C-outer(path1) be the cost of scanning the outer relation via path 1, N be the cardinality of the outer relation tuples which satisfy the applicable predicates: N = (Product of cardinalities of all relations T of the join so far) * (Product of selectivity factors of all applicable predicates) C-inner(path2) be the cost of scanning the inner relation C-nested loop join (path1,path2) = C-outer(path1) + N * C-inner (path2) C-merge (path1,path2) = C-outer(path1) + N * C-inner (path2) C-sort() includes the cost of retrieving the data, sorting the data, which may involve several passes and putting the results into a temporary list.

13. Example of Tree NAME SMITH JONES DOE DNO 50 50 51 JOB 12 5 5 SAL 8500 15000 9500 DNO 50 50 51 DNAME MFG BILLING SHIPPING LOC DENVER BOULDER DENVER EMP DEPT JOB 12 5 5 TITLE CLERK TYPIST SALES MECHANIC JOB SELECT NAME, TITLE, SAL, DNAME FROM EMP, DEPT, JOB WHERE TITLE=“CLERK” AND LOC =“DENVER” AND EMP.DNO = DEPT.DNO AND EMP.JOB = JOB.JOB

14. Example of Tree EMP DEPT JOB index EMP.DNO index EMP.JOB segment scan on EMP index DEPT.DNO segment scan on DEPT segment scan on JOB index JOB.JOB

15. Example of Tree

16. Example of Tree

17. Example of Tree

18. Example of Tree

19. Nested Queries SELECT NAME FROM EMPLOYEE WHERE DEPNO IN ( SELECT DEPNO FROM DEPARTMENT WHERE LOCATION=“DENVER” ) SELECT NAME FROM EMPLOYEE WHERE SALARY = ( SELECT AVG(SALARY) FROM EMPLOYEE ) Subquery is evaluated before the main query and is evaluated only once. SELECT NAME FROM EMPLOYEE X WHERE SALARY > ( SELECT SALARY FROM EMPLOYEE WHERE EMPLOYEE_NUMBER=X.MANAGER) Correlation Subquery must be re-evaluated for each candidate tuple from the referenced query block.

20. Conclusion • Although the costs predicted by the optimizer are often not accurate in • absolute value, the true optimal path is selected in a large majority of cases. • More work on validation of the optimizer cost formulas needs to be done, but • this preliminary work shows that database management systems can support • non-procedural query languages with performance comparable to those • supporting the current more procedural languales.