I finally got the following from the same index - regardless of exactly which columns were used in the index (except birthyear and mother's state always used). Had to use index hint because w/o bitmap merge would run and would take minutes or more - even with single bmp local indexes or bmp local indexes with birthyear + 1 other column. Regardless of size of bitmap_merge_... paramater related to pool for bitmapped merge indexes. Here are the speeds with the conc. index I ended up with on 5M row and 1.5 row table in inline view:
-for 3 year rolling average queries run between 3 - 12 secs depending on how selective the where clause is - the more columns the better from your stored proc - not just any column of course.
-for discrete - 3 to 12 secs. depending on how selective the where clause is
-for 5 year rate - 3 secs - hmm I wonder?
-for single years about 3 secs or better like 5 year rate just less years.
Again, concatenated bitmap local prefixed index with histograms on uneven data distribution column performed better then just local prefix btree or simple bitmapped index in merge.
