The Number of Extents for a table and performance...

Hi there, I have a question, does the number of extents a table has affect performance. We have been told if the number of extents is over 8 then performance starts to suffer. Is this true? Also the database is in a RAID array, so the data is stripped across 8 disks that there is no continous partition that the table is placed. It would seem the number of extents in this situation is little less important. Any comments or suggestions please, Thanks Pat Militzer pmilitzer@metromls.com

Pat Militzer wrote: > > Hi there, > > I have a question, does the number of extents a table has affect > performance. We have been told if the number of extents is over 8 > then performance starts to suffer. Is this true? No, assuming you are using IDS 7/8/9, if you are still using OL5.xx then the answer is yes! The 8 was a magic number in OL5.xx because the engine kept the locations of 8 extents per tablespace in memory. In IDS 7 this is not longer the case and the data structures are completely different. Having MANY extents in a table MAY still slow the performance of SOME queries but not all and it is no longer clear how many extents are too many. Probably more than 20 or 30 extents per fragment is too many but it still depends. If you are performing sequential scans often (see seqscans stat in onstat -p) then the effect is more problematic that if you are mainly performing indexed lookups and even less so if those indexes are detached or fragmented themselves. > Also the database > is in a RAID array, so the data is stripped across 8 disks that there > is no continous partition that the table is placed. It would seem the > number of extents in this situation is little less important. No sweat. Make sure the RAID stripe size is small (8 or 16K) and that you have your RA_ parameters tuned well and striping can only help (unless that is a RAID5 set :^( of course). Art S. Kagel