Тип таблицы CONNECT XCOL

Таблицы XCOL основаны на другой таблице или представлении, например, на таблицах PROXY. Этот тип может быть использован, когда таблица-объект содержит столбец, содержащий список значений.

Предположим, у нас есть таблица 'children', которая может отображаться следующим образом:

Мы можем иметь другое представление этих данных, где каждый ребёнок будет связан со своей матерью, создав таблицу XCOL следующим образом:

CREATE TABLE xchild (
  mother char(12) NOT NULL,
  child char(12) DEFAULT NULL flag=2
) ENGINE=CONNECT table_type=XCOL tabname='chlist'
option_list='colname=child';

Опция COLNAME указывает имя столбца, который будет получать элементы списка. Это вернёт:

select * from xchild;

Запрошенное представление:

Некоторые моменты следует отметить:

• Если исходное поле children пустое, то результат зависит от спецификации NULL для столбца "multiple". Если он допускает NULL, как в данном случае, пустая строка сгенерирует значение NULL. Однако, если столбец не допускает NULL, строка вообще не будет сгенерирована.
• Пробелы после разделителя игнорируются.
• Копия исходных данных не создаётся. Обе таблицы используют одни и те же исходные данные.
• Указание определений столбцов в инструкции CREATE TABLE необязательно.

Столбец child в столбце "multiple" можно использовать как любой другой столбец. Например:

select * from xchild where substr(child,1,1) = 'A';

Это вернёт:

Если запрос не включает столбец "multiple", умножение строк не будет производиться. Например:

select mother from xchild;

Это просто вернёт всех матерей:

То же самое происходит с другими типами инструкций select, например:

select count(*) from xchild;      -- returns 5
select count(child) from xchild;  -- returns 10
select count(mother) from xchild; -- returns 5

Группировка также даёт разные результаты:

select mother, count(*) from xchild group by mother;

Результаты:

В то время как запрос:

select mother, count(child) from xchild group by mother;

Даёт более интересный результат:

Доступны некоторые дополнительные опции для этого типа таблиц:

Использование специальных столбцов с XCOL

В таблицах XCOL можно использовать специальные столбцы. Наиболее полезным является ROWNUM, который даёт ранг значения в списке значений. Например:

CREATE TABLE xchild2 (
rank int NOT NULL SPECIAL=ROWID,
mother char(12) NOT NULL,
child char(12) NOT NULL flag=2
) ENGINE=CONNECT table_type=XCOL tabname='chlist' option_list='colname=child';

Эта таблица будет отображаться как:

Чтобы вывести только первого ребёнка каждой матери, можно сделать:

SELECT mother, child FROM xchild2 where rank = 1 ;

возвращая:

Однако, обратите внимание на следующую ошибку: попытка получить имена всех матерей, имеющих более 2 детей, не может быть выполнена следующим образом:

SELECT mother FROM xchild2 where rank > 2;

Это происходит потому, что без умножения строк значение rank всегда равно 1. Правильный способ получить этот результат более сложный, но не может использовать столбец ROWNUM:

SELECT mother FROM xchild2 group by mother having count(child) > 2;

XCOL таблицы на основе указанных представлений

Вместо указания имени таблицы-источника через опцию TABNAME, можно извлечь данные из «представления», определение которого задаётся в новой опции SRCDEF. Например:

create table xsvars engine=connect table_type=XCOL
srcdef='show variables like "optimizer_switch"'
option_list='Colname=Value';

Затем, например:

select value from xsvars limit 10;

Это отобразит что-то вроде:

Примечание: все таблицы XCOL являются только для чтения.