Re: Status of tablespaces

On Tue, 28 Jan 2003, Sean Chittenden wrote:

> > Note, too, that one of the reasons pre-allocating extents in Oracle
> > gives such an advantage is because its block allocation system is
> > not so intelligent as modern (well, if you call Berkeley FFS
> > "modern" :-)) filesystems.
>
> Do you think this would do better with UFS2?  I'm confused if you're
> implying that FFS is better than _____ or if you think that FFS leaves
> a lot to be desired in this department.

I think that FFS does a pretty good job of this. Certainly better than
Oracle does (at least as of my last look at Oracle block allocation, a
couple of years ago). My comment was related to the fact that FFS is far
from new technology.

> Anyway, the point of my original post is that being able to do stuff
> like that and have it return near instantly is wonderful when dealing
> with very large quantities of data.  Seriously, try deleting 100M rows
> without this.  With this, it'll happen in less than a second.  :)

Without even getting into the index side of things (because I don't want
to get into a big long explanation here), there's no way it could delete
all that data that quickly if another table had foreign keys referencing
it. You have to check *every* other table for records matching the ones
you're deleting, which at the very least means reading all of the ones
you're deleting and doing index searches or scans on the other tables.

Basically, this looks like PostgreSQL's TRUNCATE, which also ignores the
FK problems (though it can deal with the indexes, since you just delete
all of them as well).

Because of this, the technique is usable only in a limited number of
situations.

cjs
--
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC