Why PCTINCREASE?

A little question:

If PCTINCREASE other than zero potentially causes fragmentation and used space growth, what is the use of that parameter, why isn't it zero by default?

I mean, what's the real advantage of PCTINCREASE parameter? Could you tell an illustrative scenario?



Adriano.

Why PCTINCREASE defaults to 50? IMHO the only reason are lazy/unknowledged DBAs and developers.
Supose the tablespace default INITIAL parameter is 10K and default PCTINCREASE would be 0. When a new table (or index) is created without specifying storage, when the table grows to 100M (a rather tiny table nowadays) it will be consisted of 10,000 extents! This bares huge consequences regarding the maintaining of database dictionary and (in such an extreme case) also on performance when scaning such a table. If you apply PCTINCREASE=50, you will get 100M table in only about 20 extents. If I would have to choose between a table of 10.000 equaly sized extents and a same table of 20 differently sized extents I would choose the second option!

However I agree that PCTINCREASE should alway be 0, but also INITIAL=NEXT size should be set correctly at the same time.

Hi

The number of extents is very important? We want to use locally managed tablespace but dont know what size we should use since we have relatively big tables and very small tables and we want to use the uniform size extent option in locally managed tablespaces, will this have a huge impact on big tables if we use small extent sizes?

Well, the localy managed tablespaces are totaly different beast. The number of extents per segment is almost of no importance here, as long as the size of extents conforms with the DB_BLOCK_SIZE and DB_FILE_MULTIBLOCK_READ_COUNT. If your extent size conforms with "SAFE" algorithm you'll be just fine (SAFE method has been mentioned quite frequently in this forum, so if you need more detail you should be able to find a URL for that paper right here in this forum).