Hi Layla, Glad to hear that you were able to see index plans for some of your data.
Can you do a showstats for the index column and find out the interval(s) in which values with frequency of 1 and 5? The compiler relies on the stats to figure out the cardinality and it is possible these extreme low frequency values are buried in some of the intervals and their low frequencies are not known spcifically. If that is the case, we may need to increase # of intervals (say to 100 or even higher) so that the intervals correctly represent the low frequency values. The default is 50 to reduce the memory usage. BTW, the compiler does recognize an opposite case where some value has very high frequency (say 10% of the total rows) and applies special joins (skew buster) when necessary. showstats for table <T> on <column> detail; update statistics for table <T> on <column> generate <n> intervals; Thanks --Qifan On Thu, Aug 6, 2015 at 2:25 AM, Martin, Layla (HP DualStudy) < [email protected]> wrote: > Hi, > > > > Sorry for just another question … > > > > I got another question regarding Trafodion indexes. Which type of index do > you use? > > > > I conducted a test with 50000 table rows and a varying frequency (number > of different entries in the indexed column). > > > > This test has shown the best results (best benefit over not using an > index) for frequencies 100 and 1000 (I’ve tested 1, 5, 50, 100, 1000, 10000 > and 50000, the optimizer didn’t use the index for 1 and 5). > > I thought that B-Trees work best with a frequency close to log(number of > rows), but the results have been best close to sqrt(number of rows). > > > > Do you have an explanation for that? > > I didn’t find any information on that in the docu/wiki or on Git. > > > > Best regards > > Layla > > > > *Layla Martin* > HP DualStudy > > Hewlett-Packard GmbH > > Telefon +49 7031 4504682 > > *[email protected] <[email protected]>* > > Herrenberger Strasse 140| 71034 Böblingen| www.hp.com/de/dualstudy > > [image: Description: Description: > http://intranet.hp.com/country/germany/live/PublishingImages/NeuesLogoSmall.png] > > Follow us on: [image: facebook] <https://www.facebook.com/DualStudy.hp> > [image: > twitter] <http://twitter.com/hpdualstudy> [image: youtube] > <http://www.youtube.com/user/hpDualStudy> > > Geschäftsführer: Heiko Meyer (Vorsitzender), Thomas Bässler, Volkhard > Bregulla, Michael Eberhardt, Jochen Erlach, Angelika Gifford, Ernst > Reichart > Vorsitzender des Aufsichtsrats: Jörg Menno Harms > Sitz der Gesellschaft: Böblingen, Amtsgericht Stuttgart HRB 244081 > WEEE-Reg.-Nr. DE 30409072 > > > > > -- Regards, --Qifan
