Re: How to create a second table as sorted on one column of first table?

On Wed, 20 May 1998 11:12:12 +0300, Wladislav Ovchinnikov <wlad_at_cbsd.donetsk.ua> wrote:
>Hi!
>
>Maxim V. Krapivin wrote:
>>
>> David Shi wrote :
>> >I tried:
>> > create table second as select * from first order by key;
>
>Try to export ordered data using SQL*Plus and import using SQL*Loader.
>
>> >but didn't work. Any help is greatly appreciated!
>> >David
>>
>> All right. The data in any table is unsorted. You can use ORDER BY clause
>> when fetching data from the table. But when you create one table from
>> another this means nothing.
>
>In fact rows ARE ordered. You can read about it at
>http://www.oramag.com/archives/27/27DBA.html (march/april 1997, "How
>healthy is your index?").

The distinction is between logical and physical database implementation. Logically, the order of the rows given back by a select are not guaranteed to be ordered, even if the physical rows are, unless there is an order by statement. Physically, you can order the rows by a simple import as Maxim says. This may have a great effect, especially if it solves a row-chaining type of fragmentation, or incorrect pctfree or whatever (and think what happens with a fully compacted table transferred to one with a large pctfree...).

So, if David is trying to do this to avoid an Order By in his app, he needs to know that's wrong. If he's trying to do it because he understands the tuning issues, good going. If he's trying to do it because that's what he did in another dbms... <shudder>.

>
>--
>
>Wladislav Ovchinnikov, DBA
>Donetskaya direktsiya Ukrsotsbanka
>Donetsk, Ukraine
>
>e-mail: wlad_at_cbsd.donetsk.ua

--
These opinions are my own and not necessarily those of Information Quest

or Pebble In The Sky                     http://www.informationquest.com

http://ourworld.compuserve.com/homepages/joel_garry jgarry@nospameiq.com
"See your DBA?"  I AM the @#%*& DBA!     Remove nospam to reply.  Sorry.