Lateral Inline Views

Lateral Inline Views – an official feature of Oracle 12c (was hidden/unsupported/undocumented in 11g) – lets you refer within an inline view to tables that appear to its left in the FROM clause.

Let’s take an example (a simplification of a real-life use case I handled recently). We have a SCHEDULES table, including for each repeatable task the time range in which it should be executed, and the frequency:

create table schedules (
  name            varchar2(100) not null primary key,
  start_time      date,
  end_time        date, 
  repeat_interval interval day to second
);

Let’s fill it with 3 records: Task A should be executed every 12 minutes between 8am and 10am, Task B every 1 hour between 10am and 1pm, and Task C every 20 minutes between 1pm and 3pm:

insert into schedules (name,start_time,end_time,repeat_interval) values (
  'Task A',
  timestamp'2013-08-01 08:00:00',
  timestamp'2013-08-01 10:00:00',
  numtodsinterval(12,'minute'));
insert into schedules (name,start_time,end_time,repeat_interval) values (
  'Task B',
  timestamp'2013-08-01 10:00:00',
  timestamp'2013-08-01 13:00:00',
  numtodsinterval(1,'hour'));
insert into schedules (name,start_time,end_time,repeat_interval) values (
  'Task C',
  timestamp'2013-08-01 13:00:00',
  timestamp'2013-08-01 15:00:00',
  numtodsinterval(20,'minute'));

Now we need a query that presents all the actual instances of these tasks. We need to generate a different number of records for each task, based on the values of the START_TIME, END_TIME and REPEAT_INTERVAL columns. We’ll use an inline view with a “connect by level” query. But a regular inline view cannot refer to other tables in the FROM clause, so the following attempt will fail:

select s.name,
       inst.instance_number,
       inst.start_time
from   schedules s,
       (select level instance_number,
               s.start_time + (level-1)*s.repeat_interval start_time
        from   dual
        connect by s.start_time + (level-1)*s.repeat_interval < s.end_time) inst
order  by s.name,
          inst.instance_number;

ERROR at line 8:
ORA-00904: "S"."END_TIME": invalid identifier

Now, in 12c, we only need to add the LATERAL keyword before the inline view to make it work:

break on name skip 1

select s.name,
       inst.instance_number,
       inst.start_time
from   schedules s,
       lateral (select level instance_number,
                       s.start_time + (level-1)*s.repeat_interval start_time
                from   dual
                connect by s.start_time + (level-1)*s.repeat_interval < s.end_time) inst
order  by s.name,
          inst.instance_number;          

NAME       INSTANCE_NUMBER START_TIME
---------- --------------- -------------------
Task A                   1 01/08/2013 08:00:00
                         2 01/08/2013 08:12:00
                         3 01/08/2013 08:24:00
                         4 01/08/2013 08:36:00
                         5 01/08/2013 08:48:00
                         6 01/08/2013 09:00:00
                         7 01/08/2013 09:12:00
                         8 01/08/2013 09:24:00
                         9 01/08/2013 09:36:00
                        10 01/08/2013 09:48:00

Task B                   1 01/08/2013 10:00:00
                         2 01/08/2013 11:00:00
                         3 01/08/2013 12:00:00

Task C                   1 01/08/2013 13:00:00
                         2 01/08/2013 13:20:00
                         3 01/08/2013 13:40:00
                         4 01/08/2013 14:00:00
                         5 01/08/2013 14:20:00
                         6 01/08/2013 14:40:00

19 rows selected.

In earlier versions the same functionality can be achieved by using Collection Unnesting (which is a more complex solution and requires having corresponding types for the query):

create type execution_instance_t as object (
  instance_number number,
  start_time      date
)
/

create type execution_instance_tt as table of execution_instance_t
/

select s.name,
       inst.instance_number,
       inst.start_time
from   schedules s,
       table (cast (multiset (
         select level instance_number,
                s.start_time + (level-1)*s.repeat_interval start_time
         from   dual
         connect by s.start_time + (level-1)*s.repeat_interval < s.end_time)
       as execution_instance_tt)) inst
order  by s.name,
          inst.instance_number;

Lateral inline views have been used internally by the optimizer in 11g (at least) and could even be used by the end user by setting some event, as described here by Jonathan Lewis.