Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: should you seperate indexes from tables in seperate datafiles?

RE: should you seperate indexes from tables in seperate datafiles?

From: <>
Date: Tue, 15 Jul 2003 14:29:11 -0500
Message-Id: <>

Yes, complicated with as simple a combination as tables and their indexes. Consider even more variables introduced by using LOBs and interMedia Text.

In a past job, I used to make myself crazy analyzing and placing all the segments involved with interMedia-indexed CLOBs. Here's what I came up with:
- the main table on its own spindle

This was all on 22 JBODs (36GB each) under 8.1.7 / Win2k. The 13 million CLOB documents that were online and indexed made the database about 200GB. It was beautiful (to this nerd) to watch the disk I/O on the Win2k Performance Monitor as different queries would hit different parts of those segments at different times. Though it probably wasn't, the PerfMon graphs looked like there was a lot of parallel work going on across all those spindles.

That database was used by only one or a very vew users at a time, doing similar queries, so I think performance benefitted from that segment separation. I would definitely agree, though, that with dozens or hundreds of concurrent users, I would have had to closely monitor tablespace / datafile I/O and shuffle datafiles around to better distribute load across available spindles. That would especially hold true if user activity coincided with the nightly loading and reindexing of up to 250,000 new CLOBs.

No particular point here, but I/O distribution was a consideration for segment-to-tablespace mapping, even though an equal (or greater) consideration was differing extent sizes - 1MB for tables and "regular" indexes 100MB for the CLOB segments. ...all on LMTs, of course.

Here at AISD, almost all our 8.1.7 databases live on the 14 RAID-5 LUNs presented by our HP XP-512 array (that's just something we've gotta live with, though I'm definitely a BAARFist). Most of the DBs are on 3 HP-UX boxes, but one is on Win2k. I've not yet had a chance to map all the pieces of all the DBs to see which pieces share which physical drives, but I/O hasn't seemed to hurt DB performance. Bad performance continues to be more than adequately addressed by the horribly-inefficient SQL produced by our two 3rd Party apps (Student Info. System and Financials System). I guess that goes to show that segment distribution - even on RAID-5 - is an insignificant factor when compared to bad SQL (producing tens of millions of unnecessary logical I/Os, in this case).

Jack C. Applewhite
Database Administrator
Austin Independent School District
Austin, Texas
512.414.9715 (wk)
512.935.5929 (pager)

                      "Cary Millsap"                                                                                                    
                      <cary.millsap_at_hot        To:       Multiple recipients of list ORACLE-L <>                    
            >                 cc:                                                                                      
                      Sent by:                 Subject:  RE: should you seperate indexes from tables in seperate datafiles?             
                      07/15/2003 12:19                                                                                                  
                      Please respond to                                                                                                 

The thing that occurred to me a few years ago (as a result of a test designed by Craig Shallahamer) is that "what disks do" gets very, very complicated when you add users. On any system busy enough to have a performance problem, the odds are usually slim that a disk is just "sitting there" waiting for your next I/O call. On a busy system, someone else's I/O call is almost always going to intercede between two of *your* I/O calls.

As has been said many times, many ways...

Received on Tue Jul 15 2003 - 14:29:11 CDT

Original text of this message