< Day Day Up > |
Do you recall the discussion of filter factors from Chapter 1, "The Magic Words"? The optimizer calculates the filter factor for a query's predicates based on the number of rows that will be filtered out by the predicates. The filter factor is a ratio (a number between 0 and 1) that estimates I/O costs. It is an estimate of the proportion of table rows for which a predicate is true. The formulas used by the optimizer to calculate the filter factor are proprietary IBM information, but Table 21.2 provides rough estimates for the formulas based on the type of predicate. These formulas assume uniform data distribution, so they should be used only when determining the filter factor for static SQL queries or queries on tables having no distribution statistics stored in the DB2 Catalog. The filter factor for dynamic SQL queries is calculated using the distribution statistics, in SYSCOLDIST , if available. Table 21.2. Filter Factor Formulas
For example, consider the following query: SELECT EMPNO, LASTNAME, SEX FROM DSN8810.EMP WHERE WORKDEPT = 'A00'; The column has an index called DSN8810.XEMP2 . If this query were being optimized by DB2, the filter factor for the WORKDEPT predicate would be calculated to estimate the number of I/Os needed to satisfy this request. Using the information in Table 21.2, you can see that the filter factor for this predicate is 1/ FIRSTKEYCARDF . So, if the value of the FIRSTKEYCARDF column in the SYSIBM.SYSINDEXES DB2 Catalog table is 9 , the filter factor for this query is 1/9, or .1111. In other words, DB2 assumes that approximately 11% of the rows from this table will satisfy this request. You might be wondering how this information helps you. Well, with a bit of practical knowledge, you can begin to determine how your SQL statements will perform before executing them. If you remember nothing else about filter factors, remember this: The lower the filter factor, the lower the cost and, in general, the more efficient your query will be. Therefore, you can easily see that as you further qualify a query with additional predicates, you make it more efficient because more data is filtered out and I/O requirements diminish. |
< Day Day Up > |