EXPLAIN EXTENDED

BEGIN
        DBMS_RANDOM.seed(20090909);
END;
/

CREATE TABLE t_range (
        id NOT NULL PRIMARY KEY,
        grouper NOT NULL,
        start_date NOT NULL,
        end_date NOT NULL
)
AS
SELECT  level, MOD((level - 1), 10) + 1,
        TO_DATE('09.09.2009', 'dd.mm.yyyy') - TRUNC((level - 1) / 14400),
        TO_DATE('09.09.2009', 'dd.mm.yyyy') - TRUNC((level - 1) / 14400) +
        TRUNC(DBMS_RANDOM.value * 10) + 1
FROM    dual
CONNECT BY
        level <= 1000000
/
CREATE INDEX ix_range_grouper_startdate ON t_range (grouper, start_date)
/
CREATE INDEX ix_range_grouper_enddate ON t_range (grouper, end_date)
/
&#91;/sourcecode&#93;
</div>

This table contains <strong>1,000,000</strong> rows in <strong>10</strong> groups.

Ranges start every <strong>10</strong> minutes (<strong>144</strong> per day) and last from <strong>1</strong> to <strong>11</strong> days. Range lengths are random.

To generate the count of ranges in each group that contain each date, we need to do the following:

<ol>
	<li>Generate a list of dates, from minimal to maximal. This can be easily done using a <code>CONNECT BY</code> query on <code>dual</code> table:


SELECT  cur_date
FROM    (
        SELECT  (
                SELECT  MIN(start_date)
                FROM    t_range
                ) + level - 1 AS cur_date
        FROM    dual
        CONNECT BY
                level <=
                (
                SELECT  MAX(end_date)
                FROM    t_range
                ) -
                (
                SELECT  MIN(start_date)
                FROM    t_range
                ) + 1
        ) dates

&#91;/sourcecode&#93;

<div class="terminal">
<table class="terminal">
<tr>
<th>CUR_DATE</th></tr>
<tr><td class="date">02.07.2009 00:00:00</td></tr>
<tr><td class="date">03.07.2009 00:00:00</td></tr>
<tr><td class="date">04.07.2009 00:00:00</td></tr>
<tr class="break"><td colspan="100"></td></tr>
<tr><td class="date">18.09.2009 00:00:00</td></tr>
<tr><td class="date">19.09.2009 00:00:00</td></tr>
<tr class="statusbar"><td colspan="100">80 rows fetched in 0.0017s (0.0011s)</td></tr>
</table>
</div>

Since <code>MIN</code> and <code>MAX</code> are instant on indexed fields, this query is instant too.</li>

<li>
Build a rowset that would contain a number of ranges starting on each given date. We can use a simple <code>GROUP BY</code> to do this, since missing dates will be handled by a <code>LEFT JOIN</code> with the list of generated dates that we built on the previous step:


SELECT  grouper AS sgrp, start_date, COUNT(*) AS scnt
FROM    t_range
GROUP BY
        grouper, start_date
ORDER BY
        grouper, start_date

We see that within group 1, 640 ranges started on 02.07.2009, 1,400 ranges started on 03.07.2009 etc.

SELECT  grouper AS egrp, end_date, COUNT(*) AS ecnt
FROM    t_range
GROUP BY
        grouper, end_date
ORDER BY
        grouper, end_date

And here's the final query:

SELECT cur_date,
grouper,
SUM(COALESCE(scnt, 0) - COALESCE(ecnt, 0)) OVER (PARTITION BY grouper ORDER BY cur_date) AS ranges
FROM (
SELECT (
SELECT MIN(start_date)
FROM t_range
) + level - 1 AS cur_date
FROM dual
CONNECT BY
level <= ( SELECT MAX(end_date) FROM t_range ) - ( SELECT MIN(start_date) FROM t_range ) + 1 ) dates CROSS JOIN ( SELECT DISTINCT grouper AS grouper FROM t_range ) groups LEFT JOIN ( SELECT grouper AS sgrp, start_date, COUNT(*) AS scnt FROM t_range GROUP BY grouper, start_date ) starts ON sgrp = grouper AND start_date = cur_date LEFT JOIN ( SELECT grouper AS egrp, end_date, COUNT(*) AS ecnt FROM t_range GROUP BY grouper, end_date ) ends ON egrp = grouper AND end_date = cur_date - 1 ORDER BY grouper, cur_date [/sourcecode]

The results are just like we expected.

Note the JOIN condition end_date = cur_date - 1. We subtract a day to make sure that we select events that ended strictly before the date. The events that ended on the date in question still contain it and should contribute into the count.

This query is very fast (1,000,000 rows are processed in just a trifle more than a second).