-
Ajo Robert authored
Optimizer chooses loose index scan (QUICK_GROUP_MIN_MAX_SELECT) for a group by even when there exists a predicate with disjunction. This is due to that either of the below two cases are encountered and not handled properly. 1. A range tree created for index merge scan is not taken into account while checking for the presence of disjuncted conditions in get_best_group_min_max(). 2. A disjunction condition could lead to null tree if an index merge scan is not possible. A NULL tree will cause get_best_group_min_max() to skip many relevent checks like WA2. There are two scenarios here, (a) The WHERE clause is a disjunction of conditions on MIN/MAX column only. => We can use min_max optimization considering all other criteria (eg:SA2) are met. (b) MIN/MAX column is participating in a disjunctive WHERE clause along with other columns. => min_max optimization is not applicable in this scenario. Fix: Skip loose index scan in below scenarios involving disjoint conditions in WHERE clause. 1. When the condition in WHERE clause results in more than one disjoint range trees (when index merge scan is possible). 2. The range tree is null and MIN/MAX column participates in the WHERE clause along with other columns. [There is no range tree if WHERE condition can't be represented in a single range tree and index merge is not possible] Change-Id: I2ec8ef815d9dfa64e0bf60e97d3b6129c070e24b
Ajo Robert authoredOptimizer chooses loose index scan (QUICK_GROUP_MIN_MAX_SELECT) for a group by even when there exists a predicate with disjunction. This is due to that either of the below two cases are encountered and not handled properly. 1. A range tree created for index merge scan is not taken into account while checking for the presence of disjuncted conditions in get_best_group_min_max(). 2. A disjunction condition could lead to null tree if an index merge scan is not possible. A NULL tree will cause get_best_group_min_max() to skip many relevent checks like WA2. There are two scenarios here, (a) The WHERE clause is a disjunction of conditions on MIN/MAX column only. => We can use min_max optimization considering all other criteria (eg:SA2) are met. (b) MIN/MAX column is participating in a disjunctive WHERE clause along with other columns. => min_max optimization is not applicable in this scenario. Fix: Skip loose index scan in below scenarios involving disjoint conditions in WHERE clause. 1. When the condition in WHERE clause results in more than one disjoint range trees (when index merge scan is possible). 2. The range tree is null and MIN/MAX column participates in the WHERE clause along with other columns. [There is no range tree if WHERE condition can't be represented in a single range tree and index merge is not possible] Change-Id: I2ec8ef815d9dfa64e0bf60e97d3b6129c070e24b
Loading