| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: separate tablespaces for tables and indexes
I think in these days, with RAID and disk arrays, most of the old-time
arguments for creating separate tablespaces are invalid. As several =
others
have stated, the main reason was to reduce 'DISK' contention.
Think about the old days for a bit. With tablespaces on different disks, you increase the number of read/write heads available for getting data from the disk. If everything was on one disk, whether one big tablespace or several smaller ones, there was still only one head available to get the data. If it was busy reading blocks from disk, it didn't matter how many tablespaces/data files there were, the other processes still had to wait for that one disk head to become available again to move somewhere else.
However, with that said, there is now different reason to 'think' about
having different tablespaces/data files. It won't be the same for
everbody, as everybody's data is different, but analyzing your data (and
the application's use of it) may give you insights on reasons why to =
have
separate tablespaces. Different tablespaces allows having different =
block
sizes, different logging options, etc. A separate tablespace for RAW or
LOB data would make sense, having the largest possible block size. =
Having
a separate tablespace with logging for static lookup tables and a =
smaller
block size may make sense. Conversely, it may also make sense to have a
separate tablespace for the indexes and a large block size, depending on
how often the indexes are used and if they generally all of the =
requested
data, so tables wouldn't be accessed.
It all depends on the particular database, the data, the design, and =
it's
use.
~ Think on a grand scale, start to implement on a small scale ~
-----Original Message-----
From: oracle-l-bounce_at_freelists.org =
[mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of niall.litchfield_at_gmail.com
Sent: Tuesday, December 14, 2004 6:06 AM
To: gogala_at_sbcglobal.net
Cc: richard.foote_at_bigpond.com; oracle-l_at_freelists.org
Subject: Re: separate tablespaces for tables and indexes
On Tue, 14 Dec 2004 12:41:38 +0000, Mladen Gogala <gogala_at_sbcglobal.net>
wrote:
> Richard, I beg to differ.
>=20
>=20
I'm not going to put words into Richard's mouth (don't know enough David
Bowie lyrics for that), but I don't think that your comparison is quite
correct. If you are going to split at all, you are going to have > 1 =
disk
(or volume), otherwise the effort is pointless. Consider the (simplest)
case where you have 2 disks for data and indexes. In the case of =
creating
just one tablespace one would still utilize 2 datafiles and all the IO
would be spread across both disks. If one decided to create a data and =
an
index tablespace then one would simplistically create one datafile for
each and allocate them to different disks. Then the IO would still be
spread across disks, but it would no longer be spread approximately =
evenly
because of the different caching characteristics that Richard mentions,
instead the single data disk would suffer more contention. This is of
course solvable by the expedient of using twice as many datafiles (2 for
each
tablespace) to achieve the same IO distribution as the original =
non-split
solution. In the more general case with n disks and m tablespaces one
needs n*m datafiles to achieve the same split as n datafiles would for a
single tablespace.
all of the above assumes random IO to the files. This is a bad =
assumption
- index access in my experience is quite liable to suffer from local
hotspots.
--=20
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Tue Dec 14 2004 - 07:34:15 CST
 |
 |