When there are search arguments and joins on a table, the selectivities of the columns are combined during join costing to estimate the number of rows more accurately.
The following example joins authors and stores on both the city and state columns. There is a search argument on authors.state, so search argument transitive closure adds the search argument for stores.state table also:
select au_fname, au_lname, stor_name from authors a, stores s where a.city = s.city and a.state = s.state and a.state = "GA"
If there is an index on city for each table, but no statistics available for state, the optimizer uses the default search argument selectivity (10%) combined with the total density for city. This overestimates the number of rows that match the search argument for this query, for a state with more rows that match a search argument on state, it would underestimate the number of rows. When statistics exist for state on each table, the estimate of the number of qualifying rows improves, and overall costing for the join query improves also.
