Re: Ynt: rowid and create table as...

On Wed, 13 Jun 2001 08:20:39 +0300, you wrote:

>
>Steffen Ramlow <s.ramlow_at_gmx.de> wrote in message
>news:9g5ncd$7dp9f$1_at_ID-54600.news.dfncis.de...
>> when i do:
>> create table xxx as select empno, ename from emp order by ename;
>>
>> can i be sure that the rows are inserted into xxx ordered by ename and
 that
>> the rowids are ascending?

the data will be inserted ordered meaning that initially (before any other inserts/updates/deletes) data on a given block will be pretty much sorted. All of the A's will be together, the B's and so on.

You cannot make any assumption about the rowid which is a function of the file/block/slot.

We may use file 5 and after running out of room then file 3.

We may use an extent from the end of the file and then the front.

The data will be physically colacated based on ename initially but the rowids should be considered random data.

>I think it depends on the version you use...starting from oracle 8i you may
>use "create table xxx select ......from table ORDER BY ..." and the rows are
>inserted in the order which you chose..If I am wrong please correct me...
>
>Murat
>
>
>
>>
>>
>>
>>
>>
>

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://asktom.oracle.com/ http://asktom.oracle.com/~tkyte/
Oracle Magazine: http://www.oracle.com/oramag

Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  

Opinions are mine and do not necessarily reflect those of Oracle Corp