Индексные массивы в Oracle PL/SQL

Про индексные массиы

Язык программирования PL/SQL, предназначенный для программирования серверной части приложений, является очень мощным инструментов в умелых руках. Одним из интереснейших инструментов эффективного программирования являются такие представители коллекций PL/SQL как индексные таблицы, или ассоциативные массивы. Одним из основных применений индексных таблиц, на мой взгляд, является кэширование справочных таблиц приложений для быстрого доступа к часто используемым данным. Быстрый доступ будет автоматически обеспечиваться благодаря поиску записи в индексной таблице по индексному ключу. В этом случае поиск будет выполняться в памяти максимально быстро. Но использование индексных таблиц в качестве кэш-таблиц имеет и существенный недостаток. Индексные таблицы определяются как глобальный объект PL/SQL пакета и существуют на уровне сессии пользователя, поэтому в каждой сессии данные в них будут считываться из таблиц базы данных и занимать некоторую память. В этом смысле такое кэширование данных должно использоваться либо для небольших объемов данных, для которых критична скорость выборки, либо для крупных расчетных задач, которые запускаются не часто отдельными пользователями.

Кэширование простой таблицы индексным массивом

Допустим, у нас в приложении есть таблица UNITS, в которой хранится список единиц измерения и их параметров, и наше приложение очень часто обращается к этой таблице по ИД единицы измерения. Таблица с единицами измерения не очень большая, ее рост предсказуем, нам очень важна скорость получения параметров ЕИ по ИД, поэтому принимаем решение использовать индексную таблицу. Для этого создадим пакет с именем UNIT и в спецификации пакета определим запись, которая должна хранить некоторые параметры ЕИ:

TYPE trUNIT_PARAM IS RECORD (fnPARAM1 NUMBER,
                             fnPARAM2 NUMBER);

Далее определим тип индексной таблицы:

TYPE ttUNIT_PARAM IS TABLE OF trUNIT_PARAM INDEX BY PLS_INTEGER;

Наша индексная таблица или ассоциативный массив будет хранить в качестве элементов объявленную нами запись, индексом  таблицы будет целое число с типом pls_integer. Можно в качестве индекса использовать также binary_integer (уже устарело, использовать этот числовой тип не рекомендуется) или varchar2(N). Остается определить только глобальный индексный массив в спецификации пакета:

gtUNIT_PARAM ttUNIT_PARAM;

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

FUNCTION GET_UNIT(pnID    IN NUMBER,
                  pbFORCE IN BOOLEAN:=FALSE
                 ) RETURN trUNIT_PARAM
IS
  lrUNIT_PARAM trUNIT_PARAM;
BEGIN

  IF NOT pbFORCE AND gtUNIT_PARAM.EXISTS(pnID)
  THEN
    RETURN gtUNIT_PARAM(pnID);
  END IF;
 
  SELECT VALUE1,
         VALUE2
    INTO lrUNIT_PARAM.fnPARAM1,
         lrUNIT_PARAM.fnPARAM2
    FROM UNITS
   WHERE ID=pnID;
   
  gtUNIT_PARAM(pnID):=lrUNIT_PARAM;

  RETURN lrUNIT_PARAM;
   

Эта функция имеет два параметра – ИД записи ЕИ и булев параметр принудительной перезагрузки записи параметров, если это потребуется. При помощи такой функции мы будем очень быстро получать параметры ЕИ, если это требуется делать часто (некоторая задержка будет только при первом обращении, когда будет происходить чтение записи из таблицы базы данных). Однако мы должны помнить, что таблица, которая кэшируется, должна иметь относительно небольшой размер и предсказуемый рост в процессе эксплуатации приложения. Фактически можно утверждать, что показанный выше способ является самым быстрым из возможных а Oracle. При использовании этой технологии необходимо понимать, что если пользователь удалил запись из таблицы единиц измерения или изменил ее параметры, необходимо после выполнения операций удаления или изменения проверить наш индексный массив и также удалить из него запись или перезагрузить ее. Для перезагрузки можно однократно использовать функцию GET_UNIT выставив параметр pbFORCE в TRUE.

Кэширование сложных структур данных индексными массивами.

В чем прелесть индексных массивов Oracle так это то, что мы можем объявлять их полями записей Oracle. Таким образом мы можем создавать индексные структуры данных с любой сложностью. Это обстоятельство при грамотном использовании может на порядки поднять скорость сложных расчетов по сравнению с обычным получением данных стандартными средствами SQL. Допустим, для таблицы единиц измерения UNITS у нас существует подчиненная таблица с коэффициентами пересчета из одной ЕИ в другую, и нам в расчетах требуется для каждой ЕИ считывать список коэффициентов пересчета. В таком случае мы можем сначала объявить запись с параметрами коэффициента пересчета и индексную таблицу по типу этой записи:  

TYPE trRATIO IS RECORD (fnRATIO1 NUMBER,
                        fnRATIO2 NUMBER);

TYPE ttRATIO IS TABLE OF trRATIO INDEX BY PLS_INTEGER;

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

TYPE trUNIT_PARAM IS RECORD (fnPARAM1 NUMBER,
                             fnPARAM2 NUMBER,
                             ftRATIO  ttRATIO);

Естественно, что в процедуре GET_UNIT необходимо позаботиться о загрузке списка коэффициентов пересчета. В качестве индекса таблицы ftRATIO мы будем использовать ИД единицы измерения, в которую с заданным пересчитывается текущая ЕИ. В этом случае мы будем иметь быстрый индексный поиск по ИД пересчитываемой ЕИ. 

Проблема с индексом

Очень часто вы захотите использовать в качестве индекса ассоциативного массива идентификатор записи некоторой таблицы базы данных, который является суррогатным первичным числовым ключом. Значения идентификаторов формируются как правило при помощи последовательностей Oracle. Предельная разрядность числового типа number на много порядков превосходит разрядность числового типа pls_integer. Это может доставить серьезные проблемы при эксплуатации приложения, которое использует индексные массивы для кэширования данных, использующих в качестве индексного ключа идентификатор записи. Может произойти переполнение Numeric Overflow из-за несоответствия разрядности данных. В этом случае объявлять тип индексного массива следует с индексом по varchar2, а не pls_integer:

TYPE ttRATIO IS TABLE OF trRATIO INDEX BY VARCHAR2(48);

Скорость заполнения индексной таблицы с индексом по varchar2 будет значительно ниже (раз в 9-10), скорость поиска также значительно упадет (раз в 6-7), что связано с преобразованиями числового типа значения ключа к varchar2. Несмотря на это, можно утверждать, что индекс по varchar2 может быть также очень быстрым решением. В заключение хочется отметить, что грамотное использование показанной в этой статье технологии позволяло ускорить выполнение тяжелых расчетных задач в реальных системах с очень большими объемами обрабатываемых данных в десятки(!) раз по сравнению с общепринятыми подходами

Ссылка на основную публикацию