db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mike Matrigali <mikem_...@sbcglobal.net>
Subject Re: Order of Select when Identity column is defined
Date Wed, 22 Jul 2009 15:31:06 GMT
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


Mime
View raw message