Iris Eiron wrote:
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.
There is no guarantee of order of rows for any select query unless there
is an order by. Even if there is a index, the plan may choose not to
use this index.
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
