Re: index fragmentation

True, and your example of 160,000 extents in a table would be more than a slight overhead. On the other hand if you have a table of 10,000,000 rows, and accidentally set the extent size so that one extent holds only 5,000 rows so that you get 2,000 extents allocated during a bulk load, what fraction of the total time is likely to be from the 10,000,000 inserts, and what fraction from the 2,000 sets of recursive insert/update ?

I have to say that my personal target is to limit the extents per segment to about 40, but that's a very flexible target. Partly based on the fact that if my calculations are out I'm not going to waste more than about 2.5% of the space allocated. FET$ and the impact on smon used to be a consideration before locally managed tablespaces appeared.

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Brian Peasland wrote in message <39CA1CFB.A54A56B6_at_edcmail.cr.usgs.gov>...


>> But surely the only time you access the uet$ is

>> when you run queries against dba_extents and

>> dba_segments ?

>

>In a sense, true. But what about the recursive SQL when you are loading

>data into the table? If the table needs a new extent, then both uet$ and

>fet$ need to be updated. And this happens when no one has queried

>dba_extents or dba_segments.

>

>HTH,

>Brian

>

>

>

>--

>========================================

>Brian Peasland

>Raytheons Systems at

>  USGS EROS Data Center

>These opinions are my own and do not

>necessarily reflect the opinions of my

>company!

>========================================