Re: How to "Defragment" Tablespaces ?

John Jones wrote:
>
> I have to jump into this discussion.
So do I...

> If your SQL
> statements are doing FULL TABLE SCANS, then you need to do it. If you have
> a tuned database that correctly uses indexes, then it is really not a
> problem.

So you say that FTS are less effective? Why would the CBO switch to FTS if
it has figured out that over 5% (!) of the table has to be returned? I have used 15-25% as a rule of thumb, but CBO switches even earlier... Nowadays, I like to demonstrate to (SQL) developers that having indexes for access
paths can actually be bad. It always makes me chuckle to see the reactions.

How would you explain CBO's behaviour?

> Brian Peasland <peasland_at_usgs.gov> wrote in message
> news:3ACDCEED.A7621F58_at_usgs.gov...
> >
> > Now the onus is on you... In the absence of striping and mirroring, how
> > do you propose to improve your performance and scalabilty while placing
> > indexes and tables in the same tablespace?
> >

Here's a thought for you, Brian:

Why were clusters invented? I was under the impression that that could speed up
operations by putting frequently used tables and their indexes into one, physical
place. O/S's being what they are, the read operation will fetch some 64kB - good chance
everything you need is in that (disk) buffer: indexes, tables, the lot. 1 read, have all.

Correct me if I'm wrong

-- 
Gtrz,

Frank van Bortel