Assumptions of the Query Optimizer
If a database has not been optimized, the query optimizer assumes that:
• All exact match restrictions return 1% of the table, except where a key or index is defined to be unique, in which case one row is returned for the indexed attribute:
where emp.empno = 275
Note: To override the default of 1% for exact match qualifications, use the Configuration-By-Forms opf_exact_key parameter.
• All range qualifications (<, <=, >=, >) and like predicates, in which the first character is not a wildcard, return 10% of the table for each qualification. Thus, if there are three (non-exact match) qualifications, the following amount of the table is selected:
1/10 x 1/10 x 1/10 = 1/1000
Note: To override the default of 10% for range qualifications, use the CBF opf_range_key parameter.
• All “not equals” qualifications (<>) and like predicates, in which the first character is a wildcard, return 50% of the table for each qualification. The default 50% for these qualifications can be overidden by the Configuration-By-Forms opf_non_key parameter.
All joins are assumed to be one-to-one, based on the smaller data set; for example, when table1 with 100 rows is joined with table2 with 1000 rows, the estimated result is 100 rows.
• When there are restrictions on the join tables, the number of resulting rows is greater than or equal to the lower bound of 10% of qualifying rows from the smaller table.
If these assumptions are not valid for your data, you must optimize the database by generating statistics for it.
Last modified date: 08/14/2024