Re: Insert from another table...Create with ORDER BY

Narayanan Olagappan wrote:
>
> Jonathan Lewis wrote:
> >
> > Tomm Carr <tommcatt_at_geocities.com> wrote in article
> > <33F481A3.2A68_at_geocities.com>...
> > > eberry_at_dstcorp.com wrote:
> > > Oracle is a *relational* dbms, and remember one of the
 characteristics
> > > of a relational database is the ordering of the tuples is not
> > > significant. Oracle does not allow the ORDER BY in the insert
 statement
> > > because it is meaningless. If you will be accessing the data
 ordered by
> > > -- apparently -- col1, then create an index on col1.
> >
> > 'tuples' are logical, rows are physical.
> >
> > It may be very sensible for performance reasons to arrange the rows
> > in a specific physical order.
>
> True, this kind of constructs will help in performance.
>
> There is a work around if anyone interested:
>
> Making use of other functions which have implicit sorting, like group
> by, union etc. For eg:
>
> insert into <new tbl>
> <query>
> union
> <query>

Create an index on the column you want to order by and find the minimum value in the table
then

insert into ...
select ...
from ...
where col >= min_Value

should do the trick

-- 
Regards,

Virag

----------------------------------------------------------------------------
Virag Saksena                                        
vsaksena_at_us.oracle.com
Senior Principal Consultant                                    
650.506.5087
System Performance Group
Oracle Services
----------------------------------------------------------------------------