How to query for total days?

I have the following problem to solve using Oracle 9.2:

Assume a table exists with the following columns (and about 30 million rows):

member_id number
insurer_id number
eff_dt date -- no time component just the day term_dt date -- no time component just the day

Q. How many days was each member covered for the month of September 2002?

Note that the same member could be covered by multiple insurers and the coverages could overlap each other. However, each day should only be counted once. For example, if member_id = 100 was covered by insurer_id = 200 from the 1st to the 10th and and insurer_id = 300 from the 6th to the 15th then that member was covered for 15 days (not 10 + 10 = 20 days). You can use any features you like but the final result must come back from a single query with each member_id and the number of days.

background information ...

My current solution uses a pl/sql function to produce a bitmap mask for each row in the table representing the days covered for the given month. Then I use a user-defined aggregate function (data cartridge) to produce the bitwise or of the masks grouped by member_id. Finally, a pl/sql function is used to count the number of bits that are set in the final aggregated mask.

This all works as expected but it is VERY slow. The vast majority of the time is spent processing the custom aggregate function. For example, a MAX on the table by member_id only takes 5 minutes but my custom aggregate takes 270 minutes. This seems to be a fundamental problem with user-defined aggregates. There's nothing intensive about the bitwise or [in ODCIAggregateIterate: mask = mask - bitand(mask, value) + value]. I've used native compilation on the type and function but that had no affect on performance. I also tried parallel execution (yes I implemented ODCIAggregateMerge with 'parllel_enable') but that's actually slightly slower on our 8 processor machine (the timing for MAX was not parallel).

Any ideas?

Thanks,
Richard Received on Wed Oct 16 2002 - 14:41:32 CDT