db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Iris Eiron <irisei...@gmail.com>
Subject Order of Select when Identity column is defined
Date Wed, 22 Jul 2009 06:32:38 GMT
Hello all,

I have a table E defined like that:

   CREATE TABLE E (
    ID VARCHAR(30) NOT NULL,
    DOMAIN VARCHAR(10) NOT NULL,
    DATA BLOB NOT NULL,
    INSERT_ORDER INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH
1, INCREMENT BY 1),
    LAST_UPDATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY ( ID , DOMAIN )
   )

My question is: In what order will the rows be returned if I run the
following statement on E:
   SELECT DATA, INSERT_ORDER
   FROM E
   WHERE DOMAIN=?
   FOR UPDATE OF DATA

I cannot specify an ORDER BY clause since Derby does not support  SELECT FOR
UPDATE with ORDER BY. Is there a guarantee that the rows will be returned by
the order of the identity column INSERT_ORDER? I was not able to find
documentation supporting this.

The answer for this question is the key for improving the perfroance of a
very expensive operation, that without order takes days to execute, and with
order we expect it to complete in a few hours.

Thanks for your help!

- Iris

Mime
View raw message