Вложенные таблицы

Вложенные таблицы - часть объектно-реляционных расширений (Object Relational Extensions) СУБД Oracle. Вложенная таблица, один из двух типов наборов в Oracle, очень похожа на подчиненную таблицу в традиционной для реляционной модели паре таблиц главная/подчиненная. Это неупорядоченный набор элементов данных одного типа, встроенного или объектного. Но при использовании вложенных таблиц создается впечатление, что каждая строка в главной таблице имеет отдельную подчиненную таблицу. Если в главной таблице - 100 строк, то имеется 100 виртуальн1х вложенных таблиц. Физически же имеется только одна главная и одна подчиненная таблица. Кроме того, между вложенными и главными/подчиненными таблицами есть много синтаксических и семантических различий, которые мы рассмотрим в этом разделе.

хотя бы одной таблицы, при полном просмотре будут затрагиваться все выделенные блоки. Это отличает таблицы в хеш-кластере от всех остальных таблиц.

Количество ключей хеш-кластера фиксировано. Нельзя изменить размер хеш-таблицы, не перестраивая кластер. Но это никак не ограничивает объем данных, которые можно хранить в кластере, - ограничивается только количество генерируемых для кластера хеш-ключей. Это может повлиять на производительность из-за незапланированного совпадения ключей при слишком малом значении параметра HASHKEYS.

Эффективный просмотр диапазонов значений ключа кластера невозможен. При проверке условий типа WHERE cluster key BETWEEN 50 AND 60 не может использоваться алгоритм хеширования. Между значениями 50 и 60 много возможных значений, и серверу пришлось бы все их сгенерировать, чтобы хешировать каждое и найти соответствующие данные. Это невозможно. При поиске по диапазону значений ключа хеш-кластер будет просматриваться полностью, если нет отдельного обычного индекса.

Хеш-кластеры можно использовать при следующих условиях.

Достаточно точно известно, сколько строк будет в таблице, или можно указать

обоснованный верхний предел количества строк. Правильная установка значений параметров HASHKEYS и SIZE позволит избежать пересоздания. Если таблица существует недолго (например, в витринах или хранилищах данных), подобрать значения несложно.

Операторы ЯМД, особенно вставки, выполняются легко. Изменения тоже не тре-

буют дополнительных расходов ресурсов, если только не изменяется HASHKEY, чего лучше не делать, потому что придется переносить строку.

Доступ к данным происходит по значению ключа кластера, HASHKEY. Например, имеется таблица запасных частей, и доступ к ней выполняется по коду запасной части. Таблицы-справочники особенно удобно размещать в хеш-кластерах.

Вложенные таблицы можно использовать двумя способами. Один, - в PL/SQL-коде как средство расширения языка PL/SQL. Этот способ мы рассмотрим в главе 20. Другой, - как физический механизм хранения данных, для постоянного хранения наборов. Лично я постоянно использую их при программировании на PL/SQL и очень редко для постоянного хранения данных.

В этом разделе я кратко представлю синтаксис операторов для создания вложенных таблиц, выполнения к ним запросов и их изменения. Затем мы разберемся с подробностями реализации: что необходимо знать о фактическом хранении вложенных таблиц в СУБД Oracle.

Синтаксис вложенных таблиц

Создать таблицу с вложенной таблицей достаточно просто, а вот синтаксис операторов для работы с ними - несколько сложнее. Для демонстрации я буду использовать простые таблицы ЕМР и DEPT. Мы уже знакомы с этой небольшой реляционной моделью данных, которая реализуется следующим образом:

tkyte@TKYTE816> create table dept

2 (deptno number(2) primary key,

3 dname varchar2(14) ,

4 loc varchar2(13)

5 ) ;

Table created.

tkyte@TKYTE816> create table emp

Table created:

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

tkyte@TKYTE816> create or replace type emp type

as object (empno ename job mgr

hiredate

comm

number (4),

varchar2(10),

varchar2(9),

number(4),

date,

number(7, 2), number(7, 2)

10 );

11 /

Type created.

tkyte@TKYTE816> create or replace type emp tab type

2 as table of emp type

Type created.

Для создания таблицы с вложенной таблицей необходим тип данных для вложенной таблицы. Представленный выше код создает сложный объектный тип EMPTYPE и тип вложенной таблицы, который называется EMP TAB TYPE. В языке PL/SQL с ним можно было бы работать как с массивом. В языке SQL будет создана физическая вложенная таблица. Вот простой оператор CREATE TABLE, использующий этот тип:

tkyte@TKYTE816> create table dept and emp

2 (deptno number(2) primary key,

3 dname varchar2(14),

4 loc varchar2(13),

5 amps emp tab type

7 nested table emps store as emps nt; Table created.

tkyte@TKYTE816> alter table emps nt add constraint emps empno unique

2 unique(empno)

Table altered.

Важная часть этого оператора создания таблицы - включение столбца EMPS типа EMP TAB TYPE и соответствующая конструкция NESTED TABLE EMPS STORE AS EMPSNT. При этом, помимо таблицы DEPT AND EMP, отдельно создается реальная физическая таблица EMPSNT. Я добавил ограничение по столбцу EMPNO непосредственно для вложенной таблицы, чтобы обеспечить уникальность значения EMPN0, как это было в исходной реляционной модели. Я не могу реализовать всю модель данных. Попытаюсь добавить требование ссылки на саму себя:

tkyte@TKYTE816> alter table emps nt add constraint mgr fk

2 foreign key(mgr) references emps nt(empno);

alter table emps nt add constraint mgr fk *

ERROR at line 1:

ORA-30730: referential constraint not allowed on nested table column

Однако это не срабатывает. Вложенные таблицы не поддерживают требования целостности ссылок, поскольку не могут ссылаться на другие таблицы, даже на самих себя. Поэтому пока оставим все как есть. Теперь давайте заполним таблицу данными из существующих таблиц ЕМР и DEPT: