Re: Status of tablespaces

> 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.

> > 2) extents in table spaces - were invaluable when creating cyclical
> >    data tables.  For example: in a table space, allocate extents 8
> >    extents (each extent big enough for the day's worth of data - an
> >    extent being pre-allocated disk space), then with a stored
> >    procedure/rule, each day had its data inserted into an extent.  On
> >    the 8th day, drop the oldest extent, reformat the extent, then
> >    re-add the extent to the table space: instant daily truncation
> >    without having to do a delete on 200M rows.
>
> Cool...but what does that do to the indexes and foreign key relationships?

This was some magic as far as I could tell, but cheers to pulling from
the far reaches of my memory.

In the extents model of the universe, let's say you have one extent
for an index, and 8 for the various days of the week (+1 for
dropping/formatting).  I don't know where the mapping of data to
extent resides, but I'd imagine that if the index has data being
stored on various extents, the index would be aware of the ranges of
data that are stored on what extents.

Off the top of my head, I can only imagine that they'd do something
similar to the following.  Each extent had its own index with its own
respective range.  The ranges of the indexes were stored in a system
catalog such that when an index lookup is performed, it only queries
the index on the appropriate extent that the database is querying
over.  Then when an extent is dropped, all you have to do is update
the system catalog and dd if=/dev/zero to your hearts content.

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.  :)

> > Hrm, now that I think about it, I suppose this could be done with
> > tables instead of extents and a view on top of them...
> > ::ponders::
>
> It can make a lot of queries work not so well, because the optimizer
> won't do certain optimizations across a UNION SELECT that it will do
> on a single table. But you could always just re-code your queries to
> work directly against the appropriate tables. You'd probably want to
> build some little query generator to do this sort of thing.

Very very true.  Selecting data across time boundary extents would be
super inefficient, but easier than nothing.

--
Sean Chittenden