It's not a single query solution but would this work?
- Populate a global temporary table with the September dates for each
member.
- Delete from the global temporary table where the date is between the
eff_dt and term_dt for each member
- Count the remaining dates in the global temporary table grouping by
member
"Richard Kuhler" <noone_at_nowhere.com> wrote in message
news:Mhjr9.167514$U7.45873199_at_twister.socal.rr.com...
> 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 - 15:43:45 CDT
