Re: Reorganising a table

Thanks for your comment.

My guess is that organizing the table will give great benefits. The table is over 300 Million rows. To reorganize the table though, it must first be partitioned as sorting this many rows may take a few minutes.

As for the type of table I have considered clustered (b-tree) and indexed organized tables. There are other tables I have my eye on for index organization but this table I believe is not a good candidate. One reason is secondary indexes on indexed organized tables may have a performance overhead if the row id becomes stale (if I’ve read the manual correctly). This gives me dangerous unknowns (i.e risks) and could make testing stale row ids extremely difficult. The customer is bound to ask me (after being warned) what impact will stale row ids have on performance. How do I answer this question? Also how do I generate stale row ids and how do you know when you’ve got one?

A clustered table (b-tree) does seem to have potential but I’m concerned about inserts into already full blocks. Although we can configure free space it will soon be used up and then I guess we are into splitting of blocks and, as you say, this will have some overhead. I would like to try this but my problem is quantifying the overheads for mass insert in advance (i.e. the impacted on the main update program). We’ve already lost three weeks work testing local indexes so I need to be sure of success before I embark on this route.

That’s why I asked about organizing the table as this method is easily quantifiable and has the least risks. I know we have free capacity at the weekend so I can reserve time each month (or even quarterly) for a re-organization and I know it has no detrimental impact on the nightly cycle. OK it may be not the best choice but it is simple!

I’m sure I’m not the only one who’s though about this but has played safe by opting for normal tables using b-tree indexes with the occasional re-org.

I don’t like admitting it but in all my time of using Oracle I have only come across normal tables with B-Tree indexes.

So my point is reorganizing a table is just one method in the armory of house keeping tools and so it would seem fair to suggest this method should be covered somewhere in the Oracle manuals.

Thomas Kyte <tkyte_at_oracle.com> wrote in message news:<aj9hi00a3l_at_drn.newsguy.com>...
> 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.
Received on Tue Aug 13 2002 - 17:28:37 CDT