[QUOTE]Originally posted by nagarjuna
It's no big deal to have large no. of extents as long as your queries use indexes. It DOES matter when table goes for FTS (full table scan). When a query goes for FTS, Oracle has to check for next block which may not be continous in the case of many smaller sized extents, but there are chances of finding the continous blocks in the case of few big sized extents.
-nagarjuna
About FTS and # of extents:
Someboby remember problems with FAT file system in dos?
If a file has many little extents is this situation good or bad for
perfomance of file access. I don't thing so.
Disk I/O operation has a couple of steps:
positioning of heads.
finding of sector
read data/write data
...
if we have many extents in some table, and these extents place
on different tracks of HDD
we increase number of mechanical movements of heads
but this operation dramaticaly slower then usual read data from
HDD. This is one of the problem with number of extents.
Doesn't matter we use raids or hdd cache or not.
this is degradation of perfomance in any way.