db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mike Matrigali <mikem_...@sbcglobal.net>
Subject Re: [jira] Created: (DERBY-4) "order by" is not supported for "insert ... select"
Date Tue, 28 Sep 2004 16:35:00 GMT
What is the expected behavior of the selecting from the heap
table which was inserted.  Is the ordering guaranteed?  Note
that the underlying storage system for derby does not guarantee that
rows selected out will be in the same order as they were input - it is a
matter of space alloction, the most obvious example is imagine a 10 page
base table that happens to have page 4 free, but is currently inserting
on page 10 - the next row will reuse the free page 4 and row ordering of
base tables is in "page" ordering.

Often it will be true especially in new empty tables, but requiring it
will put a lot of restriction on space allocation and reuse algorithms
in the future.



derby-dev@db.apache.org wrote:
> Message:
> 
>   A new issue has been created in JIRA.
> 
> ---------------------------------------------------------------------
> 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: Mon, 27 Sep 2004 10:53 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
> 
> 

Mime
View raw message