I have been wondering why Oracle 11 does not follow explained plan for subquery execution with cost based optimiser.
It selects other index for execution which causes very slow performance 34M row table. Table got 900 000 rows in seven weeks.

Problem exists ofthen when query allways target newest rows by and should select date as first column (location as second). Later next times pretty soon it ends up select index with location as first column.
In queries there are 8 to 10 fields in where section with 3 to 5 predicates. There is few hundreds locations.

For up to now we have updated statistics to fix index selection.

I believe the problem being in new rows with no histograms and date as primary field.

I do not know how often statistics are updated in normal usage. Scheduled oracles own automation is not turned on.

And my question is:

Would same (date as first column) compressed index remove need update to histograms? Oracle could use data index inside compressed index for cardinality calculation, but will it do so?

To do this database would need to keep count of duplicate entries of data index inside compressed index to get exact cardinality very fast and without histograms.

If cardinality calculation is not currently affected as suggested this question, then this will come as suggestion of improment.