Re: Sorting records in Physical and Logical files

Jerry Adams wrote:

What you are experiencing is what I always see when I open a table in
query; that is, records in arrival sequence sans any sort keys.

You can verify this by adding a record to any keyed table where the
key would not normally be at the end of the table and, then, bring
the file into query and go to the end of the list. Your new record
will be the last record.

That's just the way query works.

"Always"... Until it does not. :-) Those empirical results should not be inferred as "just the way query works." That quote implies there is a predictable outcome, for how the query [retrieves &] returns data when no ordering is requested. Without ordering & selection, and without any parallel retrieval, it is merely /highly probable/ that the collation will match the arrival sequence access path. But of course, "always" is disproved with only one contradiction to the claim, unlike with the claim "highly probable". And there is proof by at least one counter example, that the collation by arrival sequence may not be used. Both index-only access and parallel data access are simple examples that can contradict the perceived-as-consistent collation matching arrival sequence; there could be any variety of others, limited only by the possibilities enabled by the /optimizer/ in its choices of how to retrieve the requested data.

So... any query request sans ORDER BY should never be *assumed* to have any predictable collation, in order to avoid the eventuality of the /sometimes not/ in the assumed order. If any collation is desired, the order must be requested, to ensure that order as a predictable outcome.

Regards, Chuck