Cast a multiset spell

[egorius]

Натолкнулся у Кайта на пример, который в максимально упрощённом виде можно сформулировать так. Пусть есть таблица с числовым столбцом. Нужно получить выборку, в которой на каждую исходную строку будет приходиться столько строк, какое значение записано в исходной строке.

Иными словами:

create table t(
  n number
);
insert into t(n) values (1);
insert into t(n) values (2);
insert into t(n) values (3);

Запрос должен вернуть:

1
2
2
3
3
3

Решение меня ошарашило.

select t.n
  from t
     , table(cast(multiset(
         select null from dual connect by level <= t.n
       ) as sys.odcinumberlist))

Какое-то время я тупо смотрел на запрос, потом всё-таки решил его понять.

• Мы соединяем исходную таблицу T с некой штукой, в которой, очевидно, всё дело;
• «select from dual connect by level <= N» — это оракловая идиома для выборки ровно N строк;
• «cast(multiset(subquery) as type) преобразует подзапрос к указанному типу PL/SQL-коллекции, в данном случае к числовому мультимножеству (sys.odcinumberlist — стандартный тип, эквивалентный table of number);
• «table(collection)» рассматривает коллекцию как таблицу.

Всё это хорошо, но подзапрос использует T.N! То есть во from’е одна таблица ссылается на другую!

Если попробовать сделать так:

select t.n
  from t
     , (
         select null from dual connect by level <= t.n
       )

то очевидно получим ORA-00904: "T"."N": недопустимый идентификатор. А вот с шаманством table-cast-multiset — работает.

Интересно заглянуть в план:

-----------------------------------------------------------
| Id  | Operation                           |  Name       |
-----------------------------------------------------------
|   0 | SELECT STATEMENT                    |             |
|   1 |  NESTED LOOPS                       |             |
|   2 |   TABLE ACCESS FULL                 | T           |
|   3 |   COLLECTION ITERATOR SUBQUERY FETCH|             |
|   4 |    CONNECT BY WITHOUT FILTERING     |             |
|   5 |     TABLE ACCESS FULL               | DUAL        |
-----------------------------------------------------------

То есть для каждой (1) строки из T (2) выполняется выборка из коллекции (3), которая получается с помощью connect by (4) из DUAL (5). Логично.

Но что получается? Получается, что Оракл в принципе умеет обходиться с условием соединения не в where (где ему место), а непосредственно в подзапросе. А если так, то почему это работает только через table-cast-multiset?.. Чего-то я всё-таки не понимаю.

Comments

[hardsign.livejournal.com]

беда этих индусов в том, что они изобретают всякую ненужную ю®, которой никто не знает как пользоваться, вместо того, чтобы сделать действительно нужные вещи™:
- чтобы forall понимал teble of record
- быстрый DBA_EXTENTS
- агрегирующую конкатенацию
- ...

A задачка твоя решается без извратов, правда, за два чтения таблицы:


select t.n
from t, (select level as l from dual connect by level<=(select max(n) from t)) d
where t.n>= d.l

[egorius.livejournal.com]

ORA-01473: подзапросы в предложении CONNECT BY недопустимы

Да, об forall я уже спотыкался. Насчёт dba_extents не в курсе, а про агрегирующую конкатенацию ты не это ли имеешь в виду?

[hardsign.livejournal.com]

Функция™ - именно эта. Ктулху фхтагн!

[egorius.livejournal.com]

Фдисятке, стало быть? А на девятке ругаицо.

Ктулху Р’льех вгах’нагл фхтагн, однозначно.