I have a partitioned table like MY_TABLE(PART_KEY, DATE_KEY, ...) with a local partitioned index on (PART_KEY, DATE_KEY). When I collect stats on the table, I end up with a HEIGHT BALANCED histogram on DATE_KEY.
The issue: the DATE_KEY values are not uniformly distributed in each partition, and the buckets in ALL_TAB_HISTOGRAMS look like they reflect the distribution of values across the whole table.
The question: If I am running a query like SELECT * FROM MY_TABLE WHERE PART_KEY=x and DATE_KEY between y and z is there a way I ensure that the optimizer will use a histogram reflecting the distribution of values for the specific partition?
Otherwise, what I think is happening is that the global distribution makes certain ranges of values look more selective than they really are, given that the query will only hit a single partition.
dba_tab_histograms(orall_tab_histograms) will have the table-level histograms. It sounds like you're saying those are correct.dba_part_histogramswill have the partition-level histograms. Are those values accurate?