db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From derby-...@db.apache.org
Subject [jira] Commented: (DERBY-4) "order by" is not supported for "insert ... select"
Date Tue, 28 Sep 2004 14:08:33 GMT
The following comment has been added to this issue:

     Author: Christian d'Heureuse
    Created: Tue, 28 Sep 2004 7:08 AM
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:

View the issue:

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

   Reporter: Christian d'Heureuse

    Created: Mon, 27 Sep 2004 10:53 AM
    Updated: Tue, 28 Sep 2004 7:08 AM

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


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.

If you think it was sent incorrectly contact one of the administrators:

If you want more information on JIRA, or have a bug to report see:

View raw message