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. [email protected] 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 > >
