Re: Unlimited extents

Someone who is foolish enough to set maxextents to unlimited with a next of 1K deserves as much trouble as they get. If you are more diligent in your sizing efforts and come out with a modest number of extents of fair size (30 extents, 10M each for example), I would not expect to see a significant performance degredation.

There are many arguments for creating a single huge extent that holds data for the next 5 years. I disagree with this system. It lowers your flexibility for allocating space and makes it a bit more difficult to track growth rates. I try to come up with a next extent size that grows 1-5 extents a month.

The performance increases you get from a single extent are arguably nonexistent with large enough extent sizes.

EXE <exe_at_exesolutions.com> wrote:

:> I was told that  if I create index with  unlimited extents option, it will
:> decrease performance of database.
:> Is it true?

: As a DBA I strongly disagree with the other two answers posted to your query.
: The difference is performance can be dramatic and I can give you an easy way
: to prove it to yourself.

: First build a table with an extent size of 1K, next 1K and populate it with
: enough records to create about 15,000 extents. Find the size of the table and
: calculate an extent size where the entire table fits into 1 to 5 extents. Now
: try to truncate the two tables. In 1 to 5 extents it will happen within a
: second. At 15,000 extents you should consider going to lunch. Same goes for
: dropping a table, and many other functions.

: Now I've heard the argument that this is only a problem for truncate and drop
: but it is also a problem for reads. Remember those 15,000 extents are not
: contiguous. This means that the DASD may need 15,000 reads whereas with a
: smaller number of larger extents a read will correspond to the
: multi_block_read. And then we get to the subject of tablespace fragmentation.

: My advice is to take the time to properly size table extents and ignore those
: that are lazy and just figure that if Oracle will allow two billion extents it
: is ok.

: Daniel A. Morgan
Received on Sun Dec 06 1998 - 16:12:34 CST