Default result order

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Default result order

Trejkaz-3
Hi.

Due to various accidents we have ended up producing a table with
multiple entries for the same key (part of the accident is that the
index was not created to be unique...)

I would like to try writing a migration to delete out of date rows and
perhaps even make the index unique to avoid future issues of the same
sort.

I happened to notice that for my own database, the data appeared to be
in the order inserted, such that the correct row was always last. If
that is always the case, then I could scan through the whole table,
recording entries which are redundant, then scan through again to
delete the redundant entries. But this only works if I can guarantee
that it's always in that order.

In the Derby docs, there is text along these lines:

    Note: Make sure to specify the ORDER BY clause if you expect
    to retrieve a sorted result set. If you do not use an ORDER BY
    clause, the result set that is retrieved will typically have the order
    in which the records were inserted.

Here it says "typically", implying that in some situations this is not
the case. so I'm wondering in what situations it wouldn't come back in
this order.

TX
Reply | Threaded
Open this post in threaded view
|

Re: Default result order

Knut Anders Hatlen-5
Trejkaz <[hidden email]> writes:

> Hi.
>
> Due to various accidents we have ended up producing a table with
> multiple entries for the same key (part of the accident is that the
> index was not created to be unique...)
>
> I would like to try writing a migration to delete out of date rows and
> perhaps even make the index unique to avoid future issues of the same
> sort.
>
> I happened to notice that for my own database, the data appeared to be
> in the order inserted, such that the correct row was always last. If
> that is always the case, then I could scan through the whole table,
> recording entries which are redundant, then scan through again to
> delete the redundant entries. But this only works if I can guarantee
> that it's always in that order.
>
> In the Derby docs, there is text along these lines:
>
>     Note: Make sure to specify the ORDER BY clause if you expect
>     to retrieve a sorted result set. If you do not use an ORDER BY
>     clause, the result set that is retrieved will typically have the order
>     in which the records were inserted.
>
> Here it says "typically", implying that in some situations this is not
> the case. so I'm wondering in what situations it wouldn't come back in
> this order.

Hi Trejkaz,

I think the rows could come out in a different order at least in these
cases:

If the query optimizer chooses an index scan instead of a table scan,
the rows will come out in the order they have in the index.

If you have compressed the table (SYSCS_UTIL.SYSCS_COMPRESS_TABLE or
SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE), rows may have been reordered
on disk.

If you have deleted rows from the table, rows inserted after the
deletion may have been placed in the holes created by the deletion
rather than at the end of the table.

--
Knut Anders