Implement cost calculation for the ORDER vs SORT choice when ordering is required for the query. The visible effects are:
1) ORDER ... INDEX plan is almost never used, being inferior to SORT in most cases -- maybe except some queries with OPTIMIZE FOR FIRST ROWS hint, explicit or implicit (e.g. SELECT FIRST). This is already proven in practice to work better.
2) SORT plan may be preferred to ORDER for the default OPTIMIZE FOR ALL ROWS scenario if its estimated as being cheaper.
3) Queries should not imply that ORDER plan will be used always if a suitable index exists. We have a number of QA tests that are affected by this change.
The cost calculation will be adjusted once more statistics will be available to the optimizer (e.g. clustering factor).
Implement cost calculation for the ORDER vs SORT choice when ordering is required for the query. The visible effects are:
1)
ORDER ... INDEXplan is almost never used, being inferior toSORTin most cases -- maybe except some queries withOPTIMIZE FOR FIRST ROWShint, explicit or implicit (e.g.SELECT FIRST). This is already proven in practice to work better. 2)SORTplan may be preferred toORDERfor the defaultOPTIMIZE FOR ALL ROWSscenario if its estimated as being cheaper. 3) Queries should not imply thatORDERplan will be used always if a suitable index exists. We have a number of QA tests that are affected by this change.The cost calculation will be adjusted once more statistics will be available to the optimizer (e.g. clustering factor).