Re: Does the number of extents affect the performance?

Roald van Geleuken wrote:
>
> Seem to have stirred up an ants nest, or so it seems. Lots of explanations,
> but how to cope with the problem? It seems to me the fastest way of
> resolving ALL problems is exporting/importing, but I'm open to suggestions.
>

You certainly have,as you can see. The fastest way of coping with the problem is to stop it from happening: Tune the allocation of NEXT so the things don't frag up too much and forget about defragging a 3 extent table or index, it's not worth the effort in the VAST majority of cases... Of course there are abnormals, but this simple technique covers about 99.99999% of all the frag problems out there. The remainder is easy: shove the problem areas into their own tablespace, so you always can defrag without space problems caused by other objects. How to imp/exp by tablespace? Ahh grasshopper, read the EXP view.sql files delivered with ORACLE and THINK! <G>

> What really bugs me is that Oracle doesn't come up with any solutions
> integrated in it's products. It's very easy to say 'O, do an exp/imp, and
> it's ok', but I've had problems with that too (large table, fragmented free
> space). There must be solutions which can be built into the system, without
> the need for user intervention. Anyone for setting up a brainstorming
> session?
>

There is, but I doubt ORACLE will do it in the near future just to cope with 0.00001% of problems. Old mainframe databases such as DMS1100 from UNIVAC coped with this problem (and others) beautifully. One day ORACLE code designers may wake up and study old database coding techniques, after all a disk is a disk, is a disk, who cares if the OS is MVS or NT?...

-- 
Nuno Souto
nsouto_at_FOSPAMacay.com.au