Hi I'm not sure I understand the question. Joining or normalization has nothing to do with that.
To stick with the example: Say you have the column month in a table with 1 million rows that have all values between 1 and 12. You can move those as constants into a table "Month" of course. And then? Link to their IDs? Which would be what? Integers from 1 to 12 (or 0 to 11 or whatever). So there would be no gain at all, algorithm- wise. PLUS the overhead of joining 1 million rows via a normal index with the 12 rows of the constant table. The interesting part here is by which strategy the table's column is scanned, not where the actual data is located. Of course not every value set matches enumerated integers. The few distinct values might be large strings, timestamps, etc. as well. Maybe that's what you are indicating with the question. Still the principle stays the same: doing a value comparison only ONCE for the whole query on the few bitmap index keys and than AND-ing togeher all the bits to yield the desired row IDs is much less work than shuffling through stuff or even joining data for each column involved in the search. To give a more concrete answer: Of course I don't have scientific research data comparing BITMAP indices and normal indices, with and without involved joining on constant tables, etc. And even if I did, they would only be for Caché and not for H2, naturally, because there are no BITMAP indices in H2 that could be tested. All I can say is what stands above: experience tells that for a great number of few distinct values, the bitmap principle yields tremendous better query performance (sometimes 100 times faster) than "normal" indices. I learned to think of ordinary ordered indices and bitmap indices as two complement strategies to efficiently handle different types of data: 1.) Many distinct values -> normal ordered index is better 2.) Few distinct values -> bitmap index is better (especially if the search condition has so scan more than one column). Not covering the second point is like ignoring "half" of the world of indexing (and, as already mentioned: the larger the row numbers get, the more like it is that the second point is the case). That's why I'm so interested in this topic and which databases support it. For further information, see http://en.wikipedia.org/wiki/Bitmap_index and http://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems#Indexes On Dec 10, 5:16 pm, cowwoc <[email protected]> wrote: > How does the performance compare to moving these values into their > own table and JOINing on a foreign key? > > Gili > > On 10/12/2011 8:27 AM, Paigan Jadoth wrote: > > > > > > > > > Hi > > > Some two years ago, Thomas kindly answered some questions I had > > briefly per mail, among them one about support for BITMAP indices. > > > My experience from work was back then and still is that BITMAP indices > > can provide a tremendous performance boost for data with many rows but > > only few distinct values (e.g. a column "month" with 10^5,6,7 rows but > > values always between 1 and 12. > > > Here's a very nice description of the concept in the (otherwise often > > fuzzy ^^) documentation of Intersystems Cach : > >http://docs.intersystems.com/cache20111/csp/docbook/DocBook.UI.Page.c... > > (btw. we found that BITMAP indices are superior to "normal" indices > > for most data that has to be indexed at all and later on, one of > > intersys' tech consultants approved that from their point of view) > > > The answer two years ago was "not yet" (see original german mail > > below). > > > I don't need it "right now", but it might be a major decision aspect > > wether to use H2 in our customer projects in the future. > > So all I want to ask now is: Is there an interest of putting the topic > > on the roadmap for a future version of H2? > > > Thank you. > > >> Re: Konkreter zu H2 > >> Sat, 30. Jan 2010 16:24:58 > > >> [...] > > >>> - Bitmap Index: Meiner/unserer Erfahrung in der Arbeit nach ist das eine > >>> exzellente Indexart. Aber ist soweit ich gelesen hab weder in H2 noch auf > >>> der Roadmap. Absichtlich nicht, oder nur "noch"> nicht? > >> Noch nicht. > > >> Gruss > >> Thomas -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
