Re: Reorganising a table

In article <8ea7fbb6.0208121358.78a7ac11_at_posting.google.com>, michael.burden_at_cgey.com says...
>
>I&#8217;m probably going to show my ignorance here but what is the
>correct way to reorganize a table, not just the indexes?
>
>The problem is we have a big transaction table which is loaded with
>rows every night. The only update to existing rows is to, set a flag.
>Rows are never deleted (unless there has been an error) so
>there&#8217;s no danger of using up free space in the block.
>
>Now most key access is based on a policy number but obviously, as rows
>get written each night, a policy&#8217;s rows are scattered over many
>blocks. This means that an SQL statement using policy number hits many
>more blocks than if the rows were loaded in policy sequence.
>
>So assuming we use standard tables and indexes the question is what
>tool should we use to reorganize a table to suit the preferred index?
>I checked out import/export and SQL*loader but neither of these seemed
>to do the trick. Then I noticed that export has a query option which
>is documented as a method for adding a where clause to the SQL
>statement used by the export command. Then I thought, perhaps it
>doesn&#8217;t check the syntax that closely and instead of adding a
>&#8216;where&#8217; clause it could be used to add and &#8216;order
>by&#8217; clause, and to my surprise this worked.
>
>Now I mention this because:
>
>The order of rows in the base table does not seem to be discussed much
>in the manuals.
>There seems to be no documented way of ordering rows without either
>writing a program or SQL to do the job.
>If export can be used to order the output why isn&#8217;t it
>documented?
>
>I know there are other types of tables like indexed and clustered but
>these are not always suitable.

why aren't they suitable here? they are in fact (index organized and clustered tables -- either hash or b*tree cluster) the way to get physical clustering of similar data.

They would be the documented way of getting the rows with the same keys "together" physically.

You could do a CREATE TABLE new_table AS SELECT * FROM OLD_TABLE ORDER BY COLUMNS but that would be a waste as you constantly load this table. Having us preserved the order -- physically cluster the data during the load -- might be something to look into (will the load take longer? absolutely. Will the data be together? Yes. nothing is free here, you'll pay the penalty on insert)

Might be time, if you have shown this physical disorganization something to be concerned about (eg: it is a real problem, not a percieved problem) to look at clustered tables and or index organized ones.

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
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