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

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

• the out-of-line CLOB segments on a 2nd spindle
• the "regular" indexes for the main table on a 3rd spindle
• the "I" table part of the interMedia index on a 4th spindle
• the "K", "R" and "N" tables and "I" index segments on a 5th 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)
JApplewhite_at_austin.isd.tenet.edu

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

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...

• DO separate tables and indexes into different tablespaces. There are lots of reasons you should do this.
• DON'T necessarily feel that you have to put the index and data tablespaces on different devices. One decision criterion is performance: don't ever put two files on the same device if the sum of their I/O-per-second rates exceeds the I/O-per-second capacity of the device. Another decision criterion is availability: don't ever put more data on a device than you can