Re: Fragmentation in Data Warehouse

From: "Martyn Hodgson" <martyn.hodgson@eaglestar.co.uk> > >A simple question, but one (I guess) with a potentially long and complex >answer. We have a small Data Warehouse (c. 50Gbyte) running on a 4 cpu HP >K-series server (HP-UX 10.20). We're about to put IDS 7.30 UC6 into >production. The database resides on an EMC raid-0 disk array, with 8 (+8 >mirrors) disks. Currently we fragment our 'medium' sized tables 3 - way >with >the indexes on a 4th disk, and 'large' tables 7 - way, with indexes on the >8th disk. We have 8 temporary dbspaces, one per disk. The disk with the >indexes on, varies from table to table so no individual disk has all of the >indexes. Most of the tables are rebuilt each month from scratch using the >HPL. > >Most of our important queries join several tables. Response time is >frequently measured in hours rather than minutes. From xtree, it appears >that most problems tend to occurr when indexed are used to return data from >large tables. Large table scans (using light scans, PDQ and hashing their >results into temporary tables) seem to fly. Well then, why don't you drop the indexes on those big tables? And reduce the amount of buffers to increase the likelihood of light scans... >Performance is pretty good (I think), for load and query times, but we'd >like to squeeze as much out as possible. The IDS manuals detail the various >fragmentation options, but rarely suggest the best options in different >circustances. We use round robin fragmentation, because it was easiest to >set up. However, I'm happy to go for key range fragmentation, if this is >likely to be beneficial. Does anyone have any experience of different >fragmentation strategies for this type of system? Expression based fragment elimination *sounds* good, until you realise that users (especially DSS users) rarely go into a table via one access path. This means that you can't eliminate fragments very easily in a DSS system. In my experience, you're probably better off sticking with round robin... HTH. ______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com