At 03:24 PM 1/09/2005 +0000, you wrote:

>Thanks to HeLen, Adam and Alan for their replies.
>
>For information, tried dropping the constraints and re-adding the PK,
>set the sort order to descending and commited. The sort order defaults
>back to ascending.

No. There is no "default sort order". Changing the direction of an index
does not "change sort order", since there is no "sort order" to begin with
and there is, equally, none afterwards. If you do a SELECT on the table
without an ORDER BY clause, you will get the rows in an arbitrary
order. There will be some sense of cardinality to it that, early in the
life of the table, will be roughly "arrival sequence", based (loosely) on
the physical order in which the data pages are filled. Over time, this
physical order will change, as deleted row stubs and obsolete or
rolled-back record versions are removed by garbage collection and the
physical space is freed up and used for new rows and versions.

Indexes have no role to play in freshly reordering the rows after a
restore, either. The row data for a table is restored before constraints
and indexes, and in the order in which it was read out, i.e. page-by-page,
like so many truckloads of brown cows.

>To answer Helen's question 'Why', it's because the table will be
>used 'as standard' in descending sort order, not ascending. Therefore
>it 'uncomlicates' matters where one has to remember to add an ORDER BY
>clause each time the table is used in an application.

Not in a database that conforms to relational rules. Stored data has no
cardinality.

>The PK is based on a generator, actually a composite field of the
>generator and a string, and is used in a grid for selecting the latest
>(hence descending sort order) record entries rather than the oldest
>(historical) ones.

Now, perhaps you will understand that the "latest" cannot be reliably
expected to be delivered first *unless* you supply a determining ORDER BY
clause. That's also why a SELECT FIRST statement without an ORDER BY
clause is meaningless. So, if the age of the records is important, you
should have a suitably indexed timestamp in there for delivering these sets
in correct order.

>Will either use a second index or ORDER BY to continue.

If you have done the dirty deed and actually succeeded in creating a PK
with a descending index (syntax is on Pp 8-9 of the Fb 1.5 release notes)
then you already *have* the index. DO NOT create a second descending index
on the same columns, or you will mess up optimization of your queries. OK
to create an ASCENDING index on the same columns, if you have a use for it
(as you will, if you ever need to join on both columns, or on the first
column).

Check carefully now what you DO have - do a SHOW TABLE ThatTable in isql.

./heLen