Data of table not sorted by primary index in Control Center

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Antanas - 05 Dec 2005 18:29 GMT

Why is that even though I have added index with DESC sort order on
primary key, records in Control Center are displayed by the sequence
they were previously inserted, not by the value of primary key?
If I perform REORG on table primary index, records get sorted by
primary index properly. But when I enter new records afterwards, the
new ones appear again sorted in sequence they were inserted into table.
Why?

Reply to this Message

Ian - 05 Dec 2005 20:18 GMT

> Why is that even though I have added index with DESC sort order on
> primary key, records in Control Center are displayed by the sequence
> they were previously inserted, not by the value of primary key?
> If I perform REORG on table primary index, records get sorted by
> primary index properly. But when I enter new records afterwards, the
> new ones appear again sorted in sequence they were inserted into table.

SQL is set-based, and unless you specify an ORDER BY clause, there is
no guarantee that you'll get records back in any specific order.

The fact that you do get them in a noticeable order (i.e. order that
records were added or PK-order after the REORG) is "coincidence." (It
may be reproducible now, but other changes to your database and/or
config have a good chance of changing this.)

Good luck,

Reply to this Message

m0002a@yahoo.com - 05 Dec 2005 20:56 GMT

> > Why is that even though I have added index with DESC sort order on
> > primary key, records in Control Center are displayed by the sequence
[quoted text clipped - 12 lines]
>
> Good luck,

The indexes are always in the exact order of the index key. Table rows
are only in order of an index after a reorg (if you specify an index to
be used in the reorg, or the table has a clustered index defined).

Reply to this Message

Knut Stolze - 06 Dec 2005 11:22 GMT

>> > Why is that even though I have added index with DESC sort order on
>> > primary key, records in Control Center are displayed by the sequence
[quoted text clipped - 14 lines]
> are only in order of an index after a reorg (if you specify an index to
> be used in the reorg, or the table has a clustered index defined).

And even that does not imply _any_ order for a simple SELECT ... FROM
<table>. The _only_ way to enforce a specific ordering is the ORDER BY
clause.

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message