Wednesday, September 16, 2015

Oracle - bitmap index, function based index and use of sign with decode

Just had a quick random thought on a use case I have in an integration.  What I describe here is overkill and inappropriate in my case but it seems interesting enough to describe since it could be useful someday.

The general problem is one where I need to classify data into one of two buckets where the primary processing operates on one bucket.  The classification is over a field tracking the number of times a particular condition occurs.  The mapping of that count to the buckets, right now, is a simple comparison ( x <= y).  Instead of simply embedding the comparison in the selection query and using a standard b-tree style index I decided to consider what else could be done. 

First I found a method of mapping the count that I thought was interesting:

               DECODE(SIGN(10-"CONDITION_CNT"),1,1,0)

In this example, I am mapping condition counts of 9 or less to the "1" bucket and everything else to the "0" bucket. The condition count is always >= 0 therefore the subtraction guaranteed a positive result for counts 0 to 9.  The sign func returns 1 for anything >0 and 0 for an argument of 0 and -1 for anything less than 0. The decode maps the 1 to 1 and condenses everything else to 0.  And there is the data determining the correct bucket..

After that, I was able to generate a bitmap index over that set of function calls.  A bitmap index was chosen solely due to the cardinality.

CREATE BITMAP INDEX CONDITION_CNT_IDX ON SOME_TABLE (DECODE(SIGN(10-"CONDITION_CNT"),1,1,0));

No, this doesn't really help me but I still found it interesting. If I had some spare time I would try to do some sort of performance comparison just "to know".  Unfortunately, spare time pretty much doesn't exist.  A big reason this doesn't help me right now is my dataset is not very big.  And even if it was, my integration system would be a performance holdup before query access was ever a problem. 

Hope you found this of some interest.

Scott 



No comments:

Post a Comment