EXPLAIN EXTENDED

Continuing the theme on overlapping ranges:

• Overlapping ranges: SQL Server
• Overlapping ranges: MySQL

From Stack Overflow:

I have an event table that specifies a date range with start_date and end_date fields.

I have another date range, specified in code, that defines the current week as week_start and week_end.

I'd like to query all events for the week.

Today, let's see it in Oracle.

A quick reminder: we have two options to write a condition for such a query.

A more simple one:

start_date < @week_end AND end_date > @week_start

is, uh, more simple, but less efficient in SQL Server and MySQL.

A more complex one:


(start_date > @week_start AND start_date < @week_end)
OR
(@week_start BETWEEN start_date AND end_date)


is more index friendly.

Let's create a sample table and see how Oracle copes with these conditions:

CREATE TABLE t_event
(
id NUMBER(10) NOT NULL,
evt_start DATE NOT NULL,
evt_end DATE NOT NULL,
evt_value VARCHAR(400) NOT NULL
)
/
ALTER TABLE t_event
ADD CONSTRAINT pk_event_id PRIMARY KEY (id)
/
CREATE INDEX ix_event_start ON t_event (evt_start)
/
CREATE INDEX ix_event_end_start ON t_event (evt_end, evt_Start)
/
BEGIN
DBMS_RANDOM.seed(20090702);
END;
/
INSERT
INTO    t_event (id, evt_start, evt_end, evt_value)
SELECT  level,
TO_DATE('02.07.2009', 'dd.mm.yyyy') - level / 1440,
TO_DATE('02.07.2009', 'dd.mm.yyyy') - level / 1440 + FLOOR(DBMS_RANDOM.value() * 35) + 1,
RPAD('Value ' || level, 400, ' ')
FROM    dual
CONNECT BY
level <= 1000000
/        
COMMIT
/

Now, let's query.

Oracle supports a nice SQL-92 operator, OVERLAPS, which allows for simple overlapping checking:

SELECT  SUM(LENGTH(evt_value))
FROM    t_event
WHERE   (evt_end, evt_start) OVERLAPS (TO_DATE('21.06.2009', 'dd.mm.yyyy'), TO_DATE('21.06.2009', 'dd.mm.yyyy') + 7)

SELECT STATEMENT 
 SORT AGGREGATE
  TABLE ACCESS FULL, 20090702_events.T_EVENT

Unfortunately, it's not optimizable, and this query uses full table scan and runs for 8.87 seconds.

Let's use the second condition:

SELECT  SUM(LENGTH(evt_value))
FROM    t_event e
WHERE   (evt_start > TO_DATE('21.06.2009', 'dd.mm.yyyy') AND evt_start <= TO_DATE('21.06.2009', 'dd.mm.yyyy') + 7)
        OR (TO_DATE('21.06.2009', 'dd.mm.yyyy') BETWEEN evt_start AND evt_end)

SELECT STATEMENT 
 SORT AGGREGATE
  CONCATENATION 
   TABLE ACCESS BY INDEX ROWID, 20090702_events.T_EVENT
    INDEX RANGE SCAN, 20090702_events.IX_EVENT_START
   TABLE ACCESS BY INDEX ROWID, 20090702_events.T_EVENT
    INDEX RANGE SCAN, 20090702_events.IX_EVENT_END_START

This one is much better: 0.07 seconds, 100 times as fast.

We see that Oracle benefits from using the more complex condition too, provided that the table is properly indexed.