Re: index fragmentation

Roman Starek wrote:
>
> >
> > but of course you were also going to quote that number of extents
> > typically will have absolutely no impact on performance...
> >
>
> I dont think so. A have some reasons for my opinion.
>
> 1) Big number of extents is problem for database. The more
> extents the bigger dictionary tables are. This brings
> big performance penalty specially for development
> enviroments. I know what is this about. Our developing
> enviroment contains 98000 objects in it.

If you're using locally managed tablespaces, then you won't be hurting your data dictionary - they won't be there

>
> 2) Big number of extents for index is not good for fast
> index scan.
>

As long as your extents are a multiple of the the maxium that the OS can return as a single unit of I/O, then there will not be a discernible difference. For example if your max unit is (say) 128k, then
1000 extents of 1m will be no slower than 10 extents of 100m - you'll still
end up with 8000 (1000m / 128k) trips to the disk.

> 3) Big number of extents usually mean that extents are
> distributed across all tablespace. This means that
> using index requires more work to be done through
> discs.

This would be a legitimate concern in a single user environment, but typically you're going to have many users all going for index blocks all over
the place, so the likelihood of ever getting contiguous access to a single
disk is very low.

>
> Generally I think number of extents is not problem for small
> number of extents < 10 maybe 20.

Whilst I'm against having ridiculous numbers of extents simply in terms of manageability, I've yet to see a strong argument for choosing a number
such as "10"

>
> Starous

Comments inline.

Cheers
Connor

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk

We are born naked, wet and hungry...then things get worse