Oracle data distribution across date index

There's a situation that arises in large database tables (~50 million rows) where users need to browse date-indexed records.

But users browsing the data do not necessarily know where on the timeline data occurs and where the gaps are. In past projects, I've quantized the records into 24-hour "buckets", and used that to create a bar-graph timeline, where开发者_StackOverflow the height of the bar indicates the number of records in that period. This allowed users to focus in on areas where data actually exists.

The drawback of this solution is that the record counts must be continually updated and maintained (if data can be inserted/removed from any point on the timeline).

Is there a more elegant solution to getting these aggregate record counts? For instance, by peeking at a date index and seeing how many values there are associated with that index?

I'm not sure if this will actually work for you, but it sounds like what you're looking for are histograms. If a histogram exists for your index, you can query USER_HISTOGRAMS to get a rough idea of the distribution of values across the index. The downside of this is that it will only be accurate as of the last time statistics were gathered for your index, so if your data changes often, it may not be up-to-date.

No too sure what you are asking but faced with a similar situation i would do the following:

Partition the table on s_date. (is that what your "bucket" is, a partition?)

Create a bitmap index on s_date.

Generate statistics like this:

select s_date,  count(s_date) from big_table where s_date > '01-APR-11' group by s_date;

Also you may want to read up on oracle's "Gathering Optimizer Statistics", it's nice to know.

cheers.