Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Separate Indexes and Data

RE: Separate Indexes and Data

From: Jared Still <jkstill_at_cybcon.com>
Date: Wed, 01 Oct 2003 10:44:35 -0800
Message-ID: <F001.005D1B44.20031001104435@fatcity.com>


Sure, you could do that.

It just doesn't seem like a good way to deal with the possibility of an index tablespace possibly having data segments in it when backing up only data segment tablespaces.

Unless you have *really* large databases with very generous restore time requirements , I don't see the  point in trying to do this anyway.

Jared

On Wed, 2003-10-01 at 05:14, Mark Leith wrote:
> Couldn't you do this with a simple:
>
> select owner, table_name
> from all_tables
> where tablespace_name = 'index_tbs';
>
> ?
>
> Or of course use IN for a list of tablespaces?
>
> Or am I missing something?
>
> -----Original Message-----
> From: ml-errors_at_fatcity.com [mailto:ml-errors_at_fatcity.com]On Behalf Of
> Jared.Still_at_radisys.com
> Sent: 30 September 2003 22:45
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Separate Indexes and Data
>
>
>
> Good question Ian. If anyone does have a different backup schedule for
> index tbs , I
> would be interested to know how they ensure that the index TBS do not
> have any
> data segments in them.
>
> Jared
>
>
>
> "MacGregor, Ian A." <ian_at_SLAC.Stanford.EDU>
> Sent by: ml-errors_at_fatcity.com
> 09/30/2003 10:34 AM
> Please respond to ORACLE-L
>
>
> To: Multiple recipients of list ORACLE-L
> <ORACLE-L_at_fatcity.com>
> cc:
> Subject: RE: Separate Indexes and Data
>
>
>
> I'd be very interested to know how many people have their index
> tablespaces on a different backup schedule from their data tablespaces. If
> so how different? What happens when a media failure occurs and you must
> restore from backup? You would need to have on hand and apply more redo
> logs to make the database current.
>
> I understand the argument proffered is separating indexes and data can
> mean that when physical corruption of the file happens to an index
> tablespace then all one needs do is to offline, drop, drop and rebuild the
> index tablespace. I admit I have not tried off-lining the tablespace first,
> but you cannot normally drop a tablespace which is being used to enforce
> referential integrity. If off-lining the tablespace first does work, I can
> see someone trying to do the rebuild with the database available and having
> duplicate records in the parent tables and records without parents in the
> child tables.
>
> On the size of the segments: The paper entitled "How To Start
> Defragmenting and Start Living" or something like that strongly advocated
> uniform extent sizes, the suggestion sizes were 128K, 4M, 128M, and 4G as I
> recall. However the paper
> Never mentioned what to do when an object that used to fit nicely into
> the 128k extent category now more properly belongs to the 4M category. If
> you move the data, large holes are left in the other tablespace, and while
> this does not impact Oracle performance, it does mean that your physical
> backups are larger than necessary. I am in the process of migrating from
> uniform to autoallocated extents. This means extents of different sizes
> share the same tablespace. The extent sizes being multiples of each other.
> This removes the argument about not having indexes and data in the same
> tablespaces due to their different sizes.
>
> Ian MacGregor
> Stanford Linear Accelerator Center
> ian_at_slac.stanford.edu
>
> -----Original Message-----
> Sent: Monday, September 29, 2003 8:10 AM
> To: Multiple recipients of list ORACLE-L
>
>
> Thomas,
>
> 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.
>
> * Index segments are usually smaller than their corresponding heap
> segments. Using separate tablespaces allows you to use a smaller extent size
> to conserve disk storage capacity.
>
> I don't think I ever wrote that you need to put indexes and their
> corresponding tables/clusters on separate disks, but you do need to be
> *able* to do that if your I/O rates indicate that you should.
>
> For the original OFA Standard definition, please see section 3 of the
> document called "The OFA Standard--Oracle for Open Systems," and section 5
> of "Configuring Oracle Server for VLDB," both available for free at
> www.hotsos.com.
>
>
> 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-----
> Thomas Day
> Sent: Monday, September 29, 2003 9:05 AM
> To: Multiple recipients of list ORACLE-L
>
>
> My struggle is not with the directory layout OFA.
>
> It is with the "mythical" OFA that every DBA that I have talked to knows
> all about. Where ORACLE says that if you are a good and competent DBA you
> will separate your table data and your index data into two separate
> tablespaces so that one disk head can be reading index entries while another
> disk head is reading the table data. You've never run into that?
>
>
>
>
>
> Tim Gorman <tim
>
> @sagelogix.com> To: Multiple
> recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Sent by: cc:
>
> ml-errors Subject: Re: BAARF
>
>
>
>
>
> 09/28/2003 09:44
>
> PM
>
> Please respond
>
> to ORACLE-L
>
>
>
>
>
>
>
>
>
> Thomas,
>
> Please pardon me, but you are off-target in your criticisms of OFA.
>
> It has never advocated separating tables from indexes for performance
> purposes. Ironically, your email starts to touch on the real reason for
> separating them (i.e. different types of I/O, different recovery
> requirements, etc). Tables and indexes do belong in different tablespaces,
> but not for reasons of performance.
>
> Cary first designed and implemented OFA in the early 90s and formalized it
> into a paper in 1995. Quite frankly, it is a brilliant set of rules of how
> Oracle-based systems should be structured, and a breath of fresh air from
> the simplistic way that Oracle installers laid things out at the time. It
> took several years for Oracle Development to see the light and become
> OFA-compliant, and not a moment too soon either. Just imagine if everything
> were still installed into a single directory tree under ORACLE_HOME? All of
> things you mention here have nothing to do with OFA.
>
> Please read the paper.
>
> Hope this helps...
>
> -Tim
>
> P.S. By the way, multiple block sizes are not intended for
> performance
> optimization; they merely enable transportable tablespaces between
> databases with different block sizes.
>
>
> on 9/25/03 11:04 AM, Thomas Day at tday6_at_csc.com wrote:
>
> >
> > I would love to have a definitive site that I could send all RAID-F
> > advocates to where it would be laid out clearly, unambiguously, and
> > definitively what storage types should be used for what purpose.
> >
> > Redo logs on RAID 0 with Oracle duplexing (y/n)?
> > Rollback (or undo) ditto?
> > Write intensive tablespaces on RAID 1+0 (or should that be 0+1)? Read
> > intensive tablespaces on RAID ? (I guess 5 is OK since it's
> cheaper
> > than 1+0 and you won't have the write penalty)
> >
> > While we're at it could we blow up the OFA myth? Since you're
> tablespaces
> > are on datafiles that are on logical volumns that are on physical
> devices
> > which may contain one or many actual disks, does it really make sense
> to
> > worry (from a performance standpoint) about separating tables and
> indexes
> > into different tablespaces?
> >
> > We have killed the "everything in one extent" myth haven't we?
> Everybody's
> > comfortable with tables that have 100's of extents?
> >
> > And while we're at it, could we include the Oracle 9 multiple
> blocksizes
> > and how to use them. The best that I've seen is indexes in big
> blocks,
> > tables in small blocks --- uh, oh, time to separate tables and
> indexes.
> >
> > Maybe we will never get rid of the OFA myth.
> >
> > Just venting.
> >
> > Tired of arguing in front of management with Oracle certified DBAs
> that
> > RAID 5 is not good, OFA is unnecessary, and uniform extents is the
> only
> way
> > to go. Looking for a big stick to catch their attention with.
> >
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Tim Gorman
> INET: tim_at_sagelogix.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: Thomas Day
> INET: tday6_at_csc.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).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: MacGregor, Ian A.
> INET: ian_at_SLAC.Stanford.EDU
>
> 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).
>
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.521 / Virus Database: 319 - Release Date: 23/09/2003

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jared Still
  INET: jkstill_at_cybcon.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).
Received on Wed Oct 01 2003 - 13:44:35 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US