I have a table in my db (ASA 9.0.2.2451) that is getting its rows out
of order. The db was originally ASA 7 and has been upgraded to ASA
9.0.2.


Original table structure is this:
ELSDATE date NOT NULL,
FIRST_SHIFT_START timestamp NULL,
FIRST_SHIFT_SEQ T_SHIFT_SEQ (double) NULL,
FIRST_SHIFT_REGNUM T_REGNUM (tinyint) NULL,
LAST_SHIFT_END timestamp NULL,
LAST_SHIFT_SEQ T_SHIFT_SEQ (double) NULL,
LAST_SHIFT_REGNUM T_REGNUM (tinyint) NULL,
CUTOFF timestamp NULL,
TOTAL_SALES T_DOLLARTOT (decimal(14,2)) NULL,
FINALIZED timestamp NULL;
No foreign keys, no constraints, index on ELSDATE.


We tried changing the index to be clustered, but it didn't seem to
help.


We periodically will run a purge on the database. Here is a simplified
example of the steps/code (Delphi) that purges this particular table:
1. Run the following to get # of records to be purged.
SELECT count(*) FROM DAY_CLOSE_CUTOFF WHERE ELSDATE < '2007-08-23';
2. Run the following to get # of records that will NOT be purged.
SELECT count(*) FROM DAY_CLOSE_CUTOFF WHERE ELSDATE >= '2007-08-23';
3. If # of records that will NOT be purged = 0 then subtract 1
from # of records to be purged. (Must keep at least 1 row in this
table.)
4. Do the following, which loops through until iCount <= 0:
if iCount < 500 then
iPurgeCount := iCount
else
iPurgeCount := 500;
while iCount > 0 do
begin
iLoopCount := iLoopCount + 1;
self.dbJournal.StartTransaction;
oPurge.SQL.Text := 'DELETE TOP ' + intToStr(iPurgeCount) +
' FROM DAY_CLOSE_CUTOFF WHERE ELSDATE <
''2007-08-23'' ORDER BY ELSDATE';
self.dbJournal.Commit;
iCount := iCount - iPurgeCount;
if iCount < 500 then
iPurgeCount := iCount;
end; //while


We think this purging is what is causing the rows to get out of order
once new rows are inserted after the purge.


I apply the following script to the db:
IF exists (SELECT * FROM sys.sysindexes WHERE iname = 'elsdate' AND
tname = 'DAY_CLOSE_CUTOFF') THEN
DROP INDEX "DAY_CLOSE_CUTOFF"."elsdate";
END IF;
IF (SELECT primary_key FROM sys.syscatalog WHERE tname =
'DAY_CLOSE_CUTOFF') = 'N' THEN
ALTER TABLE "DBA"."DAY_CLOSE_CUTOFF" ADD PRIMARY KEY ("ELSDATE");
END IF;
ALTER INDEX PRIMARY KEY ON "DBA"."DAY_CLOSE_CUTOFF" CLUSTERED;
REORGANIZE TABLE "DBA"."DAY_CLOSE_CUTOFF";
commit;


For some db's, the table is then immediately ordered correctly. For
others, it is not. I can run the reorganize multiple times in a row
and it will keep moving around a couple of the rows, but never put
them in the correct order. If I unload/reload the db, then the rows
are in the correct order.


I have also added running 'REORGANIZE TABLE "DBA"."DAY_CLOSE_CUTOFF";'
to the end of the purge process now.


What am I doing wrong that we sometimes have to unload/reload before
the table will actually have the rows in the correct order? What can
we do to prevent this from happening again in the future?

-Heather

In SQL Anywhere, clustering is an indexing hint - the physical structure
of a table is unchanged whether or not there is a clustering index on
the table.

If you require ordering of a result set in a SELECT statement, use an
ORDER BY clause.

Glenn

Heather K. wrote:
--
Glenn Paulley
Director, Engineering (Query Processing)
iAnywhere Solutions Engineering

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://case-express.sybase.com

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the iAnywhere
Developer Community at www.ianywhere.com/developer

The reorganize table statement simply orders the rows internally (i.e.,
on the table pages in the database file). If you want your result set to
be ordered, your query must include an ORDER BY. From the SQL Anywhere
8 release notes:

Row ordering A side-effect of improvements to query processing for

version 8.0 is that row ordering is less deterministic. In the absence
of an ORDER BY clause, Adaptive Server Anywhere returns rows in
whatever order is most efficient. This means the appearance of result
sets may vary depending on when you last accessed the row and other
factors. The only way to ensure that rows are returned in a particular
order is to use ORDER BY.




Heather K. wrote:

Yes, I know that we can order the results of a query, but we were
hoping to maintain the order of the rows in the tables themselves, in
case we still have something out there with a query that doesn't use
ORDER BY...

-Heather

On Nov 21, 10:38 am, "Chris Keating (Sybase iAnywhere)"
<keating_spam_f... (AT) ianywhere (DOT) com> wrote:

If you require a resultset to be ordered, you MUST use ORDER BY. You may
simply be getting lucky that the resultset 'looked' ordered (you may in
fact find on closer inspection that the resultset is not ordered). Even
if the resultset is ordered, you may rerun the same query at a different
time and the resultset will not have the same ordering. The only way to
ensure consistent ordering is with ORDER BY.

Heather K. wrote:

In article <f1db9849-eb0e-4694-b445-2dcd3ae1fd65
@p69g2000hsa.googlegroups.com>, hkinney (AT) pinncorp (DOT) com says...
Ain't gonna happen. You can't control the physical order of rows in the
database, nor can you control the order they are retrieved from the
database. The only thing you can control is the order they are returned
to the client, and for that you have to use an ORDER BY. This is true
for ALL backend databases AFAIK. The only exceptions I know of are
file-based ones like Access, dBase, and Paradox.


--
Remove the ns_ from if replying by e-mail (but keep posts in the
newsgroups if possible).

Hello,

Actually, all SQL standards say that SELECT without ORDER BY does not
guarantee any order. I already had found in the old SQL Anywhere 5.5 days
that even executing
SELECT ... FROM tabA ORDER BY colA
gives different row orders when executed two times without any change to
tabA in between in case colA contains duplicates. And as I wanted to
compare results, I had to change the ORDER BY, adding another column so
that the sorting was always unique.

Frank

On Wed, 21 Nov 2007 18:24:59 +0100, Chris Keating (Sybase iAnywhere)
<keating_spam_free (AT) ianywhere (DOT) com> wrote: