RE: RE: should you seperate indexes from tables in seperate dataf

thanks for the dis.... :)

My experience with these types of recovery scenarios has been that the application *needs* the indexes that are being rebuilt. So user access is soooo slow that I should not have allowed the users back until all the indexes were rebuilt. The end result being that the recovery really is not "complete" until everything is done. Obviously, a better evaluation of indexes could have been performed. But my conclusion was to backup and restore everything - including indexes.

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
From: rgaffuri_at_cox.net [mailto:rgaffuri_at_cox.net] Sent: Tuesday, July 15, 2003 11:54 AM
To: Multiple recipients of list ORACLE-L Subject: Re: RE: should you seperate indexes from tables in seperate datafiles

if you keep a repository of all your indexes or have the create index statements on files in the file system.. that would solve the memory problem.

oracle designer and erwin have these capabilities.

in some cases would it be better to recovery without indexes first so the users will have some access to data, then build the indexes while the instance is online?
>
> From: "Mercadante, Thomas F" <NDATFM_at_labor.state.ny.us>
> Date: 2003/07/15 Tue AM 11:39:32 EDT
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Subject: RE: should you seperate indexes from tables in seperate datafiles
>
> I disagree with the concept of recovery not including some indexes because
> "they can be rebuilt later". To me, that's like going to a gas station
and
> only filling the tank half-way because "I can get more gas later". You
are
> saving small amounts of time up front, but will pay for it later on.
>
> I prefer to restore a totally whole database (when needed), and not having
> to rely on my memory to rebuild some indexes that we purposly chose not to
> back up. Just seems silly to me.
>
> Tom Mercadante
> Oracle Certified Professional
>
>
> -----Original Message-----
> Sent: Tuesday, July 15, 2003 11:24 AM
> To: Multiple recipients of list ORACLE-L
> datafiles?
>
>
> 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.
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Mercadante, Thomas F
> INET: NDATFM_at_labor.state.ny.us
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: <rgaffuri_at_cox.net
  INET: rgaffuri_at_cox.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may