Tweaking of SQL queries in DB2 - Use of Filter factor

Tweaking the SQL statements:

 Ø       Tweaking is the process of changing a SQL statement without changing its functionality

Ø       Filter Factor is a ratio that DB2 calculates for each possible access path, based on values stored in the DB2 catalog tables and the types of predicates in the SQL.

Ø       Lower the filter factor of a predicate higher the performance

Ø       Two methods by which this can be achieved are as follows:

 

1) Adding Redundant predicates to the query: 

Ø Filter Factor of a query can be lowered by adding Redundant predicates.

Ø Example: 

SELECT EMPNAME FROM EMP

 WHERE WORKDEPT =:WS-WORK-DEPT-NAME

 

Tuned query:

 SELECT EMPNAME FROM INFOSYS.EMP

 WHERE WORKDEPT  =:WS-WORK-DEPT-NAME

       AND WORKDEPT =:WS-WORK-DEPT-NAME

       AND WORKDEPT =:WS-WORK-DEPT-NAME

 

Reason:

Filter factor is reduced in the Tuned query to a much lower value because

Filter factor (tuned query) = 3 * (Filter factor (= predicate in first query))

 

2) Most Restrictive predicates at the beginning:

Ø DB2 evaluates the predicates first by predicate type and then the order in which it is encountered.

Ø 4 types of SQL predicates and their order of processing in DB2 is given below:

-  Equality (=)

-  Ranges (BETWEEN,>= & <=)

-  IN

-  Stage 2 predicates

Ø Example:

SELECT EMPNAME FROM EMP

WHERE SEX              = ‘M’

      AND IBU               = ‘RETL’

 

Tuned Query:

SELECT EMPNAME FROM EMP

WHERE IBU               = ‘RETL’

      AND SEX              = ‘M’

 

Reason:

In the tuned query, The first predicate (IBU = ‘RETL’) has a lower filter factor than the second (SEX = ‘M’), because there are fewer employees in IBU RETL when compared to the MALE employees in the entire company.
comments powered by Disqus