RE: BAARF

Niall,

I think you've specified the right test.

However, whether to separate indexes from data is an easier argument. All it takes is one of potentially dozens of reasons, and isolating becomes the right idea.

Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:

- Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney
- Hotsos Symposium 2004: March 7-10 Dallas
- Visit www.hotsos.com for schedule details...

-----Original Message-----
Niall Litchfield
Sent: Monday, September 29, 2003 4:35 PM To: Multiple recipients of list ORACLE-L

Cary writes

> It *is* a good idea to separate index data from heap data
> into different tablespaces. But the reason isn't solely to
> eliminate I/O competition. Even if I/O competition isn't an
> issue for you (and the OFA Standard doesn't say that it will
> be), then it's *still* a good idea to separate your index
> data from your heap data, for reasons including:
>
> * Index segments have different backup and recovery
> requirements than their corresponding heap segments. For
> example, as Peter mentioned, if you have an index block
> corruption event, then it's convenient to just offline, kill,
> and rebuild an index tablespace. If the indexes and data are
> mixed up in a single tablespace, this is not an option. Another
> example: If you construct your backup schedule to make media
> recovery time a constant, then you probably don't need to
> back up your indexes on the same schedule as you back up your
> heaps. But unless they're in different tablespaces, this
> isn't an option either.

Hmmm maybe I'm going to start having to rethink some stuff, when you and Howard agree and I disagree it seems likely I'm being dense. My concern goes

Indexes are largely built for one of two reasons

1. to make performance acceptable.
2. to enforce constraints.

In a media recovery situation, recovering but with unacceptable performance or locking issues probably doesn't really constitute recovery. Now If it can be shown that trashing the index tablespace and rebuilding is generally faster than restoring datafiles and applying logs I might be more convinced but at the moment I'm not so sure. So is this garbage Or not.?

Niall

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Niall Litchfield
  INET: niall.litchfield_at_dial.pipex.com

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: Cary Millsap
  INET: cary.millsap_at_hotsos.com

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).