In the majority of the tables getting date column, we "would certainlyInch query for that recent information.
Could it be smart to index to start dating ? column "generally" in climbing down order?
Unfamiliar with Oracle's internals, but here's my knowledge of how it operates with Postgres:
Indexes are clustered for those intents and reasons. If you they are purchased asc and new rows will always be added for the finish from it (e.g. produced_at, up-to-date_at, charged_at, etc.), your brand-new rows is going to be appended (or nearly so) instead of prepended (resulting in disk page splits). This really is faster.
Your query planner will happily read a catalog backwards order. Therefore if it is a single column index, either works -- make use of the most basic if this involves how new rows are placed inside your use-situation.
Where a catalog purchased backwards order can become interesting, happens when you've a multi-column index. Say,
(id, created_at desc) within an audit log table. It's really a poor example, but here's the purpose: if you are ordering by
id, created_at desc, the index is going to be used out of the box.
Unless of course the thing is a substantial performance improvement you may wish to avoid climbing down indexes. Climbing down indexes are really function-based indexes, and also have a couple of restrictions.
For instance, based on the SQL Reference, unique climbing down indexes do not let multiple nulls, and climbing down indexes aren't used before you evaluate the index and table. (Although I'm not in a position to reproduce the 2nd limitation.)
Also, function-based indexes are quite different, and often break the index maintenance scripts that everyone creates. This is not a very good reason to prevent them, just something to consider.