RE: should you seperate indexes from tables in seperate datafiles

From: Gogala, Mladen <MGogala_at_oxhp.com>
Date: Tue, 15 Jul 2003 10:26:43 -0400
Message-Id: <25929.337809@fatcity.com>

Actually, there is a performance benefit, but is almost negligible. Performance benefit comes from the fact that indexes are usually read by using "db_file_sequential_read", which is, as I was told by 3 or 4 wise men without any gifts, a single block read. Having vast majority of I/O being short allows, at least in theory, the controller to better optimize the incoming I/O requests, thus achieving better service times. I must say that I haven't actually seen the benefits myself but my faith is rock solid and I'll continue to separate data from indexes. Mladen Gogala
Oracle DBA
Phone:(203) 459-6855
Email:mgogala_at_oxhp.com

-----Original Message-----
From: Rachel Carmichael [mailto:wisernet100_at_yahoo.com] Sent: Tuesday, July 15, 2003 11:04 AM
To: Multiple recipients of list ORACLE-L Subject: Re: should you seperate indexes from tables in seperate datafiles?

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.

rgaffuri_at_cox.net wrote:
> 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).

Do you Yahoo!?
SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Rachel Carmichael
  INET: wisernet100_at_yahoo.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

Received on Tue Jul 15 2003 - 09:26:43 CDT