George H <[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
