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