db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Sylvain Leroux <s...@wanadoo.fr>
Subject Re: Inserts are out of order
Date Fri, 11 Sep 2009 13:28:22 GMT
Not sure of your exact needs, but why not using something like:

CREATE TABLE POS.SECTIONS_REL_ITEMS
(
     SECTION_ID CHAR(36) NOT NULL,
     ITEM_ID CHAR(36) NOT NULL,
     FOREIGN KEY(SECTION_ID) REFERENCES POS.TEST_SECTIONS(SECTION_ID),
     FOREIGN KEY(ITEM_ID) REFERENCES POS.TEST_ITEMS(ITEM_ID),

     UNIQUE(SECTION_ID,ITEM_ID),
     ROW_NUM INT GENERATED ALWAYS AS IDENTITY
);

And:
SELECT * from POS.SECTIONS_REL_ITEMS ORDER BY ROW_NUM

AFAIK, It isn't a major change regarding your keys - and it gives you
the right order, *without relying on Derby's internal behavior*...



Sylvain


George H a écrit :
> 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
> <mailto:george.dma@gmail.com>
> 
> 
> On Fri, Sep 11, 2009 at 12:59 PM, Knut Anders Hatlen 
> <Knut.Hatlen@sun.com <mailto:Knut.Hatlen@sun.com>> wrote:
> 
> George H <george.dma@gmail.com <mailto: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
> 
> 


-- 
Website: http://www.chicoree.fr



Mime
View raw message