Lateral Inline Views

Oren Nakdimon 2 Replies

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: Continue reading

The FIRST and LAST Aggregate Functions

Oren Nakdimon 1 Reply

One of my favorite aggregate functions in Oracle is FIRST (and of course its counterpart, LAST; ok, so two of my favorite aggregate functions).

The very common aggregate functions MIN and MAX allow you (conceptually) to sort a group of rows by some column and return the value of that column from the first or last row in the sorted group.

The FIRST and LAST functions extend this ability, and allow you to sort a group of rows by one column, but return the value of another column from the first or last row in the sorted group. Continue reading

Creating an Index on a Static Table Referenced by an Active Table

Oren Nakdimon Leave a Reply

We have a parent-child pair of tables with a foreign key constraint between them, and we need to add an index to the parent table, while the application is active.

The parent table is static during the creation of the index (no DML on it), but lots of DML statements are done on the child table. The parent table is relatively big, so the index creation takes a relatively significant time.

Continue reading