db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <Knut.Hat...@Sun.COM>
Subject Re: Order of Select when Identity column is defined
Date Wed, 22 Jul 2009 17:59:22 GMT
Mike Matrigali <mikem_app@sbcglobal.net> writes:

> 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.

You may force the optimizer to pick a plan that uses a specific index or
constraint. Then the result will be ordered the same way as the
index. See http://db.apache.org/derby/docs/10.5/tuning/ctunoptimzoverride.html

For the table above:

ALTER TABLE E ADD CONSTRAINT E_INSERT_ORDER_UNIQUE UNIQUE(INSERT_ORDER)

SELECT DATA, INSERT_ORDER
FROM E -- DERBY-PROPERTIES CONSTRAINT=E_INSERT_ORDER_UNIQUE
WHERE DOMAIN=?
FOR UPDATE OF DATA

-- 
Knut Anders

Mime
View raw message