Re: Index nearing maxextents

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 12 Nov 1998 18:42:46 GMT
Message-ID: <36530e9f.11531381@192.86.155.100>

A copy of this was sent to Dan Morgan <dmorgan_at_exesolutions.com> (if that email address didn't require changing) On Wed, 11 Nov 1998 13:16:38 -0800, you wrote:

>> I have an index that is nearing its maxextents. I'm not sure which is the
>> best way to handle this:
>>
>> 1. export and re-import the table
>> 2. drop and re-create the index

If using 7.3, alter index rebuild would be the most efficient perhaps as far as speed (but would consume the most space as there must be enough room for 2 copies of the index momentarily).

I would, if using 7.3, alter the index and allow it to have more extents though...

>
>Drop and recreate the index. And by all means keep the index when you recreate
>it to one extent by creating an appropriate storage clause. Even though Oracle
>has now made it possible to have effectively unlimited extents this option is
>for the lazy that don't care about performance. A good rule of thumb is to never
>let the number of extents exceed 20.

 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ why?

whats the basis for that rule of thumb?

It is only relevant that you extent sizes be even multiples of you multi-block read count for full scans (of which you only infrequently do on an index). For scattered reads (such as an index scan performs where block1 points to block2 and block3 and block2 and block3 might be MILES from block1 on disk and from eachother), 1 extent or 100 extents or 1000 extents makes no difference -- you are reading a block at a time anyway.

>
>Daniel A. Morgan

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Nov 12 1998 - 12:42:46 CST