Re: Big 7.1 open items - Mailing list pgsql-hackers

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
numberof extents a  table   can  have at all.  In Oracle7 it was 512 (or somewhat below -   don't recall correct).
Maybethat'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
runtimeand       that's the base  for  fdatasync()  beeing  sufficient  at       syncpoints. All what might be
inaccurateafter 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
themall       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
simplyan 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 #