2

I have a table and it has a 64-bit flag column named "flags". Each bit in the "flags" column represents a value. I have to search on the table for each of the bits in "flags" column. Shall i put an index over the column "flags" for faster retrieval? Does bitmap indexing will help here ? If it does, how will it work ?

user1642656
  • 21
  • 3
  • 1
    64 separate columns with 64 individual bitmap indexes would work. The data model you;re describing would be extremely hard to index particularly if you're searching by more than oe flag at a time. Any hope of fixing the data model? – Justin Cave Oct 16 '14 at 06:19
  • Since the properties are boolean, keeping each of them as a separate column was rejected as it would have made a huge table (considering we already have 60 columns). And hence it was clubbed in a bitmap and stored in a single column. Now we have to do filters on those flags. To make fast retrieval, i am looking to index it properly. Can you please elaborate 64 separate columns with 64 individual bitmap indexes ? – user1642656 Oct 16 '14 at 07:23
  • 1
    There is no technical drawback of having a table with 130 columns. And there is no practical way of fixing your problem when you insist that all 64 flags should be stored in one column and expect the database to perform a speedy retrieval. Sidenote: maybe you should change your datamodel to store the flags in a different table with a foreign key to the master table (1 record per flag). That might also help you as soon as you need to add the 65th flag :-) – Martin Schapendonk Oct 16 '14 at 08:07
  • An ordinary index (NOT a bitmap index) may have some benefit for queries - even if you're only after the nth bit, the query can do a fast full scan on the index instead of a full table scan. But your problem here is that you have people who think it's acceptable to save a few MB or GB of storage, at the cost of much slower queries and higher workload on the database server. – Jeffrey Kemp Oct 17 '14 at 15:14

0 Answers0