| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Index nearing maxextents
A copy of this was sent to tkyte_at_us.oracle.com (Thomas Kyte)
(if that email address didn't require changing)
On Thu, 12 Nov 1998 18:42:46 GMT, you wrote:
>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:
>
[snip]
>>
>>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?
>
I would like to add to the following paragraph and point out that if you issue TRUNCATE and free storage and/or DROP commands against the object in question, you will want to keep the number of extents in the low 10's or 100's, not thousands as the added space management overhead will make the truncate and drop commands work slower.
So, if you plan on trunc'ing the table or dropping the table lots of times, keep the extents to 10's or hundreds -- otherwise, let em grow.
>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
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
--
http://govt.us.oracle.com/ -- downloadable utilities
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 Fri Nov 13 1998 - 08:59:18 CST
 |
 |