Коллекции в PL/SQL

Коллекции
Коллекцией называется упорядоченная группа элементов одного типа.

Язык PL/SQL поддерживает три вида коллекций:

1. вложенные таблицы (nested tables);
2. ассоциативные массивы (associative arrays);
3. массивы переменной длины (variable-size arrays).

Плюсы использования коллекций:

• Кеширование статичной информации
• Отслеживание элементов данных для спец.обработки
• Хранение списков непосредственно в столбцах таблицы

Вложенные таблицы (Nested Tables)

Вложенную таблицу можно рассматривать как одномерный массив, в котором индексами служат значения целочисленного типа. Вложенная таблица может иметь пустые элементы, которые появляются после их удаления встроенной процедурой DELETE. Количество элементов может динамически увеличиваться.

Максимальное число строк вложенной таблицы составляет 2 гигабайта.

Наиболее важным отличием между вложенными и индексированными таблицами является то, что вложенные таблицы являются постоянной формой коллекции.

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

• Могут использоваться как в SQL (тип столбца в таблице), так и в PL\SQL коде
• Содержат однородные данные, т.е. все строки имеют одинаковую структуру данных
• Требуется инициализация, при использовании в PL\SQL
• Порядок элементов не зафиксирован
• Используется память PGA (для всех 3 типов коллекций)
• При попытке чтения элемента с несуществующим индексом -> исключение NO_DATA_FOUND

Ассоциативные массивы

Вариант вложенной таблицы, в которой элементы могут иметь произвольные значения индексов, расположенные в определенном порядке.

Позволяет работать со столбцами как с единой переменной – массивом.

Является временной формой коллекции, существует в течении сессии.

Другое название ассоциативных массивов – индексированные таблицы или pl\sql таблицы.

Ассоциативный массив - это наиболее часто используемый вид коллекции. Его не надо инициализировать или расширять. В Oracle9i Database Release 2 и более поздних версиях ассоциативные массивы можно индексировать не только по числам, но и по строкам.

Массив переменной длины (VARRAY)

Подобно двум другим типам коллекций, массивы переменной длины являются одномерными коллекциями, состоящими из однородных элементов.

Особенности:

• Размер всегда ограничен и массив не может быть разреженным.
• Максимальное количество элементов указывается при определении типа.
• Могут использоваться как в PL\SQL так и в таблицах.
• Порядок элементов при сохранение и извлечении, в отличии от вложенных таблиц, сохраняется.
• Индекс – положительное число от 1 до 2,147,483,647
• Нельзя удалить произвольный элемент массива

Как узнать размер индекса в Oracle

Приложенный скрипт поможет определить физический размер таблицы и всех её индексов.

Хранение паролей в БД Oracle

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

В данном примере мы будем использовать пакет DBMS_OBFUSCATION_TOOLKIT, который доступен, начиная с Oracle8i:

• Таблицы с секретными данными
• Пакет для работы с шифрованием
• Тестирование

Узнать версию БД Oracle и совместимость

Простейший код для того, чтобы узнать версию БД и совместимость:

DECLARE
  l_version  VARCHAR2(100);
  l_compatibility  VARCHAR2(100);
BEGIN
  DBMS_UTILITY.db_version (version       => l_version,
                           compatibility => l_compatibility);
  DBMS_OUTPUT.put_line('Version: ' || l_version || '  Compatibility: ' || l_compatibility);
END;

Comma_to_table & table_to_comma

Есть удобное средство для создания небольших виртуальных таблиц со значениями, взятыми из строкового выражения с "," в качестве разделителя.

Методы доступа к данным таблиц Oracle

Full Table Scan (FTS)

Полное сканирование таблицы при поиске (также известно как последовательное сканирование). Часто это указание на проблему отсутствующего индекса таблицы или его не использования по каким либо причинам, например, не собрана статистика для оптимизатора Oracle или собрана давно и не соответствует текущему распределению данных в таблице. Но не всегда Full Table Scan это проблема! Например, для таблиц с небольшим количеством строк Full Table Scan может быть гораздо быстрее, чем индексный поиск. Все зависит от данных таблицы.

Index lookup

Поиск значения в таблице по существующему индексу. В результате такого поиска в индексе ищется уникальный внутренний системный идентификатор записи таблицы ROWID, по которому в дальнейшем осуществляется быстрый доступ к искомой строке. Существует несколько разновидностей индексного поиска, которые будут рассмотрены ниже:

- index unique scan
- index range scan
- index full scan
- index fast full scan
- index skip scan

Index unique scan
Метод поиска единственного значения через уникальный индекс. Всегда возвращается одно значение.

Index range scan
Метод применяется для поиска множества значений при помощи индекса. Используется при поиске диапазонов значений при помощи SQL операторов between, >, <, <>, >=, <=. Для неуникального индекса может выдавать множество значений и для условия равенства, например COL_NAME=3. Index Full Scan
Метод выполняет полное сканирование индекса. Применяется в том случае, если все необходимые данные могут быть считаны из индекса без обращения к данным таблицы. Возвращает считанные данные в отсортированном виде. Однако может быть неэффективен, так как всегда применяет одиночное чтение блоков индекса для обеспечения сортировки. Как правило решение об эффективности использования Index Full Scan принимает оптимизатор на основе собранной ранее статистики. Может применяться только тогда, когда все столбцы, участвующие в запросе, присутствуют в индексе и имеют ограничение NOT NULL.

Index Fast Full Scan
Выполняется полное сканирование индекса методом быстрого множественного чтения блоков индекса в сегменте данных. Такое быстрое чтение может выполняться сразу несколькими параллельными процессами. Применяется в том случае, если все необходимые данные могут быть считаны из индекса без обращения к данным таблицы. Не осуществляет сортировку считанных данных. Может применяться только тогда, когда все столбцы, участвующие в запросе, присутствуют в индексе и имеют ограничение NOT NULL.

Index Skip Scan
Метод доступа к составному индексу. Появился в Oracle начиная с девятой версии. В более ранних версиях Oracle не использовал составной индекс, если в условиях запроса не был в обязательном порядке указан лидирующий столбец составного индекса, что приводило к рекомендации использовать в составном индексе первым наиболее часто используемый столбец в SQL запросах. Метод доступа Index Skip Scan снял эти ограничения, теперь составной индекс может быть использован и в случае отсутствия в условиях запроса лидирующего столбца составного индекса.

ROWID
Самый быстрый метод доступа к данным таблицы по внутреннему системному идентификатору записи таблицы. Обычно применяется после индексного поиска или при явном указании в SQL запросе значения ROWID строки данных таблицы.


Joins

Join или Соединение это предикат, который объединяет данные из нескольких источников данных (таблиц и представлений). Существует 3 вида соединений, которые рассматриваются ниже:

- Sort Merge Join (SMJ)
- Nested Loops (NL)
- Hash Join (HJ)

Sort Merge Join (SMJ)
Выполняется соединение данных нескольких источников при помощи предварительной сортировки данных исходных таблиц. Часто очень неэффективный алгоритм из-за потенциально больших накладных расходов на сортировку данных перед выполнением соединения.

Nested Loops (NL)
Метод соединения данных вложенными циклами. Наиболее общий алгоритм соединения данных из нескольких внешних источников. Сначала сканируется источник 1, затем для каждой найденной записи источника 1 в цикле ищется удовлетворяющий условию список строк из источника 2, затем организуется цикл по источнику 3 и т. д. В случае наличия индексов осуществляется индексный поиск данных в источниках по заданным условиям, иначе выполняется полное сканирование таблиц-источников. В случае отсутствия индексов время выполнения соединения может оказаться неприемлемо высоким.

Hash Join (HJ)
При выполнении соединения предварительно строится хэш-массив согласно условиям поиска. Hash Join является самым быстрым алгоритмом соединения данных из нескольких источников, появился начиная с версии Oracle 7.3, однако не является универсальным, т.е. не может быть использован для любых условий соединений.

Cartesian Product
Декартово произведение. Декартово произведение возникает обычно в том случае, если для нескольких источников не указаны никакие условия соединения. В итоге каждая строка из источника 1 соединяется со всеми строками источника 2, затем то же самое для каждой строки источника 2 по отношению к источнику 3 и так далее. Декартово произведение способно генерировать огромное количество строк и как правило является ошибкой кодирования. Однако иногда декартово произведение может применяться намеренно.

Источник.

Синтаксис объединения стандарта ANSI в Oracle

Oracle9i вводит новый синтаксис объединения, соответствующий стандарту ANSI SQL, определенному для SQL/92.