|
|
|
While investigating partitioned file query performance, certain queries were identified to run in sub-optimal time, even when not partitioned. It was found that the optimizer would prefer an index for sorting rather then to optimize the query, thus resulting in slower performance. Forcing the join order with the "ctree ordered" clause was found to revert this condition.
Priority was almost always given to sorting when choosing an index and hence fix the join order such that tables with the sort columns are placed on the left (LHS and RHS switch case candidates are not considered during join method optimization). In some cases, it was found that choosing the correct join order based on ANL condition could lead to very low cardinality from the join node and hence better performance even if the sort node is retained in the plan.
For the switch case candidate, check if the sort hint was set for this join node and the sort columns are also from this node. If so, check if the cardinality of this join candidate is less than 1000, and also if the cardinality is 20 time less than the cardinality of the currently selected join candidate. If this is the case, allow the current candidate to be considered as one of the candidates, otherwise ignore it.
Note: Currently this will only allow switching when the sort columns are NOT from multiple tables.
