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

On 22 May 1998 23:04:34 GMT, David Shi <dshi_at_magpage.com> wrote:
>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.

For this scenario, you should use explain plan to discover whether it is indeed sorting again. See the Advanced Oracle Tuning book. If you are joining on something other than a subset of the primary key, you may have an intractable problem, and be justified in creating the second table - this is denormalizing for performance. Or, you may be amazed at what Oracle can do with large buffers.

It sounds like you want to do something like create table b as select y, z, x from a; exp/imp to compact and then define a primary key on b different than a. If you have large rows in a, you may want table b to be keys plus rowids to get the actual data from table a when you report.

Or not.

>
>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
>

--
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.