Newbie question: Should a table/index ideally always be in a single extent ?

I will shortly try to tune my first database, and I have some questions about the extremely common advise to size a table or index so that it fits in one extent for the foreseable future.

As far as I understand, the arguments for this advise are :

1. No time is spent to dynamically extend the segment.
2. The data blocks are nearer when they are all in the same extent than when they are in different extents scattered in the tablespace (and its associated datafile). Therefore, assuming the datafile is contiguously stored on the hard disk, head movement is minimized, especially for full table scans.

Is this correct ? Are there other arguments I am not aware of ?

However, I have some concerns with such a sizing :

1. I'll have to support many different sites, and I try *not* to have to exactly predict the future growth of the database. I would like the same setup to work for small sites with not much data and disk space *and* for bigger sites.
2. If the initial prediction is somewhat wrong, the segment will get multiple extents and will need occasional but time and human resources consuming reorganizations.

Therefore, I though of the following :

1. Put each of the heavily modified tables (there are ~15 of them) in its own tablespace. Assign of course one datafile to each tablespace.
2. Set for each table INITIAL and NEXT to the same value, such that big sites will see a dynamic extension of the table, its tablespace, and its datafile every 4 days and small sites will extend every month. PCTINCREASE is 0 for both the table and the tablespace.
3. Regularly defragment the hard drive so that datafiles are contiguous.

It seems to me that this setup meets argument 1. Extension occurs rarely.

It meets argument 2. A given tablespace contains all blocks and only blocks of a given table, is breaked downed in equal-sized extents, and has a near-optimal size. The corresponding datafile is contiguous on disk. There is therefore no more fragmentation than with the more traditionnal setup of having the 15 tables stored each in a unique extent in a big tablespace.

It meets my concerns : Tablespaces automatically adapt to the growth. There is no need for a reorganization at the Oracle level, as it occurs at the OS (NT) level by defragmenting the datafiles.

Am I missing something ? Do you see there problems with this setup ? Should I activate CKPT - whose responsability I do not understand well right now - as I'll have many datafiles ?

I realize this setup is not applicable as it is to indexes.

Thanks in advance for your comments.

• Raoul