db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <Knut.Hat...@Sun.COM>
Subject Re: Inserts are out of order
Date Fri, 11 Sep 2009 09:59:43 GMT
George H <george.dma@gmail.com> writes:

> Hi, I have a strange problem and I am not sure if this is a problem with
> Insert or select or maybe I am missing something here.
> Apache Derby EmbeddedDriver
> - (802917)
> I have 3 tables and as I insert IDs into 1 table and do a select on it, I see
> the values in the same order I inserted them in.
> I do this for 2 tables. My third table is my relationship table which whose
> primary keys are the keys of both tables together.
> When I insert rows, I do not see them in the same order I inserted them in.
> Below I provide SQL statements that show this problem. I hope someone can
> guide me as to what is the problem and how to solve it. Many thanks in
> advance.
> select * from POS.SECTIONS_REL_ITEMS;
> Result
> SECTION_ID                                          
> b6c0bdf8-68c8-4882-9723-878574fefb52    04bc6246-02d3-43b8-81cd-399f52bc58d8
> b6c0bdf8-68c8-4882-9723-878574fefb52    0784c479-7cd6-4cef-a446-f7a3724321c4
> b6c0bdf8-68c8-4882-9723-878574fefb52    4e233de6-4044-4f73-9450-29833549700f
> b6c0bdf8-68c8-4882-9723-878574fefb52    9ee99832-c23a-41e6-bb85-e7f98035717c
> b6c0bdf8-68c8-4882-9723-878574fefb52    f481ba35-7f49-407a-aaed-fc6213ae1b90
> The Item IDs are out of order than how I inserted them in.

The optimizer probably chooses to scan the primary key index instead of
the base table, so you'll get the results sorted the same way as in the
index. Without an ORDER BY clause, a SELECT statement is free to return
the results in any order. If it's a requirement to get the result in a
certain order, you could have an extra auto-generated column[1] in the
table and order by that column. It is also possible to use optimizer
overrides[2] to prevent the optimizer from using the index, in which case
you'll get the rows in the order they are stored in the base table. The
optimizer override would look like this:

ij> select * from POS.SECTIONS_REL_ITEMS --DERBY-PROPERTIES index=null

[1] http://db.apache.org/derby/docs/10.5/devguide/cdevtricks21248.html
[2] http://db.apache.org/derby/docs/10.5/tuning/ctunoptimzoverride.html

Knut Anders

View raw message