I was wondering how you analyze your fact and dimension tables.
Our fact table is partitioned per month. Each partition contains 4M rows and is 270 MB large. We are using 9 dimensions, 6 have about 50'000 rows (2MB), 1 about 1M rows (50MB) and 2 about 3M rows (200MB). All tables are compressed. The version of Oracle we are using is 22.214.171.124.
What I was wondering is how you would, using dbms_stats, analyze the fact and dimension tables. Which percentage would you analyze? On which column would you build histograms?
any sample of more than 5% would be better and histograms for columns which are frequently joined.. ( which would mostly likely be indexed columns .. which otherwise i dont see why would an index be present
on that col/cols )
For partitions.. i would think stats for new partitions created would be sufficeint, as a general rule, where in application would have increasing amount of data on latest partitions which are mostly on date columns..
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
Generally so, I think. It's not always correct because estimating the number of rows to be accessed is a tricky business in the real world as Oracle doesn't have a mechanism for maintaining multi-column histograms, which would really help in many cases.
The key is to get the right value of optimizer index cost adj, or to collect system (hardware) statistics.