Re: Performance and number of extents

Nuno Souto wrote:
>
> Hi. For a while now there has been this folk tale here in Australia on
> how a DBA needs to maintain all objects at a single extent otherwise
> performance drops DRAMATICALLY.
>
>

You are absolutely right: this is a piece of hokum that has been around as long as Oracle and it just isn't true.

If you thnk about it you realise that multiple extents in a data segment can make no difference to an indexed read, which "hops" randomly from page to page, while a serial read of a data *or* of an index segment merely requires a few hundred extra instructions at the end of each extent. You might notice the performance hit if you had less than a dozen rows per *extent*, but not otherwise.

What has happened, of course, is that someone took the trouble to re-organise their extents by an export and re-import and - hey presto! - performance improved! Well of course it did: the indexes were re-organised and the data compressed into fewer pages.

This myth has been around so long, however, and it is *such* an easy way for unthinking people to fool themselves into thinking that they are doing something useful, that I doubt if it will go away. :-(

-- 
Chrysalis

FABRICATI DIEM, PVNC
('To Protect and to Serve')
Terry Pratchett : "Guards Guards"