Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How to create a second table as sorted on one column of first table?

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

From: David Shi <dshi_at_magpage.com>
Date: 22 May 1998 23:04:34 GMT
Message-ID: <6k50a2$le1$0@204.179.92.131>


The reason for this is for improving performance on large table joins, if the joining key is already physically sorted, Oracle won't need to sort again in each join and should save a lot of time if tables are of millions of records.

On Thu, 21 May 1998 18:51:57 GMT, joelga_at_pebble.org (Joel Garry) wrote:

>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
Received on Fri May 22 1998 - 18:04:34 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US