Re: Overflow Distributions in Online 7.20

Art S. Kagel wrote: > > mc wrote: > > > > I am having a performance problem in Online 7.20. When I look at > the > > distributions on the table using the "dbschema -hd" command , I > notice that > > the leading column of the index which I am using only shows 3 > distributions > > "bins" but shows 200 in an OVERFLOW heading. What causes this > overflow > > info on the distribution report and is it related to slow > performance on > > using the index ? > > An overflow 'bin' is created for any key value whose occurrance count > is more than a certain percentage of the total count for the keys in > the > bin to which it would otherwise have been assigned. I asked an > intense > series of questions on this very subject a few months ago. I saw > something similar and wanted to know if I should adjust the UPDATE > STATISTICS parameters to widen the bins so that fewer keys were in > overflow bins or narrow them so that more were. The answer is that > overflows are a "GOOD THING" they let the optimizer know exactly how > many of each of the most populous key values there are. This helps > the > optimizer to zero in on those values when needed and also keeps the > estimates for less populous keys from being skewed by these keys that > are essentially outliers. That is true, but overflow bins indicate highly duplicate key values. If most of your keys are highly duplicate, then this could impact on performance. A very long and narrow B-Tree structure will incur a lot more IO. If you have highly duplicate key values, consider adding additional columns to them to increase their uniqueness. Hope that helps, -- Mark. +----------------------------------------------------------+-----------+ |Mark D. Stock - Informix SA http://www.informix.com |//////// /| |mailto:mdstock@informix.com FAQ http://www.iiug.org |///// / //| | +-----------------------------------+//// / ///| | Tel: +27 11 807 0313 |If it's slow, the users complain. |/// / ////| | Fax: +27 11 807 2594 |If it's fast, the users keep quiet.|// / /////| |Cell: +27 83 250 2325 |Therefore, "No news: travels fast"!|/ ////////| +----------------------+-----------------------------------+-----------+