Reorganising a table

I’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’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’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’t check the syntax that closely and instead of adding a ‘where’ clause it could be used to add and ‘order by’ 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’t it documented?

I know there are other types of tables like indexed and clustered but these are not always suitable. Received on Mon Aug 12 2002 - 16:58:19 CDT