BE AWARE - FRAGMENTATION ON PARTITIONED TABLES WITH DIFFERENT INITIAL and NEXT SIZES
I agree w/ what gpsingh said regarding storage parameters. The generally accepted practice is to define storage parameters at the tablespace level as opposed to defining them at the table level.
Also, I generally like to keep my extent sizes the same (ie the same value for both initial and next). As U2 once said, there's been a lot of talk about the effects of multiple extents, maybe, maybe too much talk. (well, it was actually the next song but I digress...) In general, Oracle has said that you can easily handle 4096 extents within a tablespace. I don't like getting near that but obviously you can easily keep extent sizes the same. This keeps the tablespace from becoming fragmented and makes it easier to manage. Initial is allocated per partition so if your table has 16 initial partitions then you will have 16 extents that are 49152 in size. any additional extents will be 12400. Say you drop a partition with a single extent...this frees up 49152K in the tablespace. You then have 4 extends on your other partitions that fill this space 3*12400= 37200 leaving 11952 of free space. The next extent will not be able to fit in this space of course and so will go and allocate another extent, if it can, elsewhere in the tablespace. This space will never be used again unless the extent next to it gets freed up at some point in time and the space is coalesced. You can really run into problems as you should be able to see by this point! If you had set your initial and next extents at 12400 then you might have had 5 extents (again, no big deal) but when you deallocated the extent, any new extents from the other partitions would have easily have aquired this space instead of extending into a new partition.
On another note, be aware of how you are sizing your data files as well. First of all, your file size should be a multiple of your extent size and remember also to allow for the data file header which is 1 . For example, if you are setting initial and next to 10240 and you want to allow for 100 extents, the initial datafile size should be 10240*10+db_block_size. If you don't allow for this you'll waste space because the 10th extent wouln't be able to allocate because you only have 10240- available. There is a great Oracle whitepaper entitled "How to Stop Defragmenting and Begin Living" that describes Oracle's SAFE (simple algorythm for extents) which goes into detail about extents, fragmentation and Oracle performance in regard to extents. I don't agree w/ allowing that many extents but certainly it's not a huge concern to have a (reasonably large) number of extents.
Anyone else care to comment?
[Edited by wjramsey on 03-20-2001 at 07:14 PM]