12. Using the Query Optimizer : Database Statistics : Assumptions of the Query Optimizer
 
Share this page                  
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 wild card, 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 wild card, 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.