At 07:10 AM 13/07/2008, you wrote:

>Hi all,
>
>FB 2.1
>
>I have a query that will be ordering a TimeStamp field descending. Is it
>generally a good idea to place a descending index on a field like that?

If you're applying it for a DESC sort, it can help. At this stage of the game, the searching is complete and the engine is operating on an intermediate output set. If it makes a typical set return faster, then it's a good idea.

A DESC index can help a *search* if it applies to a search criterion on ColumnX that is targeted at the highest values of ColumnX or to an aggregation on MAX(ColumnX).

> I would
>assume that a Time Stamp would have fairly low selectivity, but I guess that
>would depend on how many records are being inserted consistently...?

A timestamp is a unique moment in time. So, for example, 2008-07-12 14:22:45.123 and 2008-07-12 14:22:45.124 are distinct nodes in the index. If each continues to live solo in its node then it is unique. Unique = highest possible selectivity.

If by "inserted consistently" you mean that the timestamp is read from server time, not from client time, then the possibility of its being unique is high. "Non-uniqueness" really depends on when and how the timestamp is recorded.

As an example, if you're posting and committing the sales transactions one by one and using a Before Insert trigger to write the timestamp, then the chance of a duplication is minute - another terminal writes a transaction within the same millisecond. On the other hand, if you're gathering up all the transactions once a month and writing CURRENT_TIMESTAMP onto that column on all of those records inside one execution of a stored procedure, they'll all have the same timestamp value. (In the latter example, selectivity would be potentially a little better if the SP wrote the timestamp from the value 'NOW'.)

> 'WHERE ' +
> ' SALE.DELIVERY_STATUS = :pDeliveryStatus ' +
> 'ORDER BY ' +
> ' SALE.SALE_DEPART_TIME DESC;';

The SALE.DELIVERY_STATUS column will have extremely low selectivity....so the search is going to be slow intrinsically and any index on it will get in the way. In your example, you're not selecting on SALE.SALE_DEPART_TIME so its indexes aren't involved in the search. However, because you *need* the descending order, the DESC index could be useful if the output set is large. Test it with and without, with a "midstream" cache in both cases, and see whether it makes any difference. That's the bottom line, always.

./heLen