· Jun 29 3m read

How to compress (maintain) bitmap indexes for volatile tables

InterSystems FAQ rubric

For volatile tables (tables with many INSERTs and DELETEs), storage for bitmap indexes can become inefficient over time.

For example, suppose that there are thousands of data with the following definition, and the operation of bulk deletion with TRUNCATE TABLE after being retained for a certain period of time is repeatedly performed.

Class MyWork.MonthData Extends (%Persistent, %Populate)
/// Level of satisfaction
Property Satisfaction As %String(VALUELIST = ",満足,やや満足,やや不満,不満,");
/// Age
Property Age As %Integer(MAXVAL = 70, MINVAL = 20);
Index AgeIdx On Age [ Type = bitmap ];

An image (partial) of the bitmap index storage created by INSERT is as follows.

^MyWork.MonthDataI("AgeIdx",20,1) = $zwc(401,120,4,75,102,10,<omit> 958)/*$bit(5,76,103,107・・・
^MyWork.MonthDataI("AgeIdx",21,1) = $zwc(407,121,29,178,251,2<omit>,732,772,898,960)/*$bit(3・・・
^MyWork.MonthDataI("AgeIdx",22,1) = $zwc(402,96,5,57,74,164,<omit>,0,4)/*$bit(20,63,77,92,10・・・
^MyWork.MonthDataI("AgeIdx",23,1) = $zwc(133,116)_$c(0,0,8,0<omit>,64,0,4)/*$bit(20,63,77,92・・・
^MyWork.MonthDataI("AgeIdx",25,1) = $zwc(404,119,105,155,235<omit>,947)/*$bit(106,156,236,30・・・
^MyWork.MonthDataI("AgeIdx",26,1) = $zwc(128,119)_$c(0,0,0,2,<omit>,0,128)/*$bit(26,80,115,1・・・
 <omit the following>

If you delete data all at once with TRUNCATE TABLE, the record data will disappear, but part of the bitmap index storage will remain (image).

^MyWork.MonthDataI("AgeIdx",20,1) = $zwc(145,120)/*$bit()*/
^MyWork.MonthDataI("AgeIdx",21,1) = $zwc(151,121)/*$bit()*/
^MyWork.MonthDataI("AgeIdx",22,1) = $zwc(146,96)/*$bit()*/
^MyWork.MonthDataI("AgeIdx",23,1) = $zwc(133,116)_$c(0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,・・・
^MyWork.MonthDataI("AgeIdx",24,1) = $zwc(131,125)_$c(0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,・・・
^MyWork.MonthDataI("AgeIdx",25,1) = $zwc(148,119)/*$bit()*/
^MyWork.MonthDataI("AgeIdx",26,1) = $zwc(128,119)_$c(0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,・・・
 <omit the following>

In this way, if unnecessary information remains in the bitmap index storage due to repeated data re-insertion, or if the storage becomes inefficient due to batch update work, methods of class %SYS.Maint.Bitmap Namespace() and OneClass() can be used to compress (maintain) bitmap indices and restore them to optimal efficiency.

An execution example is as follows.

// First argument: Class name
// Second argument: Do not record in the journal: 1; Record: 0
// Third argument: Display execution result: 1; Do not display: 0
>set st=##class(%SYS.Maint.Bitmap).OneClass("MyWork.MonthData",1.1)
Class:  MyWork.MonthData Start Time:  2017-06-21 15:34:54
     Global:  ^MyWork.MonthDataI("$MonthData")was compressed:  96.15%
           Old Size: 0.000(MB) New Size: 0.000(MB)
     Global:  ^MyWork.MonthDataI("AgeIdx")was compressed:  61.09%
           Old Size: 0.004(MB) New Size: 0.002(MB)
Compression time in seconds:  0

The Namespace() method does bitmap index compression for the entire namespace. See the class reference below for details.

Class reference (%SYS.Maint.Bitmap.Namespace())【IRIS】
Class reference (%SYS.Maint.Bitmap.Namespace())

Discussion (0)0
Log in or sign up to continue