db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Iris Eiron <irisei...@gmail.com>
Subject Re: Order of Select when Identity column is defined
Date Tue, 28 Jul 2009 15:28:19 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
>
> This is great! Thank you Knut!

What I origianlly wanted to do (but did not think I would be able to) was to
select by the order of the primary key - ORDER BY DOMAIN, ID. Can I still do
that using similar approach? I am guessing there is an index by the primay
key, but I do not know how it is called ... Should I create it explicitly?

Thanks again,
- Iris

>
> > --
> > Knut Anders
>
>
>

Mime
View raw message