Re: Table performance problem

Bill Weaver wrote: > > I have a performance problem with a certain table once that table exceeds > 1,000,000 rows and only when it exceeds 1,000,000 rows. I've set explain on > and the optimizer is using the proper index. We run update statistics > nightly. The table has only 1 extent and there are 3 levels on the index. > I have several ideas to try, but I'd like some opinions/suggestions before I > waste a lot of time trying to chase this one down. Anyone have any ideas as > to why we have this trouble once we hit the magic millionth row? I've > considered detaching the index, would this be a significant improvement or > only a minor one? Would just a simple index rebuild be enough (even though > there are only 3 levels)? Fragmentation is also another option. I > currently run ODS 7.22. As is often the case I have more questions for you than answers. How many leaves do(es) the index(es) have? Unique keys? Rebuilding the index with a high fill factor (say 95%) may save I/Os if there are a lot of 1/2 full leaves. HOW have you updated statistics? LOW(default)/MEDIUM/HIGH or per the recommendations in the manuals or release notes? What is the storage medium (single disk, disk partition, part of a RAID array (what type RAID1, RAID2, RAID5?) You may benefit more from striping the table accross multiple disks in hardware if possible than from partitioning the table in to multiple fragments. If the table has grown in such a way that the index leaves are no longer physically near to the data pages rebuilding or detaching the index may help. When is the table slow, simple queries, queries requiring sorts, queries when joined to another table? Try updating statistics according to the instructions in the SERVERS_7.2 release notes file if you do not do so now. Art S. Kagel