db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From George H <george....@gmail.com>
Subject Re: Inserts are out of order
Date Fri, 11 Sep 2009 10:44:09 GMT
Thanks for the explanations.

I have tried adding a 3rd identity column, just numbers increasing. And the
regular select shows them in the order of the identity column. This is one
solution I might consider.

I have tried running
select * from POS.SECTIONS_REL_ITEMS --DERBY-PROPERTIES index=null;  to no
avail. I still get the same results.

I bet if I had INTEGER instead of VARCHAR as the 2 keys, they'd be retrieved
in the same order I inserted them in just because of the natural
auto-increment ordering. Though I have to use those 36 char generated keys
and they may not be in proper order, that's why I can't use an ORDER BY in
my select. Unless I add an extra auto-increment column as my sort key.

Again thanks for the help.
--
George
george.dma@gmail.com


On Fri, Sep 11, 2009 at 12:59 PM, Knut Anders Hatlen <Knut.Hatlen@sun.com>wrote:

> 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
> > 10.5.3.0 - (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                                           ITEM_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
>

Mime
View raw message