Лекция 12: Служебные виды объектов. Работа с редакциями объектов

Индексы

Индексы в БД в Oracle

Индексы (от латинского "указатель", обращающий внимание на запрещенные католической церковью книги или их фрагменты) являются хранимыми вспомогательными объектами, используемыми при отработке запросов на SQL. В базах данных смысл индексов — воплотить функцию, позволяющую по значению (или набору значений) получить адреса строк таблицы с этими значениями. В промышленных БД всех типов подобные функции обеспечиваются с помощью индекса как специальной хранимой структуры, хотя умозрительно требования в такой структуре нет, а нужна лишь функциональность.

Наиболее употребимы в Oracle B*-древовидные индексы. Они могут создаваться:

• автоматически, СУБД — как средство проверки ограничений целостности "первичный ключ" и "уникальность" в таблицах,
• либо вручную, разработчиком — ради ускорения доступа к строкам таблицы.

Во втором случае ("вручную") для создания индексов используется специальная команда SQL. Примеры:

CREATE INDEX emp_idx ON emp ( ename );
CREATE UNIQUE INDEX name_loc_idx ON dept_copy ( dname, loc );

На выбор столбцов для древовидного индекса есть ограничения.

• Разрешено создавать индекс не более чем на 32 столбца.
• Нельзя индексировать столбцы некоторых типов (например, семейства LOB или же LONG/LONG RAW).

Влияние индексов на эффективность работы с БД противоречиво. Индексы:

• способны сокращать время обращения к строке таблицы;
• способны увеличивать время обращения к строке таблицы (при "неудачном" распределении индексированных строк по блокам);
• требуют места в БД;
• способны замедлять обновления таблиц.

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

Некоторые общие и простые соображения по поводу использования древовидных индексов:

• Индекс неэффективен при малом количестве различных индексированых значений (например, пол: "М" и "Ж"), когда они представлены примерно равными количествами.
• При отсутствии значений (NULL) сразу во всех индексируемых столбцах (если индекс построен по нескольким столбцам) строка не индексируется. Поиск "по отсутствующим значениям" будет игнорировать индекс и выполняться полным просмотром таблицы.

Второй по важности тип индекса появился в версии Oracle 8.1 и существует для Enterprise Edition. Это поразрядный (bitmap) индекс. Он используется исключительно для ускорения доступа к данным таблицы и дает отдачу во вполне определенных обстоятельствах.

Доменный индекс (иначе — прикладной, предметный) программируется разработчиком приложения для конкретного типа объектов, однако несколько видов доменных индексов приходит в готовом виде с ПО Oracle, будучи уже запрограммированными разработчиками СУБД.

Для всех видов индексов допускаются частные случаи конфигурации.

Индексы для проверки заявляемых ограничений целостности

Употребление индексов ради ускорения доступа составляет предмет оптимизации запросов и не является темой настоящего материала. Здесь приводятся некоторые сведения об индексах, используемых СУБД для технической поддержки ограничений целостности.

При обычном объявлении в таблице первичного ключа или свойства уникальности столбцов СУБД автоматически создаст служебный уникальный древовидный индекс. В случае многостолбцовой уникальности допускается задать несколько ограничений на одних и тех же столбцах, но обязательно перечисляемых в разном порядке. Для всех таких ограничений будет использоваться один и тот же индекс — соответствующий первому по порядку создания ограничению. В результате следующих действий два "разных" ограничения AB и BA будут внутренне проверяться одним и тем же индексом AB:

CREATE TABLE t ( a NUMBER, b NUMBER, c NUMBER );
ALTER TABLE t ADD CONSTRAINT ab UNIQUE ( a, b );
ALTER TABLE t ADD CONSTRAINT ba UNIQUE ( b, a );

В автоматику создания служебного индекса можно вмешаться. Так, желаемые свойства автоматически создаваемому индексу можно сообщить, вложив в предложение CREATE TABLE или ALTER TABLE … ADD ограничение (где формулируется ограничение целостности) конструкцию CREATE INDEX, например:

CREATE TABLE t ( 
  c NUMBER PRIMARY KEY USING INDEX ( CREATE INDEX pk_t ON t ( c ) )
, d VARCHAR2 ( 100 ) 
);

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

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

CREATE TABLE t ( c NUMBER );
CREATE INDEX pk_t ON t ( c );
ALTER TABLE t ADD PRIMARY KEY ( c ) USING INDEX pk_t;

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

В последнем предложении конструкцию USING INDEX можно было бы не употреблять. Однако же если бы индекса PK_T заранее не существовало, эту же конструкцию можно было использовать для заведения индекса с желаемыми характеристиками, применив следующую формулировку:

... USING INDEX [имя_индекса] [свойства_индекса] ...

или даже:

... USING INDEX ( CREATE INDEX имя_индекса [свойства_индекса] ) ...

Обратите внимание, что индекс в этом случае не обязан быть уникальным. (Упражнение. Проверьте свойство уникальности у индекса PK_T). Более того, если ограничение создается как DEFERRABLE, индекс обязан быть неуникальным, и именно таковым он при том создается СУБД автоматически.

Если при заведения ограничения используется существующий индекс, явно создаваемый или же создаваемый ради ограничения с возможностью отложенной проверки (DEFERRABLE), то в отличие от автоматического он не будет удаляться вместе с удалением ограничения. (Упражнение. Проверьте это.) В этом есть своя логика, но это же может приводить к недоразумениям, когда по удалению ограничения целостности в БД сохранится "остаточный" индекс, не всегда нужный по делу.

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

CREATE TABLE tr ( d NUMBER REFERENCING t ( c ) );
CREATE INDEX fk_t ON tr ( d );
DROP INDEX fk_t;

Упражнение. Проверьте, что создание индекса на внешний ключ не оказывает влияния на логику поведения последнего.

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

Таблицы с временным хранением строк

Отличаются от обычных таблиц БД тем, что время хранения строк в них ограничено концом либо транзакции, либо сеанса связи с СУБД — по выбору разработчика БД. Описания же таких таблиц (метаданные) хранятся в словаре-справочнике БД на общих основаниях с описаниями обычных таблиц, то есть вплоть до выдачи команды DROP TABLE. Эти свойства объясняют выбор фирмой Oracle названия: GLOBAL TEMPORARY в отличие от таблиц LOCAL TEMPORARY, имеющихся со времен SQL-92 (но не в Oracle), полный жизненный цикл которых ограничен программным блоком.

Пример создания таблицы с временем хранения строк, ограниченным транзакцией:

CREATE GLOBAL TEMPORARY TABLE temp AS SELECT * FROM emp WHERE 1 = 2;
-- строк нет
INSERT INTO temp SELECT * FROM emp;
-- строки появились
SELECT * FROM temp;
-- проверка
COMMIT;
-- строки пропали
SELECT * FROM temp;
-- проверка

Упражнение. Как изменится результат команды CREATE выше, если в формулировке SELECT опустить фразу WHERE?

Примеры создания таблиц с явно указанными временами хранения строк — до конца текущего сеанса или же до конца текущей транзакции:

CREATE GLOBAL TEMPORARY TABLE tx ( c NUMBER ) ON COMMIT PRESERVE ROWS;
CREATE GLOBAL TEMPORARY TABLE ts ( c NUMBER ) ON COMMIT DELETE ROWS;

ON COMMIT DELETE ROWS не требует явного указания, так как подразумевается по умолчанию.

Если не считать "короткого" времени жизни строк, по своим потребительским свойствам таблицы с временным хранением строк почти не отличаются от обычных. Например, для них можно строить индекс (напомним: ведь их описание хранится постоянно).

Таблицы обоих видов предоставляют каждому сеансу собственное множество строк, независимое от строк, заведенных в других сеансах (для таблиц, где время хранения строк ограничено сеансом, это неочевидно). Однако выполнение операций DDL с такими таблицами СУБД по понятным причинам увязывает с наличием в них строк (собственных) в других сеансах. Так, построить индекс (CREATE INDEX) удастся только, если в данный момент другой сеанс не завел в таблице собственные строки. Таким образом, косвенная связь содержимого таких таблиц в разных сеансах все-таки имеется.

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