Re: insert statement using order by

Hi,

If you will not insert duplicated rows, you can use <DISTINCT> word which bring Oracle to sort rows, but duplicates will killed. Or <group by> also.

insert into T select distinct id, name ftom T0; insert into T select id, name ftom T0 group by id, name;

However Oracle does not guarant phisical order of rows in segment. Oracle will insert row into first free space is find. You can use suppress inserting using hint "APPEND". In this case Oracle will ignore any free space lower highwatermark and will append rows into new blocks created above highwatermark.

insert /*+APPEND*/ into T select distinct id, name ftom T0;

Note, this approach - phisically ordered tables - is acquittaled in case you will create index on this table. When you will create index on columns where all row values presorted you can use <NOSORT> word. Benefit is in that Oracle will not make sorting.

create index IT1 on T(id) nosort;
create index IT2 on T(id,name) nosort;

This approach have sence for big tables.

If you interested in "ordered" tables, take a look on the index-organized tables in Oracle8.
This stored whole data of raw in a primary key based on B-tree index. So you will have no "dual" logical reads (index-table) when select from the index-organized tables. Only index will being accessed. But you cant have other indexes on such tables because this have no ROWID.

Sorry, I have not sight your "order by DESC" in question :^).

You can use

insert /*+append*/ into backT
select -di, translate(eman,
 'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
 'ZYXWVUTSRQPONMLKJIHGFEDCBA'),
from (select distinct -id di, translate(name,

            'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
            'ZYXWVUTSRQPONMLKJIHGFEDCBA') eman
             from T)

But I'm only wandering for what cause do you need this? May be there is something wrong in a database design?

Mark Malakanov,
OraDBA
Sapience, Toronto

Stephen Pillow <stephen_pillow_at_i2.com> wrote in message news:_WqM3.3825$7K4.30709_at_typ11a.deja.bcandid.com...
> Hi all,
>
> I would like to be able to take data from one table and put the data in
> another table sorted. I have attempted to use the "insert into tablename
> (select * from othertable order by desc)". However, Oracle 8 and 8i do
not
> support this(it is a bug). Oracle did support the use of order by in a
sub
> query in release 7.3.4.
>
> I am looking for a solution that does not involve exporting the table data
> to file sorted and then loading it using sql loader.
>
> Thanks for your help,
> Stephen
>
>
>
Received on Mon Oct 11 1999 - 21:50:50 CDT