Oracle Database – CROSS APPLY and OUTER APPLY

An inline view normally cannot see the row it is being joined to. CROSS APPLY removes that restriction: the subquery in the FROM clause may reference columns from tables to its left, and it is evaluated once per outer row. Oracle added it in 12.1, borrowing the T-SQL spelling alongside the ANSI keyword LATERAL.

It is the tool for three things a plain join handles badly: top-N per group, several aggregates over the same child table in one pass, and calling something row by row.

The problem it solves

Before 12c, this was simply illegal — c.id is not in scope inside the inline view:

SELECT c.last_name, o.id, o.order_date
FROM   customers c,
       (SELECT * FROM orders o
        WHERE  o.customer_id = c.id      -- ORA-00904: "C"."ID": invalid identifier
        ORDER  BY o.order_date DESC
        FETCH  FIRST 3 ROWS ONLY) o;

Add CROSS APPLY and the correlation is legal:

SELECT c.last_name, o.id, o.order_date, o.total
FROM   customers c
CROSS  APPLY (SELECT * FROM orders o
              WHERE  o.customer_id = c.id
              ORDER  BY o.order_date DESC
              FETCH  FIRST 3 ROWS ONLY) o;

Three rows per customer, chosen per customer. A correlated scalar subquery could never do this: it returns one value, not three rows and four columns.

Four spellings, two behaviours

Oracle accepts both the T-SQL and the ANSI syntax, and they compile to the same thing. What actually differs is only whether an empty subquery drops the outer row.

That first row is the one that catches people. CROSS APPLY reads like "cross join, so nothing is lost", and it is the opposite:

-- Customers with no orders VANISH from this result
SELECT c.last_name, o.id
FROM   customers c
CROSS  APPLY (SELECT * FROM orders o WHERE o.customer_id = c.id
              ORDER BY o.order_date DESC FETCH FIRST 1 ROWS ONLY) o;

-- Customers with no orders appear with o.id NULL
SELECT c.last_name, o.id
FROM   customers c
OUTER  APPLY (SELECT * FROM orders o WHERE o.customer_id = c.id
              ORDER BY o.order_date DESC FETCH FIRST 1 ROWS ONLY) o;

Reach for OUTER APPLY by default unless you positively want the outer row filtered out. Silently losing rows is the harder bug to notice.

Two structural limits: the lateral view can only reference tables that appear before it in the FROM clause, and it cannot sit on the right-hand side of a RIGHT or FULL OUTER JOIN.

Use 1: top-N per group

This is the headline case, and it is worth comparing against the analytic-function version from the previous post, because they perform very differently.

-- (a) ROW_NUMBER: reads every order, numbers all of them, throws most away
SELECT * FROM (
  SELECT o.*, ROW_NUMBER() OVER (PARTITION BY customer_id
                                 ORDER BY order_date DESC) rn
  FROM   orders o
) WHERE rn <= 3;

-- (b) OUTER APPLY: walks an index backwards per customer and stops after 3
SELECT c.id, c.last_name, o.id AS order_id, o.order_date
FROM   customers c
OUTER  APPLY (SELECT o.id, o.order_date FROM orders o
              WHERE  o.customer_id = c.id
              ORDER  BY o.order_date DESC
              FETCH  FIRST 3 ROWS ONLY) o;

With an index on orders (customer_id, order_date DESC), (b) does three index reads per customer and never touches the rest of the table. (a) must scan and sort all orders before it can discard anything.

The trade-off flips with the shape of the data:

That last row is the trap. FETCH FIRST 3 inside the lateral view only stops early if Oracle can produce the rows already ordered. Without the index it sorts the whole matching set, per outer row, and the query is far slower than the analytic version.

Use 2: several aggregates in one pass

The pattern APPLY improves most, and the one people miss. Suppose you want each customer with their order count, revenue and last order date. Scalar subqueries mean three separate correlated queries against orders:

-- Three passes over orders per customer
SELECT c.last_name,
       (SELECT count(*)        FROM orders o WHERE o.customer_id = c.id) AS orders,
       (SELECT sum(o.total)    FROM orders o WHERE o.customer_id = c.id) AS revenue,
       (SELECT max(o.order_date) FROM orders o WHERE o.customer_id = c.id) AS last_order
FROM   customers c;

-- One pass, and the column list stays readable
SELECT c.last_name, s.orders, s.revenue, s.last_order
FROM   customers c
OUTER  APPLY (SELECT count(*)             AS orders,
                     sum(o.total)         AS revenue,
                     max(o.order_date)    AS last_order
              FROM   orders o
              WHERE  o.customer_id = c.id) s;

Note a real semantic difference here, not just a speed one. An aggregate with no GROUP BY always returns exactly one row, so this subquery is never empty — which means count(*) comes back as 0 rather than NULL for a customer with no orders, and CROSS APPLY would not drop them. The difference between CROSS and OUTER only bites when the subquery can genuinely produce zero rows.

The alternative shape — LEFT JOIN to a GROUP BY subquery — is fine and often produces a better plan when you are aggregating most of the child table. APPLY wins when you are filtering to a small slice of customers, because the aggregate is then only computed for the customers you asked about.

Use 3: per-row function calls

A table function returning a collection, invoked once per row:

CREATE OR REPLACE TYPE t_num_list AS TABLE OF NUMBER;
/

CREATE OR REPLACE FUNCTION split_ids (p_csv VARCHAR2) RETURN t_num_list PIPELINED IS
BEGIN
  FOR r IN (SELECT regexp_substr(p_csv, '[^,]+', 1, LEVEL) AS v
            FROM   dual CONNECT BY LEVEL <= regexp_count(p_csv, ',') + 1) LOOP
    PIPE ROW (to_number(r.v));
  END LOOP;
  RETURN;
END split_ids;
/

-- One row per id in each import row's CSV column
SELECT i.batch_id, x.column_value AS product_id
FROM   imports i
CROSS  APPLY TABLE(split_ids(i.product_ids)) x;

Or without a function at all, splitting a delimited column into rows:

SELECT i.batch_id, t.tag
FROM   imports i
CROSS  APPLY (SELECT regexp_substr(i.tags, '[^,]+', 1, LEVEL) AS tag
              FROM   dual
              CONNECT BY LEVEL <= regexp_count(i.tags, ',') + 1) t;

One clarification that saves time: JSON_TABLE, XMLTABLE and TABLE() are already implicitly lateral — they can reference an outer column with no APPLY at all. You only need APPLY when the thing on the right is an ordinary subquery.

-- No APPLY needed: JSON_TABLE sees o.payload directly
SELECT o.id, j.sku, j.qty
FROM   orders o,
       JSON_TABLE(o.payload, '$.items[*]'
         COLUMNS (sku VARCHAR2(40) PATH '$.sku',
                  qty NUMBER       PATH '$.qty')) j;

Reading the plan

A lateral view executes as a nested loop, and the column to watch is Starts — how many times the inner side ran.

SELECT /*+ GATHER_PLAN_STATISTICS */ c.id, o.order_date
FROM   customers c
OUTER  APPLY (SELECT o.order_date FROM orders o
              WHERE  o.customer_id = c.id
              ORDER  BY o.order_date DESC FETCH FIRST 3 ROWS ONLY) o
WHERE  c.status = 'ACTIVE';

SELECT * FROM dbms_xplan.display_cursor(format => 'ALLSTATS LAST');

------------------------------------------------------------------------------------------
| Id | Operation                        | Name                | Starts | E-Rows | A-Rows |
------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                 |                     |      1 |        |   2412 |
|  1 |  NESTED LOOPS OUTER              |                     |      1 |    810 |   2412 |
|* 2 |   TABLE ACCESS FULL              | CUSTOMERS           |      1 |    810 |    842 |
|  3 |   VIEW                           | VW_LAT_A18161FF     |    842 |      3 |   2412 |
|* 4 |    VIEW                          | VW_LAT_1BBF5C63     |    842 |      3 |   2412 |
|  5 |     WINDOW NOSORT STOPKEY        |                     |    842 |      3 |   2412 |
|  6 |      TABLE ACCESS BY INDEX ROWID | ORDERS              |    842 |        |   3254 |
|* 7 |       INDEX RANGE SCAN DESCENDING| ORDERS_CUST_DATE_IX |    842 |        |   3254 |
------------------------------------------------------------------------------------------

Everything good about that plan is visible in two places. Starts = 842 on the inner side is one execution per active customer, as expected. And WINDOW NOSORT STOPKEY over an INDEX RANGE SCAN DESCENDING is the payoff — NOSORT means the index already supplied the order, STOPKEY means it stopped at three. 3,254 rows touched instead of the whole orders table.

The VW_LAT_… names are Oracle's internal lateral views; seeing them just confirms the correlation was recognised. Two things to look for when an APPLY query is slow:

• SORT ORDER BY STOPKEY instead of WINDOW NOSORT STOPKEY — no usable index, so it is sorting per outer row. Add the composite index or switch to ROW_NUMBER.
• Starts far higher than you expected — the outer row source is not as filtered as you thought. Fix the outer predicate; a cheap inner query executed two million times is still expensive.

The optimiser may also decorrelate a lateral view — rewriting it into a hash join plus a window function, i.e. version (a) of the top-N query. That is usually a good decision on large row sources. If you need to compare both shapes, write both and time them rather than fighting the rewrite with hints.

On 11g, where none of this exists

APPLY and LATERAL are 12.1 and later. The equivalents:

• Top-N per group → ROW_NUMBER() in an inline view, filtered outside. Works everywhere, and on 11g there is also no FETCH FIRST, so the inner APPLY version would need ROWNUM anyway.
• Multiple aggregates → LEFT JOIN to a GROUP BY subquery.
• Table functions → TABLE() with the old (+) correlation, which already worked: FROM imports i, TABLE(split_ids(i.product_ids)).

When not to use it

APPLY is a nested loop by construction, so it is the wrong shape whenever you are processing most of both tables. A report aggregating every order for every customer wants a hash join and a GROUP BY, not 800,000 correlated executions. The rule of thumb: the outer row source should be small or well filtered, and the inner side should have an index that answers it directly. When both are true, nothing else in the language is close.

Next

Analytic functions — the other answer to top-N per group, and the feature that removes more self-joins than anything else in Oracle SQL.