Re: Big 7.1 open items

Tom Lane wrote:
>
> It gets a little trickier if you want to be able to split
> multi-gig tables across several tablespaces, though, since
> you couldn't just append ".N" to the base table path in that
> scenario.
>
> I'd be interested to know what sort of facilities Oracle
> provides for managing huge tables...

Oracle tablespaces are a collection of 1...n preallocated
files. Each table then is bound to a tablespace and
allocates extents (chunks) from those files.

There are some per table attributes that control the extent
sizes with default values coming from the tablespace. The
initial extent size, the nextextent and the pctincrease.
There is a hardcoded limit for the number of extents a table
can have at all. In Oracle7 it was 512 (or somewhat below -
don't recall correct). Maybe that's gone with Oracle8, don't
know.

This storage concept has IMHO a couple of advatages over
ours.

The tablespace files are preallocated, so there will
never be a change in block allocation during runtime and
that's the base for fdatasync() beeing sufficient at
syncpoints. All what might be inaccurate after a crash is
the last modified time in the inode, and that's totally
irrelevant for Oracle. The fsck will never fail, and
anything is up to Oracle's recovery.

The number of total tablespace files is limited to a
value that ensures, that the backends can keep them all
open all the time. It's hard to exceed that limit. A
typical SAP installation with more than 20,000
tables/indices doesn't need more than 30 or 40 of them.

It is perfectly prepared for raw devices, since a
tablespace in a raw device installation is simply an area
of blocks on a disk.

There are also disadvantages.

You can run out of space even if there are plenty GB's
free on your disks. You have to create tablespaces
explicitly.

If you've choosen inadequate extent size parameters, you
end up with high fragmented tables (slowing down) or get
stuck with running against maxextents, where only a reorg
(export/import) helps.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #