Re: RE: should you separate indexes from tables in separate dataf

Tanel,

<quote>when rebuilding, fast full scan is done on existing index</quote>

True; except in case of index rebuilding with ONLINE option; the data blocks are read from the table instead.

Arup Nanda
----- Original Message -----
From: "Tanel Poder" <tanel.poder.003_at_mail.ee> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Tuesday, July 15, 2003 5:19 PM
Subject: Re: RE: should you separate indexes from tables in separate dataf

> Hm, index rebuilding speed shouldn't have anything to do with table data
> location, because when rebuilding, fast full scan is done on existing
index,
> data is sorted to temp ts and then copied back to index tablespace...
>
> Tanel.
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Tuesday, July 15, 2003 8:29 PM
>
>
> > R,
> > Just to avoid confusion, that wasn't what I meant by batch program
> > benchmarking.
> > Are you rebuilding indexes? I've seen some strong benefits when
> > rebuilding indexes of having the index and table on separate devices.
But
> > then that is really easy for you to test.
> >
> > Dennis Williams
> > DBA, 80%OCP, 100% DBA
> > Lifetouch, Inc.
> > dwilliams_at_lifetouch.com
> >
> >
> > -----Original Message-----
> > Sent: Tuesday, July 15, 2003 12:14 PM
> > To: Multiple recipients of list ORACLE-L
> > dataf
> >
> >
> > so there are benefits of splitting indexes and tables on different mount
> > points in an instance used for batch loads? such as a data publication
> model
> > where you ingest deltas?
> >
> > any data on this?
> > >
> > > From: DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM>
> > > Date: 2003/07/15 Tue PM 12:04:24 EDT
> > > To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> > > Subject: RE: RE: should you separate indexes from tables in separate
> dataf
> > >
> > > R,
> > > My personal theory on where this started was benchmarking. Before
the
> > > Oracle Wait Interface was developed, about the only hard-core
> information
> > > you could get on tuning came as a result of people running benchmarks.
> If
> > > you benchmark a batch program by itself that uses an index to access a
> > > table, you will get much better performance if you place the index and
> > table
> > > on separate devices.
> > > Now, as has been pointed out, in a busy multi-user system with
many
> > > users, many tables and indexes, you shouldn't just put the indexes on
> > > separate devices and call it Miller time. You should observe the
actual
> > > usage at a time when performance is critical (Cary Millsap does a nice
> job
> > > of describing this, although he focuses on finding critical
> applications),
> > > look for devices that are most heavily used, and move data files
> > accordingly
> > > to spread the load. If you sill feel you must keep the table and
indexes
> > > separate, you can criss-cross, i.e. put table A on device A and index
A
> on
> > > device B, then table B on device B and index B on device A.
> > >
> > > Dennis Williams
> > > DBA, 80%OCP, 100% DBA
> > > Lifetouch, Inc.
> > > dwilliams_at_lifetouch.com
> > >
> > >
> > > Dennis Williams
> > > DBA, 80%OCP, 100% DBA
> > > Lifetouch, Inc.
> > > dwilliams_at_lifetouch.com
> > >
> > >
> > > -----Original Message-----
> > > Sent: Tuesday, July 15, 2003 10:29 AM
> > > To: Multiple recipients of list ORACLE-L
> > > datafiles
> > >
> > >
> > > does anyoen disagree? Didnt this get started with the 'DBA Handbook'
or
> > was
> > > it a different text?
> > >
> > > >
> > > > From: "Mercadante, Thomas F" <NDATFM_at_labor.state.ny.us>
> > > > Date: 2003/07/15 Tue AM 11:10:05 EDT
> > > > To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> > > > Subject: RE: should you seperate indexes from tables in seperate
> > datafiles
> > > >
> > > > R,
> > > >
> > > > Some of it depends on the disk storage. I have always followed the
> > > > time-proven method of organizing disks and placing indexes away from
> the
> > > > tables they belong to.
> > > >
> > > > Our warehouse is using EMC external disk. What the warehouse
> architect
> > > did
> > > > was to stripe the EMC disks in such a way that all mount points (Sun
> > > system)
> > > > are spread across all the EMC disks. What this does is to spread
all
> > > files
> > > > in the database across all the EMC drives. And with 4 Gig of EMC
> cache
> > > > available, it further disproves the theory that separing indexes
from
> > data
> > > > are required. The end result, in my case, is almost like one big
RAM
> > disk
> > > -
> > > > where all disk IO is spread across all disk.
> > > >
> > > > If you do not have this arrangement, then I would still try and keep
> > > indexes
> > > > and data away from each other. But let's face it, we *never* have
> > enough
> > > > disk mount points, so we end up merging things together somewhat
> anyway.
> > > >
> > > > hope this helps.
> > > >
> > > > Tom Mercadante
> > > > Oracle Certified Professional
> > > >
> > > >
> > > > -----Original Message-----
> > > > Sent: Tuesday, July 15, 2003 10:49 AM
> > > > To: Multiple recipients of list ORACLE-L
> > > >
> > > >
> > > > There has been alot of literature stating that you will recieve
> > > performance
> > > > improvements by seperating indexes and tables across multiple I/O
> > points.
> > > >
> > > > Ie... you have a tables tablespace and an index tablespace. If you
put
> > > them
> > > > on seperate hard drives, you will have less I/O contention.
> > > >
> > > > Now Im seeing some articles stating that this is not true. That
oracle
> > > > actually accesses indexes and tables serially. Now it might be
useful
> > > > seperate indexes from tables for maintenance purposes but this wont
> > lower
> > > > I/O contention.
> > > >
> > > > Can anyone chime in on this? Curious to see where the evidence is
> > leading?
> > >
> > > >
> > > > --
> > > > 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
> > > > also send the HELP command for other information (like subscribing).
> > > > --
> > > > 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
> > > also send the HELP command for other information (like subscribing).
> > > --
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > > --
> > > Author: DENNIS WILLIAMS
> > > INET: DWILLIAMS_at_LIFETOUCH.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: <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
> > also send the HELP command for other information (like subscribing).
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: DENNIS WILLIAMS
> > INET: DWILLIAMS_at_LIFETOUCH.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: Tanel Poder
> INET: tanel.poder.003_at_mail.ee
>
> 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
Received on Tue Jul 15 2003 - 15:57:46 CDT