<<
>>

CREATE INDEX имя индекса ON счет (код клиента, тип счета)

СУБД ведет индекс автоматически в специальном индексном файле. Индекс представляет собой совокупность иерархически связанных блоков данных, имеющих специальную структуру (рис. 12.20).
Минимальное число уровней иерархии в индексе равно 3. Каждому оператору CREATE INDEX соответствует свой индекс. Рассмотрим организацию индекса.

Блоки всех уровней, кроме последнего, имеют следующую структуру: в первых двух столбцах хранятся значения атрибутов «Код клиента» и «Тип счета», а третий столбец содержит ссылку на блок индекса более высокого уровня. В третьем столбце блока последнего уровня индекса хранятся номера записей таблицы Счет. Например, номер записи 9 означает, что в 9-й записи таблицы Счет хранятся код клиента и тип счета, равные 1 и 1. Строки каждого блока индекса лексикографически упорядочены (упорядочены коды) по значениям атрибутов, т.е. сначала строки упорядочены по атрибуту «Код клиента», а затем — по атрибуту «Тип счета».

Рассмотрим, как выполняется поиск записей с помощью индекса.

Пусть на сервер поступил запрос:

SELECT остаток

FROM счет

WHERE код клиента = 1 AND тип счета 14. Далее по указателю считывается первый блок 3-го уровня и здесь выбираются строки, для которых выполняется неравенство 10 < (Код клиента, Тип счета) < 14. Этому требованию удовлетворяют все три строки считанного блока 3-го уровня. Таким образом, условию поиска удовлетворяют записи с номерами 9, 2, 3. Затем эти записи считываются из таблицы Счет и их поля Остаток помещаются в результирующую таблицу (см. рис. 12.19).

Использование индексов для больших таблиц очевидно. Предположим, что в таблице Счет хранится 1 млн записей. Если бы таблица Счет не была индексирована по атрибутам Код клиента и Тип счета, то для проверки условия серверу СУБД необходимо было бы считать 1 млн записей.

В данном же случае СУБД просмотрела только три блока индекса и считала три записи.

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

INSERT INTO счет (номер счета, код клиента, тип счета, остаток)

VALUES (15, 1,3, 55);

Пусть номер первой пустой записи в таблице Счет равен 10. Сделаем упрощающее предположение, что максимальное число строк в каждом блоке индекса равно 3 (см. рис. 12.20). СУБД не только включает новую запись в таблицу, но и обновляет индекс.

Сначала ядро СУБД формирует код 13 (код клиента и тип счета в новой записи). В индексе выполняется поиск блока 3-го уровня, где хранится строка с наименьшим кодом, удовлетворяющим условию (код клиента, тип счета) >13. Это третья строка первого блока 3-го уровня (14 > 13). Перед этой строкой и должна быть включена новая строка (1, 3, 10). Но этот блок заполнен. В этом случае СУБД выполняет следующие действия: запрашивается память под еще один блок 3-го уровня, верхняя половина строк первого блока (3-го уровня) остается в нем, а нижняя строка вместе с новой переписывается в новый блок. Так как появился новый блок 3-го уровня, то в блок 2-го уровня необходимо включить новую строку с указателем на этот новый блок. Но блок 2-го уровня заполнен. Поэтому создается новый блок 2-го уровня и в блок 1-го уровня помещается новая строка с указателем на этот новый блок. В результате формируется индекс, показанный на рис. 12.21.

Так как при включении новой строки в заполненный блок выполняется его «расщепление» (т.е. из одного блока получаются два), то индекс называется 5-деревом (В означает Bindery).

Ниже приведены рекомендации по кодированию операторов SELECT.

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

Например, запрос

SELECT код клиента FROM счет

WHERE остаток < 100 AND остаток > 80;

приведет к перебору всех записей таблицы Счет.

2. По возможности не используйте операцию NOT (не) для индексированных атрибутов. Например, запрос

SELECT код клиента, остаток FROM счет

WHERE NOT(HOMep счета = 3);

будет выполняться долго, так как в этом случае СУБД будет работать по следующему алгоритму (здесь предполагается, что атрибут Номер счета индексирован):

• считать из индекса все номера записей (список U1); при большой таблице этих номеров может быть много;

• считать из индекса номера записей, удовлетворяющих условию «номер счета = 3» (список U2);

• найти разность списков (U3 = Ul - U2), т.е. исключить из U1 номера, содержащиеся в списке U2;

• читать записи с номерами из списка U3.

При выполнении запроса

SELECT код клиента, остаток

FROM счет

WHERE код клиента < 10 AND тип счета =1 AND NOT(HOMep счета = 3);

СУБД организует поиск записей по сложному индексу (код клиента, тип счета) и уже после их чтения отфильтрует записи с условием «номер счета = 3».

3. Если создан сложный индекс (см. выше), то не используйте второй атрибут отдельно от первого в условии поиска, например:

SELECT код клиента, остаток

FROM счет

WHERE тип счета = 3;

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

Приведем еще несколько рекомендаций по созданию индекса.

1. Индексируемая таблица не должна быть слишком маленькой. Если СУБД может записать все строки таблицы всего в несколько блоков данных, то создавать индекс нет смысла. Объем ввода/вывода при обращении к строкам таблицы от этого не уменьшится.

2. Если для работы с таблицей очень часто используются операторы INSERT, UPDATE, DELETE, то большое число индексируемых атрибутов может привести к существенному увеличению времени выполнения этих операторов из-за значительных временных затрат на модификацию индексов.

3. Если часто выполняется поиск по какой-либо группе атрибутов, то рекомендуется построить сложный индекс для этих атрибутов.

4. Рекомендуется индексировать атрибуты, которые имеют сравнительно большое число различных значений. В этом случае уменьшается число номеров записей на одно значение атрибута, что сокращает время выполнения операций с индексными списками.

По возможности следует не использовать соединения таблиц и вложенных подзапросов, при выполнении которых также применяется опера-

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

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

• блокировку обновляемых записей БД,

• ведение логических транзакций,

• обработку тупиковых ситуаций.

Блокировка обновляемых записей БД. Большинство СУБД автоматически блокирует записи БД при их обновлении.

Пример. Пусть в БД хранится таблица Счет и две операционистки банка одновременно выполняют следующие операции: первая операционистка переводит со счета 100 на счет 1001 условную сумму 300, а вторая операционистка перечисляет со счета 1001 на счет 100 условную сумму 150.

Предположим, что разработана одна хранимая процедура на языке PL/SQL Oracle для выполнения указанных операций:

1 CREATE PROCEDURE проводка (номер 1 IN NUMBER, номер 2 IN NUMBER, сумма IN NUMBER) IS

2 CURSOR курсор (ном_счета1 NUMBER, ном счета 2 NUMBER) IS

SELECT номер, остаток

FROM счет

WHERE номер=ном_счета 1 OR номер=ном_счета 2

FOR UPDATE OF остаток;

3 запись Kypcop%ROWTYPE;

4 BEGIN

5 OPEN курсор (номер 1, номер 2);

6 LOOP

7

8

Конечно, эту задачу можно решить и по-другому, но здесь демонстрируется использование курсора, связывающего непроцедурный язык SQL с процедурным языком (в данном случае — с PL/SQL).

Прежде чем рассказать, каким образом СУБД блокирует записи, поясним некоторые операторы рассмотренной процедуры:

1 — CREATE PROCEDURE: создание хранимой процедуры с параметрами «номер!» (номер счета «откуда»), «номер2» (номер счета «куда»), «сумма» (сумма проводки).

2 — CURSOR: декларативный оператор, который определяет курсор с именем «курсор». Курсор имеет входные параметры («ном счета]» и «ном_счета2»), а также описывает оператор SELECT для обновления (FOR UPDATE OF).

3 — декларативный оператор, который строит в оперативной памяти переменную «запись». Структура записи совпадает со структурой списка атрибутов, описанных в операторе SELECT (номер, остаток).

4 — BEGIN: начало описания тела процедуры.

5 — OPEN: открыть курсор с параметрами, получаемыми процедурой при вызове. При открытии курсора оператор SELECT после подстановки параметров выполняется ядром СУБД. Результаты поиска возвращаются обратно процедуре в виде множества записей курсора.

6 — LOOP: начать цикл по записям курсора.

7 — FETCH: скопировать поля (номер и остаток) текущей записи курсора в переменную «запись».

8 — EXIT: выйти из цикла, если все записи курсора обработаны.

9 — IF: обновить поле «остаток» переменной «запись».

10 — UPDATE: обновить текущую запись курсора.

11 — END LOOP: завершить тело цикла LOOP.

12 — CLOSE: закрыть курсор.

13 — COMMIT: завершить транзакцию и снять все блокировки.

14 — END: завершить описание тела процедуры.

На рис. 12.22 показано, как с помощью блокировок СУБД корректно выполняет обновления записей таблицы Счет при одновременном обращении рабочих станций к процедуре «проводка».

Первый процесс открывает курсор, и ядро СУБД выполняет описанный в этом курсоре оператор SELECT. Все найденные записи автоматически блокируются, так как при описании курсора было указано FOR UPDATE OF. При открытии курсора другим процессом выполняется поиск записей, но так как они блокированы, выполнение оператора SELECT откладывается и второй процесс переходит в состояние ожидания. После выполнения первым процессом оператора COMMIT обновленные записи становятся доступными другим клиентам (т.е. перемещаются из сегмента отката в таблицу Счет), записи 100 и 1001 разблокируются и выполняется оператор SELECT, связанный со вторым процессом.

При этом поиск ведется уже по обновленной таблице Счет.

Ведение логических транзакций (ЛТ). Логическая транзакция — это совокупность изменений в БД, которые должны быть все зафиксированы в БД либо ни одного. Все СУБД, поддерживающие распределенную обработку, обеспечивают ведение транзакций. Различные СУБД обеспечивают работу с ЛТ примерно по одной схеме. Рассмотрим реализацию этой схемы на примере Oracle,

В PL/SQL для работы с транзакциями используют два оператора:

• COMMIT — это многофункциональная команда; она используется, чтобы

а) завершить предыдущую транзакцию и начать новую,

б) отменить все блокировки, установленные предыдущей транзакцией,

• ROLLBACK — позволяет программным путем отменить изменения, выполненные текущей транзакцией.

Рассмотрим схему ведения транзакций (рис. 12.23).

Если программа выполняет изменение записи (программа 1), то это изменение фиксируется на диске в сегменте отката. Здесь сохраняются записи до и после изменений. СУБД ведет в сегменте отката одну транзакцию для каждого активного процесса. Если другой процесс выполняет при этом поиск той же записи, то она читается не из сегмента отката, а из базы данных, т.е. до завершения транзакции этот процесс «не видит» никаких изменений, выполненных первым процессом.

По команде COMMIT СУБД выполняет следующие действия.

Рис. 12.22. Схема обработки двух запросов процедурой «проводка»

Рис. 12.23. Схема ведения транзакций

1. Транзакция из сегмента отката переписывается в журнал транзакций. Переписываются записи после изменения (см. 1 на рис. 12.23). Если до этого активным являлся журнал транзакций на диске 2 и он полностью заполнен (журнал имеет фиксированный размер на диске), то СУБД автоматически переключается на журнал транзакций, размещенный на диске 1. Теперь на нем будут сохраняться записи транзакций «после обновления». При этом журнал на диске 2 будет автоматически архивироваться сервером СУБД на устройство высокой емкости. После архивирования журнал освобождается для приема записей транзакций (после переполнения журнала на диске 1), и т.д.

2. Записи БД «после обновления» запоминаются в таблицах БД (через буфер сервера СУБД), т.е. СУБД показывает изменения другим процессам.

Рис. 12.24. Схема восстановления данных в БД после сбоя

Необходимо отметить, что транзакция процесса 1 удаляется из сегмента отката не сразу после выполнения команды COMMIT. Это связано с использованием в Oracle версий записей. Пусть оператор SELECT (см. процесс 2) пытается читать запись, обновленную процессом 1. Если дата (время) обновления записи (дата выполнения COMMIT) позже даты начала выполнения SELECT, то запись «до обновления» читается из сегмента отката (см. 3 на рис. 12.23). Это связано с тем, что по SELECT уже могли быть прочитаны из БД другие записи той же транзакции, но до того как они были обновлены в БД. Чтобы сохранить целостность поиска (т.е. чтобы не было рассогласования), используется старая версия записи («до обновления»). Если дата обновления записи раньше даты начала выполнения SELECT, то читается запись из БД (см. 4 на рис. 12.23). Транзакция удаляется в сегменте отката (т.е. освобождается память в этом сегменте) в том случае, если сервером СУБД выполнены все команды, которые были начаты до завершения этой транзакции.

Рассмотрим, как СУБД восстанавливает данные в БД после сбоев или отказов (рис. 12.24). В сервере СУБД через определенный интервал времени запускается процесс, перезаписывающий измененные блоки из буфера сервера СУБД на диск. После этого в журнале транзакций делается соответствующая отметка. Предположим, что система «зависла» в момент перезаписи транзакции из сегмента отката в журнал транзакции (см. «неполная транзакция» на рис. 12.24). Это один из самых неприятных случаев. После восстановления и перезагрузки системы СУБД автоматически пытается восстановить потерянные данные и целостность БД.

1. СУБД находит в журнале транзакций последнюю (с конца файла журнала) отметку о перезаписи данных на диск и выполняет докат базы данных (см. рис. 12.24), т.е. начиная с этой отметки и до конца файла, СУБД читает из журнала транзакций записи после изменений и записывает их в БД (через буфер сервера СУБД). При этом в БД помещаются записи после изменений и из неполной транзакции.

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

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

CREATE PROCEDURE (номер, IN NUMBER, номер2 IN NUMBER,

сумма IN NUMBER) IS

BEGIN

UPDATE счет SET остаток=остаток - сумма WHERE номер=номер, ;

UPDATE счет SET остаток=остаток + сумма WHERE номер=номер2;

END;

Тупиковая ситуация возникает при одновременном обращении рабочих станций к процедуре «проводка». В данном случае СУБД выполнит откат транзакции процесса с наименьшим приоритетом и выдаст этому процессу сообщение об ошибке.

В прикладной программе должны быть закодированы операторы распознавания ошибок. В PL/SQL для этого целей используется блок EXCEPTION:

EXCEPTION

-- если транзакция была прервана СУБД

WHEN TRANSACTION_BACKED_OUT

обработка ошибки;

WHEN идентификатор другой ошибки

обработка ошибки;

END;

Рис. 12.25. Окно Module Definition

<< | >>
Источник: Григорьев Ю.А., Ревунков Г.И.. Банки данных. 2002

Еще по теме CREATE INDEX имя индекса ON счет (код клиента, тип счета):

  1. Индекс Флэша
  2. Индекс Флэша — Кинкейда
  3. ИНДЕКС
  4. ИНДЕКС СОЦИОМЕТРИЧЕСКИЙ
  5. Зрачковый индекс
  6. Двойной счет
  7. Глава 72 - Гражданского кодекса Банковский счет
  8. Статья 1073. Правовые последствия ненадлежащего выполнения банком операций по счету клиента
  9. Статья 1062. Внесение денежных средств на счет вкладчика другим лицом
  10. Статья 885. Устранение недостатков за счет заказчика
  11. Статья 1069. Кредитование счета
  12. Статья 621. Выполнение обязательства за счет должника
  13. Статья 1066. Договор банковского счета
  14. Статья 1067. Заключение договора банковского счета
  15. Предложение №27 Новое имя.