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 ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, July 15, 2003 5:19 PM > 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" <[EMAIL PROTECTED]> > 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. > > [EMAIL PROTECTED] > > > > > > -----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 <[EMAIL PROTECTED]> > > > Date: 2003/07/15 Tue PM 12:04:24 EDT > > > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > > > 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. > > > [EMAIL PROTECTED] > > > > > > > > > Dennis Williams > > > DBA, 80%OCP, 100% DBA > > > Lifetouch, Inc. > > > [EMAIL PROTECTED] > > > > > > > > > -----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" <[EMAIL PROTECTED]> > > > > Date: 2003/07/15 Tue AM 11:10:05 EDT > > > > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > > > > 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: <[EMAIL PROTECTED] > > > > INET: [EMAIL PROTECTED] > > > > > > > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > > > > > > > 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: [EMAIL PROTECTED] (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: <[EMAIL PROTECTED] > > > INET: [EMAIL PROTECTED] > > > > > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > > > > > 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: [EMAIL PROTECTED] (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: <[EMAIL PROTECTED] > > INET: [EMAIL PROTECTED] > > > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: Arup Nanda INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).