| 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: should you seperate indexes from tables in seperate datafiles?
This is a multi-part message in MIME format.
--------------E00855C91A8911BB7C389D0E Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit
I'll agree with Rachel's methodology and add another consideration. Look at separating constraint indexes (primary keys, unique, perhaps even foreign keys) from performance indexes. If you find resource constraints on backups (time/disk), you can safely ignore the performance indexes. The recovery impact is that the application/sql may run slower without the indexes, but the data and constraints are intact. AFter the system is up and running, you can rebuild the indexes.
Rachel Carmichael wrote:
>
> I separate indexes and tables into different tablespaces for
> maintenance purposes, not for performance, as there really is no
> performance benefit if you are on a system with multiple users. At any
> given time, many users will be doing queries that read the indexes and
> many users will be doing queries that read the tables. Besides, I don't
> get to control how my disks are set up (part of that "now now little
> girl, don't you worry your pretty little head about how the disks are
> set up, you just leave that sort of stuff to us big <male> data center
> operations people" crap I get)
>
> Maintenance: if I lose an index tablespace datafile, I can just
> offline/drop the tablespace and recreate it and the indexes within it
> rather than do recovery. My indexes and my tables tend to have
> different extent size requirements (most of my indexes are NOT
> comprised of all columns in the table) so I separate them for extent
> size purposes as well.
--------------E00855C91A8911BB7C389D0E
Content-Type: text/x-vcard; charset=us-ascii;
name="daniel.fink.vcf"
Content-Transfer-Encoding: 7bit Content-Description: Card for Daniel Fink Content-Disposition: attachment;
begin:vcard
n:Fink;Daniel
tel;cell:303.808.3282
tel;work:303.272.3225
x-mozilla-html:TRUE
adr:;;;;;;
version:2.1
email;internet:daniel.fink_at_sun.com
title:DB Services Lead
x-mozilla-cpt:;-4832
fn:Daniel Fink
end:vcard
Received on Tue Jul 15 2003 - 09:26:18 CDT
 |
 |