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