Lee,

> 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?

The underlying data type doesn't really matter too much when
considering the suitability of an index.

> 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...?

OK, I think I now understand you. You are referencing a timestamp as
in the time the current record was inserted rather than the SQL
concept of a timestamp; a field capable of storing any date and time.

If I do understand you correctly, I suppose it depends on the
frequency of inserts. If your program bulk inserts once a day, then
selectivity wont be the best. If your program trickle inserts the
records across the day, it will most likely be excellent.

>
>
> Query.SQLText :=
> 'SELECT ' +
> ' SALE.OID, ' +
> ' SALE.USER_OID, ' +
> ' SALE.CUSTOMER_OID, ' +
> ' SALE.SALE_ENTRY_TIME, ' +
> ' SALE.SALE_DEPART_TIME, ' +
> ' SALE.SALE_ENTRY_TIME, ' +
> ' POS_USER.USER_FIRST_NAME, ' +
> ' POS_USER.USER_LAST_NAME, ' +
> ' CUSTOMER.CUST_FIRST, ' +
> ' CUSTOMER.CUST_LAST ' +
> 'FROM ' +
> ' CUSTOMER ' +
> ' INNER JOIN ' +
> ' (POS_USER INNER JOIN SALE ON POS_USER.OID = ' +
> ' SALE.USER_OID) ON CUSTOMER.OID = SALE.CUSTOMER_OID ' +
> 'WHERE ' +
> ' SALE.DELIVERY_STATUS = :pDeliveryStatus ' +
> 'ORDER BY ' +
> ' SALE.SALE_DEPART_TIME DESC;';
>

I personally wouldn't bother with an index for ordering unless
* You are using First X or rows syntax to limit records.
* You really need the first page of results quicker. (A fetch all
results will most likely be faster without the index at all).

This query looks like it will be reasonably slow because
DeliveryStatus most likely has poorly selectivity, so you are doing a
tablescan. Unless you really really need every record, I would see if
you can't get some sort of date range into the where clause as well to
limit the returned records.

I also wouldn't bother using brackets around your inner join. You only
really need to do that if the join inside the brackets is some form of
outer join, and as it is you may be limiting the choices of the
optimiser (it has to join pos_user to sale prior to joining with
customer, which may not be the fastest in every case).

Adam