db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mike Matrigali (JIRA)" <j...@apache.org>
Subject [jira] Updated: (DERBY-4) "order by" is not supported for "insert ... select"
Date Tue, 22 May 2007 00:35:16 GMT

     [ https://issues.apache.org/jira/browse/DERBY-4?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel

Mike Matrigali updated DERBY-4:

Does Derby today guarantee anything other than uniqueness from "generated always"  fields?
 The current implementation
happens to return increasing keys, but do we ever guarantee that, and should we?  Will it
limit the ability in the future to 
optimize concurrency with respect to generating keys.  It may be easier to guarantee increasing
keys within a single statement, but not across threads inserting into the same table.  

An example of a cross thread optimization would be the following.  Today we allocate a chunk
of keys and cache them in
one place in the system.  That place is a bottleneck for all concurrent inserters.   To completely
alleviate the bottleneck we
could allocate a group of keys for each thread doing inserts, then they would only bottleneck
when they had used up the
group of keys.  And if we didn't want to "lose" key space we might reuse the keys from the
group that weren't exhausted which
then for a single thread might  result in keys not incrementing any more.  I believe there
are some issues reported in jira about concurrency problems with generated keys, so work in
this area may be required in the future.

> "order by" is not supported for "insert ... select"
> ---------------------------------------------------
>                 Key: DERBY-4
>                 URL: https://issues.apache.org/jira/browse/DERBY-4
>             Project: Derby
>          Issue Type: New Feature
>          Components: SQL
>            Reporter: Christian d'Heureuse
>         Assigned To: Bryan Pendleton
>            Priority: Minor
>         Attachments: insertOrderBy.diff, insertOrderBy_v2.diff, samples.ij
> When filling a table with "insert ... select ...", "order by" cannot be specified.
> There is not method to copy a table sorted into another table (except using export/import).
This would be useful to optimize performance for big tables, or to create identity values
that are ascending (related to another column).
> Example:
> create table temp1 (
>    s varchar(10));
> insert into temp1 values 'x','a','c','b','a';
> create table temp2 (
>    i integer not null
>       generated always as identity
>       primary key,
>    s varchar(10));
> insert into temp2 (s)
>    select s from temp1 order by s;
> --> Error: "order by" is not allowed.
> -- trying to use "group by" instead of "oder by":
> insert into temp2 (s)
>    select s from temp1 group by s;
> select * from temp2;
> --> "group by" did not sort the table.

This message is automatically generated by JIRA.
You can reply to this email to add a comment to the issue online.

View raw message