Re: How to reorg a table?
Date: 1995/09/15
Message-ID: <1995Sep15.101617.1_at_cbr.hhcs.gov.au>#1/1
In article <42n4ho$15ec_at_news.doit.wisc.edu>, scarrola_at_mailsrvr.bussvc.wisc.edu (Steve Carrola) writes:
> In databases like DB2, you can reorganize a table so that the physical
> file is placed in sorted order by the primary key. This usually increases
> performance. Does the export/import of a table take care of this?
No.
> If so, is there another way to do this without an export/import? with
> the table on-line?
Look at CLUSTER's. You can specify how rows are grouped by a specific key and the database will then maintain that order. Be aware that you can waste an awful lot of block space if you choose an inappropriate blocking factor etc.
The manual method is to drop all indexes except the one you want the data sorted by, create another table as select * from your table using your index (eg where indcol > ' ') and thus load the data in in sorted order. The problem with this method is that if your records vary a LOT in size then smaller records will be used to "top up" blocks where a larger record wouldn't fit so you introduce unsorting again.
Alternate manual method is to unload your table to a flat file and then use SQL*Loader (with bulk insert). This removes the "top up" problem above.
All manual methods fall down over time as new records are inserted and the order is lost.
You should be careful with sorting the rows on one key because if the data is accessed on a different key then the data may be spread all over the table storage area. Sorting may dramaticly improve performance on one access method but also dramaticly degrade performance on all other access paths.
I have heard rumours that a later Oracle release (7.3?) allows rebuilding of indexes (ie repacking to minimum depth) without having to drop and recreate them and that table sorting may be included somehow.
Bruce...
- Bruce Pihlamae -- Database Administration *
- Commonwealth Department of Human Services and Health *
- Canberra, ACT, Australia (W) 06-289-7056 * *=================================================================*
- These are my own thoughts and opinions, few that I have. *
"The more complex the argument gets, the easier it is to refute." "Killing is wrong!" -- Trent 'The Uncatchable' Castanaveras Received on Fri Sep 15 1995 - 00:00:00 CEST