Re: Unlimited extents

> 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 Thu Nov 26 1998 - 12:19:11 CST