Yes by changing num_rows we can get right cardinality.
But for single table access path oracle would still use a FTS.
Quoting amit poddar <amit.poddar@xxxxxxxx>:
"You say you can't change the query, so if you want to fake this query, you probably need to use dbms_stats.set_table_stats
to tell Oracle that the table has a very small number of rows -
but don't change the block count - so that a properly calculated
selectivity is fooled into getting the right cardinality."
Sorry for being dense, but how does changing num_rows change the selectivity ?
It doesn't. Note that Jonathan said "so that a properly calculated selectivity is fooled into getting the right cardinality" i.e. the selectivity calculation (5% or 5% of 5%) isn't changed but the resulting cardinality estimate (num_rows * selectivity) is.