Re: Disadvantage of Unlimited extent?

Ng TC wrote in message <6uev7o$rg8$1_at_newton2.pacific.net.sg>...
>Hi,
>
>Can anyone tell me what is the disadvantage of setting table extents to
>unlimited?

Sure there are disadvantages:
1. Dropping tables with many many extents takes a long time. The Oracle tables that track used and free extents, UET$ and FET$, are not indexed in a way that helps the performance of drops. If your table has 2000 extents, then you're essentially scanning a table with at least 2000 rows, and you're doing that 2000 times. Consider how that changes when you go to 10,000 extents. I did it on 64,000 extents and it took almost a day to drop the table on a dedicated 12-way server.

2. Setting extents to unlimited implies that you are not controlling the extent allocation, and hence the space usage, in your database. I prefer to control that. Heck, I'd rather set maxextents to 10,000 then set it to unlimited.

My bottom line philosophy on this: if you have unlimited physical resources and unlimited time for recovery, then unlimited extents makes sense for you. If not, then it does not.

Just an opinion.

Hope this helps.
Kevin.

http://www.kevinloney.com Received on Fri Sep 25 1998 - 00:00:00 CDT