Kbase P99320: SQL-92: When should I use {NO REORDER} ?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
16/10/2008 |
|
Status: Unverified
GOAL:
SQL-92: When should I use {NO REORDER} ?
GOAL:
What is the syntax of the {NO REORDER} option of the SELECT statement?
FACT(s) (Environment):
Progress 9.x
OpenEdge 10.x
FIX:
By default, the optimizer knows the least costly way to perform joins and you rarely have to intervene. In some cases, you may want to force optimizer to perform joins in certain order.
When evaluating joins, the optimizer uses the available statistics to estimate the cardinality (the number of rows per table) and selectivity (precentage of rows a predicate returns) for each table. By default, it joins the tables in the following order:
- Join the table with the lowest cardinality with the next lowest cardinality
- Join the result of the join with the table of next lowest cardinality and so on...
If you are thoroughly familiar with your application data and know that, in spite of the cardinalities, a different join order does yield for better selectivity (fewer intermediate rows), you may want to overwrite the default joint order by using the {NO REORDER} option of the SELECT statement. This syntax of this option is to place the string {NO-REORDER}, braces included, immediately after the FROM option of the SELECT statement:
SELECT <column list> FROM <table list> {NO-REORDER}
Please remember that the default joint order is based on table statistics. Therefore, these statistics should be kept up-to-date by executing UPDATE STATISTICS for the tables, indexes and columns of the database periodically.