| 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: How can i change the MAXEXTENTS ??
A copy of this was sent to "James C. Specht, Jr." <jspecht_at_primenet.com>
(if that email address didn't require changing)
On Thu, 06 Aug 1998 18:42:11 -0400, you wrote:
>Thomas Kyte wrote:
>>
>> A copy of this was sent to ashesh48_at_my-dejanews.com
>> (if that email address didn't require changing)
>> On Thu, 06 Aug 1998 17:17:33 GMT, you wrote:
>>
>> >Hi All, I got into the problem with the MAXEXTENTS of the table. It has
>>
[snip]
>> What version of Oracle? Try:
>>
>> SQL> alter table <TNAME> storage ( maxextents unlimited );
>>
>> or use some other number in place of unlimited.
>>
>> As long as your extent size is a multiple of your db_file_multiblock_read_count,
>> having 1, 100 or 1000 (or more) extents is pretty much the same.
>>
>>
[snip]
>
>I have been told that you should keep you extents at 1. The more
>extents you have the slower your responses get.
Its a MYTH.
Consider an indexed read. We do pure scattered reads on index blocks (even if they were in one extent we read block1, then read block 1000, then read block 500 -- its not relevant what extent they were in, we are reading in a scattered fasion). That gets us a rowid of the row we want (file/block/slot on block generally speaking) and we do IO on that block. Again, the number of extents has ZERO impact on this operation.
Consider a full scan. We read db_file_multiblock_read_count blocks at a time. These blocks are contigous. As long as our extents are even multiples of db_file_multiblock_read_count, we read contigous blocks. When we hit the end of one extent, we just go to the next. number of extents doesn't impact this operation either.
the only operations that I can think of that even might be impacted would be a DROP command. Dropping a table with 1000 extents might take longer then dropping a table with 1 extent (more rows in the data dictionary to delete and log).
>It is even discussed in
>the Oracle Admin Guide.
Once upon a time, 2k blocks ruled and the max extents was 121. If you hit that -- bamm, table cannot get any bigger. You have to unload and reload.
This is not an issue anymore.
>Am I wasting my weekends resizing tables to
>keep myself at 1 extent?
IMO - absolutely yes.
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 Aug 07 1998 - 09:32:03 CDT
 |
 |