Re: Inserts are out of order

Sylvain Leroux Fri, 11 Sep 2009 06:29:02 -0700

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 beretrieved in the same order I inserted them in just because of thenatural auto-increment ordering. Though I have to use those 36 chargenerated 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 [email protected]
<mailto:[email protected]>

On Fri, Sep 11, 2009 at 12:59 PM, Knut Anders Hatlen<[email protected] <mailto:[email protected]>> wrote:


George H <[email protected] <mailto:[email protected]>> 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

Re: Inserts are out of order

Reply via email to