лабораторный практикум. Русакова М.С. Лабораторный практикум. Практикум по базам данных самара 2015 министерство образования и науки российской федерации
 Скачать 0.72 Mb.
|
|
Индексы по убыванию. Индексы по убыванию позволяют отсортировать данные в структуре индекса от «больших» к «меньшим» (по убыванию), а не от «меньших» к «большим» (по возрастанию). 2. Индексы на основе битовых карт. Обычно в В*-дереве имеется однозначное соответствие между записью индекса и строкой – запись индекса указывает на строку. В индексе на основе битовых карт запись использует битовую карту для ссылки на большое количество строк одновременно. Такие индексы подходят для данных с небольшим количеством различных значений, которые обычно только читаются. Столбец, имеющий всего три значения – Y, N и 71 NULL, – в таблице с миллионом строк очень хорошо подходит для создания индекса на основе битовых карт. 3. Индексы по функции. Эти индексы на основе В*-дерева или битовых карт хранят вычисленный результат применения функции к столбцу или столбцам строки, а не сами данные строки. Это можно использовать для ускорения выполнения запросов вида: SELECT * FROM T WHERE ФУНКЦИЯ(СТОЛБЕЦ) = НЕКОТОРОЕ_ЗНАЧЕНИЕ, поскольку значение ФУНКЦИЯ(СТОЛБЕЦ)уже вычислено и хранится в индексе. 4. Прикладные (application domain) индексы. Это индексы, которые строит и хранит приложение, будь-то в базе данных Oracle или даже вне базы данных Oracle. Надо сообщить оптимизатору, насколько избирателен индекс, насколько «дорогостояще» его использование, а оптимизатор решает на основе этой информации, использовать этот индекс или нет. Текстовый индекс interMedia – пример прикладного индекса; он построен с помощью тех же средств, которые можно использовать для создания собственных прикладных индексов. 5. Текстовые индексы interMedia. Это встроенные в сервер Oracle специализированные индексы для обеспечения поиска ключевых слов в текстах большого объема. Индексы с обращенным ключом Индексы с обращенным ключом создаются, если в конце предложения CREATE INDEX указана опция REVERSE. В индексе с обращенным ключом просто обращается порядок байтов в каждом столбце ключа индекса, в результате обращенные ключи окажутся «далеко» друг от друга. При этом сокращается количество экземпляров, обращающихся к одному и тому же блоку, и, следовательно, количество выполняемых тестовых опросов. Один из недостатков индекса с обращенными ключами – то, что его нельзя использовать в некоторых случаях, когда обычный индекс вполне применим. Например, при поиске по следующему критерию индекс с обращенным ключом по столбцу х не поможет: where x > 5. Данные в индексе не отсортированы, поэтому просмотреть диапазон нельзя. С другой стороны, некоторые просмотры диапазонов в индексе с обращенным ключом вполне выполнимы. Если имеется составной индекс по столбцам X, Y, при поиске по следующему условию можно будет использовать индекс с обращенным ключом и «просматривать диапазон» в нем: where x = 5. Дело в том, что байты в столбцах X и Y обращены. Сервер Oracle не обращает байты значения X || Y, а сохраняет в записи индекса результат выполнения reverse(X) || reverse(Y). Это означает, что все значения X = 5 будут храниться вместе, так что сервер Oracle может просматривать последовательно листовые блоки индекса для поиска всех таких строк. Индексы по убыванию Индексы по убыванию – новое средство сервера Oracle, начиная с версии 8i, расширяющее функциональные возможности индекса на основе В*-дерева. Они позволяют хранить значения столбца в индексе от «большего» к «меньшему», а не по возрастанию. Возможность создавать индекс по убыванию имеет значение только для составного индекса, в котором некоторые столбцы упорядочены по возрастанию (ASC), а некоторые – по убыванию (DESC). Чтобы создать индекс, упорядоченный по убыванию, достаточно в предложении CREATE INDEX указать ключевое слово DESC после имени индексируемого столбца: 72 CREATE INDEX DESC_RN_IDX ON RENT (RN DESC, TYPE ASC) / Если в запросе присутствует условие where с проверкой проиндексированного столбца и сортировка по этому столбцу в порядке убывания, то в плане выполнения можно увидеть дополнительный шаг сортировки (если не создан индекс по убыванию). В нашем случае с использованием индекса, упорядоченного по убыванию, дополнительного шага сортировки в конце плана нет. Индексы на основе битовых карт Индексы на основе битовых карт появились, начиная с версии 7.3 сервера Oracle. Индексы на основе битовых карт – это структуры, в которых хранятся указатели на множество строк, соответствующих одному значению ключа индекса, тогда как в структуре В*-дерева количество ключей индекса обычно примерно соответствует количеству строк. В индексе на основе битовых карт записей очень мало, и каждая из них указывает на множество строк. В индексе на основе В*-дерева обычно имеется однозначное соответствие – запись индекса ссылается на одну строку. Предположим, создается индекс на основе битовых карт по столбцу Typeв таблице Realty: CREATE BITMAP INDEX TYPE_IDX ON Realty (Type) / Сервер Oracle будет хранить в индексе примерно следующее. Таблица 31. Пример индекса на основе битовой карты Значение/ Строка 1 2 3 4 5 6 7 8 9 10 11 12 13 14 Комната 1 0 0 0 1 0 0 0 0 1 1 0 0 0 Квартира 1 комн 0 0 0 1 0 1 0 0 0 0 0 0 0 1 Квартира 2 комн 0 1 1 0 0 0 0 0 0 0 0 0 0 0 Квартира 3 комн 0 0 0 0 0 0 1 0 0 0 0 0 0 0 Гараж 0 0 0 0 0 0 0 0 0 0 0 0 1 0 Офисное помещение 0 0 0 0 0 0 0 0 0 0 0 1 0 0 Дача 0 0 0 0 0 0 0 1 1 0 0 0 0 0 Это показывает, что в строках 1, 5, 10 и 11 находится значение «Комната», тогда как в строках 4, 6 и 14 – значение «Квартира 1 комн». Также понятно, что пустых строк нет (индексы на основе битовых карт содержат записи для пустых значений – отсутствие такой записи в индексе означает, что пустых строк нет). Если необходимо посчитать, в скольких строках хранится значение «Квартира 1 комн», индекс на основе битовых карт позволит сделать это очень быстро, без обращения к таблице. Если необходимо 73 найти все строки, в которых в столбце Type хранится значение «Квартира 1 комн» или «Квартира 2 комн», достаточно просто скомбинировать соответствующие битовые карты из индекса: Таблица 32. Пример комбинирования значений в индексе на основе битовой карты Значение/ Строка 1 2 3 4 5 6 7 8 9 10 11 12 13 14 Квартира 1 комн 0 0 0 1 0 1 0 0 0 0 0 0 0 1 Квартира 2 комн 0 1 1 0 0 0 0 0 0 0 0 0 0 0 Квартира 1 комн или Квартира 2 комн 0 1 1 1 0 1 0 0 0 0 0 0 0 1 Это позволяет быстро понять, что критериям поиска удовлетворяют строки 1, 2, 3, 4, 6, 14. Битовая карта, которую сервер Oracle хранит для каждого значения ключа, устроена так, что каждая позиция представляет идентификатор строки базовой таблицы на случай, если понадобится выбрать для дальнейшей обработки соответствующую строку. На запросы вида: SELECT COUNT (*) FROM Realty WHERE Type = ‘Квартира 1 комн’ OR Type = ‘Квартира 2 комн’ / можно ответить непосредственно по индексу на основе битовых карт. Для ответа на запрос вида: SELECT * FROM Realty WHERE Type = ‘Квартира 1 комн’ OR Type = ‘Квартира 2 комн’ / придется обратиться к таблице. Сервер Oracle применит функцию, преобразующую установленный бит i в битовой карте в идентификатор строки, по которому можно обратиться к таблице. Индексы по функциям Индексы по функциям были добавлены в версии сервера Oracle 8.1.5. Они позволяют индексировать вычисляемые столбцы и эффективно использовать их в запросах, т. е. они позволяют реализовывать не зависящий от регистра символов поиск или сортировку, искать результаты вычисления сложных выражений и эффективно расширять возможности языка SQL, добавляя собственные функции, а затем эффективно осуществляя по ним поиск. 74 Индексы по функциям имеет смысл использовать, т. к.: их легко добавить, и они дают немедленный результат; они могут ускорить работу существующих приложений, не изменяя логику их работы и запросы. Пример CREATE INDEX OWN_UPPER_IDX ON Owner (UPPER(Ow)) / Теперь имеется индекс по функции UPPER от столбца. Любое приложение, использующее не зависящие от регистра запросы, будет обращаться к этому индексу, что значительно повысит производительность. До появления подобных индексов нужно было просматривать каждую строку в таблице Owner, переводить значение столбца в верхний регистр и сравнивать с литералом. Теперь, при наличии индекса по UPPER(Ow), сервер ищет заданный литерал по индексу, просматривая несколько блоков данных, а затем обращается к таблице по идентификатору строки для получения соответствующих данных. Это делается очень быстро. Рост производительности особенно заметен при индексировании по заданным пользователем функциям от столбцов. Начиная с версии Oracle 7.1, появилась возможность использовать в операторах SQL функции, задаваемые пользователем. Обратите внимание, что в пользовательской функции должно присутствовать новое ключевое слово, DETERMINISTIC. Оно означает, что данная функция при одних и тех же входных данных всегда даст одинаковый результат. Это обязательно следует указать при создании индекса по функции, заданной пользователем. Необходимо сообщить серверу Oracle, что результат выполнения функции предопределен (DETERMINISTIC) и она будет всегда давать одинаковые результаты при одинаковых входных данных. При использовании индексов по функции существует проблема: не удается создать такой индекс по встроенной функции TO_DATE. Для этого придется создавать собственную интерфейсную функцию для встроенной функции TO_DATE и индексировать ее. Пример CREATE OR REPLACE FUNCTION MY_TO_DATE (P_STR IN VARCHAR2, P_FMT IN VARCHAR2) RETURN DATE DETERMINISTIC IS BEGIN RETURN TO_DATE (P_STR, P_FMT); END; / CREATE INDEX FUN_DATE ON Lease (MY_TO_DATE (LDate, 'Mon-yyyy')) / 75 Увеличение количества индексов для таблицы не всегда ускоряет запросы. Каждая операция DML, выполняемая над таблицей с индексами, требует обновления индекса. Чем больше индексов связано с таблицей, тем больше усилий требуется от сервера на обновление всех индексов после операции DML. Когда создавать индекс: столбец часто используется в предложении WHERE или условиях соединения; столбец имеет широкий диапазон значений; столбец содержит большое количество неопределенных значений; два или более столбцов часто используются вместе в предложении WHERE или условии соединения; таблица большого размера, и предполагается, что большая часть запросов будет выбирать менее 10-15% строк. Помните, что если вы хотите обеспечить уникальность, то следует задать ограничение UNIQUE в определении таблицы. Тогда уникальный индекс будет создан автоматически. Когда не создавать индекс: таблица небольшого размера; столбцы не очень часто используются как условие в запросе; большая часть запросов будет выбирать более 10-15% строк; таблица часто обновляется. Просмотр индексов, удаление индексов. Существование индексов можно проверить с помощью представления словаря данных USER_INDEXES. Столбцы, включенные в индекс, можно проверить с помощью представления USER_IND_COLUMNS. Изменять индексы нельзя. Чтобы изменить индекс, его нужно сначала удалить, потом создать заново. Для удаления индекса из словаря используется команда DROP INDEX. Чтобы удалить индекс, необходимо быть его владельцем или иметь привилегию DROP ANY INDEX. Синтаксис: DROP INDEX индекс; Здесь: индекс имя индекса. Вопросы 1. Что такое индекс? Какие бывают виды индексов? 2. Что такое индекс на основе битовых карт? 3. Что такое прикладной индекс? 4. Когда необходимо использовать индексы? 5. Можно ли проиндексировать представление? Зачем нужны индексы по реверсированным ключам? 6. В каких случаях используется функциональный индекс? Можно ли его применять для пользовательской функции? Если да, то в каком случае? 76 Задания 1. Создайте составной индекс по всем внешним ключам таблицы Lease. Напишите запрос, который будет использовать ваш индекс для доступа к таблице: например, выведите фамилии владельцев и адреса сдаваемой ими недвижимости, если номер владельца находится в первой пятерке записей, а номер арендатора – во второй. Убедитесь, что используется ваш индекс. Напишите запрос, который будет обращаться только к индексу, например, пересчитайте количество договоров, удовлетворяющих условиям из первого запроса. Удалите индекс. 2. Создайте составной индекс по столбцам NOn и NTn таблицы Lease, который будет упорядочен по убыванию по одному полю и по возрастанию по другому. Напишите запрос, который будет выводить данные из таблицы (таблиц), используя созданный индекс для доступа к таблице. Убедитесь, что используется ваш индекс. Напишите запрос, который будет обращаться только к индексу. Удалите индекс. 3. Создайте реверсный составной индекс по столбцам NOn и NTn таблицы Lease. Напишите запрос, который будет выводить данные из таблицы (таблиц), используя созданный индекс для доступа к таблице. Убедитесь, что используется ваш индекс. Напишите запрос, который будет обращаться только к индексу. 4. Создайте индекс на основе встроенной однострочной функции Oracle. Напишите запрос, который будет выводить данные из таблицы (таблиц), используя созданный индекс для доступа к таблице. Убедитесь, что используется ваш индекс. Напишите запрос, который будет обращаться только к индексу. Создайте индекс, работающий с функцией to_date (в формате которой для даты удерживается только год). Подсказка: возьмите пользовательскую функцию для работы с датами, приведенную в книге Тома Кайта. Аналогично предыдущим случаям, напишите запросы, использующие индекс для доступа к таблице и для вывода данных. 5. Просмотрите информацию о пользовательских индексах в словаре данных. Удалите индексы. Снова создайте индекс из задания 1, просмотрите информацию о нем. Удалите его и пересоздайте, скомпрессировав по первому столбцу. Просмотрите изменения. 77 Лабораторная работа №11 Курсоры Ключевым понятием языка PL/SQL является курсор. Каждое SQL-утверждение, выполняемое сервером, имеет индивидуальный курсор, связанный с: неявным курсором, объявленным для всех DML-утверждений; явным курсором, объявленным и поименованным программистом. Явный курсор – это поименованный запрос, содержащий некоторое фиксированное число строк в выборке. Чаще всего курсор содержит данные одной строки выбираемой таблицы. По существу, курсор является окном, через которое пользователь получает доступ к информации баз данных. Курсоры, в частности, могут использоваться для присваивания конкретных значений переменным программы. Рис. 3. Синтаксическая диаграмма работы явного курсора Здесь: DECLARE создает именованную SQL область; OPEN определяет активное множество; FETCH передает текущий ряд в переменную; EMPTY проверка на существование ряда, возврат к FETCH, если ряды существуют; CLOSE реализовать активное множество. Явный курсор Объявление явного курсора происходит при помощи выражения CURSOR имя_курсора IS выражение_выборки; Здесь: имя_курсора имя, присваиваемое явному курсору; выражение_выборки выражение, содержащее команду SELECT. Указания Нельзя включать INTO в предложение курсора. Если требуется использовать последовательность, то в предложении курсора необходимо применить ORDER BY. 78 Пример DECLARE CURSOR real_curs IS SELECT * FROM realty / Для того чтобы далее работать с курсором, его необходимо открыть командой OPEN. Синтаксис: OPEN имя_курсора; Выборка данных из курсора может быть выполнена в набор переменных подходящих типов командой FETCH. Синтаксис: FETCH имя_курсора INTO [переменная1, переменная2…] | [имя_записи] / Здесь: имя_курсора имя описанного ранее курсора; переменная выходная переменная для размещения результата; имя_записи имя ранее определенной записи. Указания В предложение INTO команды FETCH следует включать столько же переменных, сколько столбцов возвращает команда SELECT. Необходимо проверить совместимость типов данных. Между именами переменных и столбцами устанавливается позиционное соответствие. Другой способ – это определить запись курсора (RECORD) и ссылаться на нее в предложении INTO. Команда FETCH позволяет проверить, содержит ли курсор строки. Если команда FETCH не выбирает никаких значений, значит, в активном наборе не осталось необработанных строк. По окончании обработки команды SELECT курсор необходимо закрыть командой CLOSE. Синтаксис: CLOSE имя_курсора; Здесь: имя_курсора имя ранее описанного курсора. Для явных курсоров, как и для неявных, имеется 4 атрибута, с помощью которых можно получить информацию о курсоре. 79 Таблица 33. Атрибуты курсора Атрибут Описание %ISOPEN Логический атрибут, значение которого равно TRUE, если курсор открыт %NOTFOUND Логический атрибут, значение которого равно TRUE, если последняя команда FETCH не вернула строку %FOUND Логический атрибут, значение которого равно TRUE до тех пор, пока не окажется, что последняя команда FETCH не вернула строку. Это атрибут, обратный атрибуту %NOTFOUND %ROWCOUNT Числовой атрибут, возвращающий общее количество строк, выбранных на данный момент Обычно для обработки нескольких строк из явного курсора создается цикл, при каждом выполнении которого выбирается одна строка. В итоге обрабатываются все строки активного набора. Прежде чем сослаться на курсор, проверяйте успех каждой выборки с помощью атрибутов данного курсора. Не забудьте указать критерий выхода из цикла! Выборка строк возможна только при открытом курсоре. Проверить, открыт ли курсор, можно с помощью атрибута %ISOPEN. Для выборки точного количества строк можно либо создать цикл FOR с числовым параметром, либо использовать простой цикл и определять момент выхода из цикла с помощью атрибута курсора %ROWCOUNT. |