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@Yahoo.com #