The following comment has been added to this issue:
Author: Christian d'Heureuse
Created: Tue, 28 Sep 2004 7:08 AM
Body:
Thanks for your explanations.
For the ordered numbering I could write a stored procedure that inserts the
data row for row.
What do you think about a (system) stored procedure to re-load a table, so that
the records are physically sorted by the primary key? Maybe it could be an
extension to SYSCS_UTIL.SYSCS_COMPRESS_TABLE.
I assume that this would not solve the "insert select" problem, because there
is no guarantee that the "physical" order would be used to insert the rows. But
it would be useful to optimize disk access for large tables, e.g. journal
records for adresses could be physically grouped together (for each address).
Otherwise one has to define an index that includes all columns that are
accessed.
Another solution would be to implement clustering indexes.
---------------------------------------------------------------------
View this comment:
http://issues.apache.org/jira/browse/DERBY-4?page=comments#action_53460
---------------------------------------------------------------------
View the issue:
http://issues.apache.org/jira/browse/DERBY-4
Here is an overview of the issue:
---------------------------------------------------------------------
Key: DERBY-4
Summary: "order by" is not supported for "insert ... select"
Type: New Feature
Status: Unassigned
Priority: Minor
Project: Derby
Assignee:
Reporter: Christian d'Heureuse
Created: Mon, 27 Sep 2004 10:53 AM
Updated: Tue, 28 Sep 2004 7:08 AM
Description:
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.
---------------------------------------------------------------------
JIRA INFORMATION:
This message is automatically generated by JIRA.
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
If you want more information on JIRA, or have a bug to report see:
http://www.atlassian.com/software/jira
