Лабораторная работа №5 посвящена оптимизации запросов и работе с индексами




НазваниеЛабораторная работа №5 посвящена оптимизации запросов и работе с индексами
страница19/19
Дата19.12.2012
Размер0.65 Mb.
ТипЛабораторная работа
1   ...   11   12   13   14   15   16   17   18   19

Вариант 19.


Л. р. №1. Создание и заполнение отношений БД фитнес-клуба.

  1. Отношение "Группы" (поля "Название", "Примечание").

  2. Отношение "Клиенты" (поля "ФИО", "№ абонемента", "Группа", "Дата рождения", "Пол", "Вес", "Рост", "Начало действия абонемента", "Окончание действия абонемента", "Телефон").

  3. Отношение "Тренеры" (поля "ФИО", "Должность", "Телефон").

  4. Отношение "Расписание занятий":

Содержимое поля

Тип

Длина

Дес.

Примечание

Идентификатор

N

6

0

первичный ключ

Группа

C

20




внешний ключ к таблице "Группы"

Тренер

N

5

0

внешний ключ к таблице "Тренеры"

Вид занятий

С

30




обязательное поле

Зал

С

10




задать список значений

День недели

С

2




задать список значений

Начало занятий

N

4

2

часы и минуты

Продолжительность

N

4

2

часы и минуты

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

Л.р. №2. Выборка данных. Один из запросов надо написать двумя способами и объяснить, какой из вариантов будет работать быстрее и почему.

Создать упорядоченные списки:

  • клиентов группы "Брейк-данс", у которых срок действия абонемента заканчивается в этом месяце;

  • клиентов, у которых дни рождения в текущем месяце;

  • групп с указанием количества клиентов на сегодняшний день.

Вывести список тренеров, у которых нет занятий. Вывести расписание занятий в определённом зале.

Л.р. №3. Работа с представлениями. Для созданных представлений необходимо проверить с помощью запросов UPDATE и INSERT, являются ли они обновляемыми, и объяснить полученный результат.

  1. Представление "Расписание занятий" (по залам и по дням недели).

  2. Представление "Тренеры групп": группа – тренер.

  3. Представление "Количество текущих клиентов по видам занятий": вид занятий  количество клиентов-мужчин – количество клиентов-женщин.

Л.р. №4. Изучение операций реляционной алгебры. Необходимо написать на языке SQL запросы, которые реализуют операции реляционной алгебры. Если для демонстрации операций РА недостаточно отношений, созданных во время выполнения работы №1, то следует создать дополнительные отношения.

Л.р. №5. Оптимизация запросов и работа с индексами. Общее для всех вариантов задание приведено в конце данного документа.

Л.р. №6. Изучение механизма транзакций. С помощью различных операторов SQL (DDL, DML) определить ситуации автоматической фиксации транзакций. Запустить два окна SQL Plus и определить, какой уровень изоляции транзакций установлен. Проиллюстрировать работу с командами SAVEPOINT, COMMIT и ROLLBACK.

Лабораторная работа №5. Оптимизация запросов


  1. Таблицы:

Persons (ID, Fam, Name, Otch, Sex, Born, Doc, Seria, Nomer) – Люди (Идентификатор, Фамилия, Имя, Отчество, Пол, Дата рождения, Тип документа, Серия документа, Номер документа).

Spr_doc (id, name) – Справочник документов (Идентификатор, Название).

(В дисплейном классе таблицы созданы. Текст для создания таблиц на домашнем компьютере можно взять с сайта rema44.ru).

  1. Выполнить следующие команды:

  • создать таблицу PLAN_TABLE (скрипт в конце документа)

  • set autotrace on; -- установить режим автоматической трассировки

  • set serveroutput on; -- режим вывода результатов трассировки на экран

  1. Написать и выполнить следующие запросы:

  • Список “Александров Герасимовых”.

  • Количество различных типов документов (из таблицы Persons).

  • Отчет "Типы документов": название документа – количество людей с таким документом.

  • Список всех людей по фамилии, заканчивающейся на 'ИНЧ'.

  • Количество людей, группированных по десятилетиям (по годам рождения): 1900 – 1910 – 1920 – … – 1990. Обратите внимание: дата рождения имеет тип varchar(10).

  • Список всех людей, имеющих одинаковую серию и номер документа.

Для всех запросов разобрать планы выполнения.

  1. Построить индексы по полям, участвующим в запросах. Создание индекса выполняется командой

CREATE [UNIQUE] INDEX имя_индекса ON
имя_таблицы (имя_столбца [ASC | DESC] [,имя_столбца [ASC | DESC]]...])

Выполнить заново все запросы и посмотреть, изменились ли планы их выполнения.

  1. Запустить команду

analyze table persons compute statistics;

Снова выполнить запросы и посмотреть, изменились ли планы их выполнения.

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

Подсказки в Oracle оформляются в виде комментариев:

  • SELECT --+INDEX

* FROM Emp

WHERE Sex = 'w';

  • SELECT /*+ALL_ROWS */ depNo, eName, sal

FROM Emp

ORDER BY depNo;

(-- – комментарий до конца строки; /* */ – многострочный комментарий).

Можно использовать следующие подсказки:

Подсказки для определения пути доступа к таблицам.

FULL – полный просмотр таблицы. Синтаксис:

FULL(таблица).

Например:

SELECT /* +FULL(e) don't use index */ eName, sal

FROM Emp e

WHERE depNum = 1;

ROWID – доступ по ключу базы данных. Эта подсказка обычно используется в программах на PL/SQL при работе с курсором. Синтаксис:

ROWID(таблица)

CLUSTER – просмотр кластера, содержащего указанную таблицу. Синтаксис:

CLUSTER(таблица)

HASH – просмотр таблицы через хеш-кластер. Синтаксис:

HASH(таблица)

INDEX – просмотр таблицы с помощью индекса. Синтаксис:

INDEX(таблица [индекс1 индекс2 …])

Эта подсказка может задать один или одновременно несколько индексов:

  • если указан один индекс, оптимизатор постарается использовать только его (если это возможно);

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

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

INDEX_ASC – просмотр таблицы в порядке возрастания индекса. Синтаксис:

INDEX_ASC(таблица [индекс1 индекс2 …])

Эта подсказка также может задать один или одновременно несколько индексов. Этот вариант использования индекса принят по умолчанию.

INDEX_DESC – просмотр таблицы в порядке убывания индекса. Синтаксис:

INDEX_DESC(таблица [индекс1 индекс2 …])

Эта подсказка используется в тех случаях, когда пользователь знает, что интересующие его значения лежат в конце индекса (например, они являются последними по времени).

AND_EQUAL – задает план выполнения, основанный на слиянии результатов выборки по отдельным индексам, состоящим из одного столбца. Синтаксис:

AND_EQUAL(таблица индекс1 индекс2 [индекс3 индекс4 индекс5])

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

Подсказки для порядка соединения.

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

Подсказки для операции соединения.

Существует два метода соединения: вложенное соединение и сортировка-объединение. Если в команде задан синоним таблицы, то в подсказке нужно использовать этот синоним.

USE_NL – задает вложенное соединение таблиц. Синтаксис:

USE_NL(таблица)

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

USE_MERGE – задает соединение таблиц путем сортировки-объединения. Синтаксис:

USE_MERGE(таблица)

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


Табл.1. Ранг путей доступа

Ранг

Пути доступа

1

Одна строка по ROWID*

2

Одна строка по кластерному соединению

3

Одна строка по хеш-кластеру с уникальным или первичным ключом

4

Одна строка по уникальному или первичному ключу

5

Кластерное соединение

6

Ключ хеш-кластера

7

Ключ индексного кластера

8

Составной индекс

9

Индекс по одиночному столбцу

10

Индексный поиск по закрытому интервалу

11

Индексный поиск по открытому интервалу

12

Сортировка-объединение

13

MAX и MIN по индексированному столбцу

14

ORDER BY по индексированному столбцу

15

Полный просмотр таблицы

* – идентификатор строки – значение, которое может быть однозначно

преобразовано в физический адрес записи


create table PLAN_TABLE (

statement_id varchar2(30),

timestamp date,

remarks varchar2(80),

operation varchar2(30),

options varchar2(30),

object_node varchar2(128),

object_owner varchar2(30),

object_name varchar2(30),

object_instance numeric,

object_type varchar2(30),

optimizer varchar2(255),

search_columns number,

id numeric,

parent_id numeric,

position numeric,

cost numeric,

cardinality numeric,

bytes numeric,

other_tag varchar2(255),

partition_start varchar2(255),

partition_stop varchar2(255),

partition_id numeric,

other long,

distribution varchar2(30));


Работая на сервере, для определения времени выполнения запроса можно воспользоваться утилитой TKPROF. Результаты трассировки SQL–предложений сохраняются в каталоге

%ORACLE_HOME%RDBMSxx\TRACE

в файле трассировки с именем ORAххххх.TRC. Обработка этого файла выполняется командой

tkprof80 ORAххххх.TRC output=ORAххххх.out explain=system/manager

Время обработки запроса складывается из трех частей:

Parse – разбор

Execute – выполнение

Fetch – формирование результата для выдачи на экран


Также можно оценить время выполнения запроса с помощью утилиты DBA*Studio, которая имеет оконный интерфейс (закладка Instance – Session).
1   ...   11   12   13   14   15   16   17   18   19

Похожие:

Лабораторная работа №5 посвящена оптимизации запросов и работе с индексами iconЛабораторная работа Использование редактора запросов sql borland sql explorer(TM) для создания запросов к данным с использованием языка sql

Лабораторная работа №5 посвящена оптимизации запросов и работе с индексами iconОтчет по дисциплине «методы оптимизации и принятия решения»
«лабораторная работа №4. Программная реализация методов оптимизации функции одной переменной (метод ломаных)»
Лабораторная работа №5 посвящена оптимизации запросов и работе с индексами iconЛабораторная работа №1 Задание 1
Данная работа посвящена изучению и реализации линейных конструкций в программах на языке Паскаль, а также освоению элементарных синтаксических...
Лабораторная работа №5 посвящена оптимизации запросов и работе с индексами iconЛабораторная работа №5 Анализ операций с ценными бумагами
Лабораторная работа №5 включает 5 заданий. Для выполнения этих заданий необходимо ознакомиться с теоретическим материалом, приведенным...
Лабораторная работа №5 посвящена оптимизации запросов и работе с индексами iconМетодические указания к лабораторной работе 7
Лабораторная работа Работа даёт возможность ознакомиться с приборами химической разведки, которыми укомплектовано рабочее место,...
Лабораторная работа №5 посвящена оптимизации запросов и работе с индексами iconЛабораторная работа Установка и настройка 6 Лабораторная работа Демонстрационный проект 7 Упражнение 1: Работа с основной схемой проекта 7 Упражнение 2: Работа со схемой «Резервуарный парк»
Разработка систем диспетчерского контроля и управления с использованием Infinityscada 4
Лабораторная работа №5 посвящена оптимизации запросов и работе с индексами iconЛабораторная работа №2 по дисциплине: «Информационно-поисковые системы»
Перешла на сайт поисковой системы Апорт (Яндекс, Рамблер. Нашла в каждой системе ссылки на ее описание в целом, на описание языка...
Лабораторная работа №5 посвящена оптимизации запросов и работе с индексами iconЛабораторная работа. Получение и свойства оксидов, гидроксидов и солей
Лабораторная работа. Ряд напряжений металлов. Гальванические элементы. Электролиз юююююю
Лабораторная работа №5 посвящена оптимизации запросов и работе с индексами iconЛабораторная работа №2 по дисциплине: «Информационно-поисковые системы»
Перешла на сайт поисковой системы Апорт (затем Яндекс и Рамблер). Нашла в каждой системе ссылки на ее описание в целом, на описание...
Лабораторная работа №5 посвящена оптимизации запросов и работе с индексами iconЛабораторная работа 3 Интерфейс на естественном языке к базе знаний интеллектуальной системы
Изучение общих принципов построения и функционирования подсистемы анализа запросов на естественном языке для системы накопления знаний...
Разместите кнопку на своём сайте:
Библиотека


База данных защищена авторским правом ©lib.znate.ru 2014
обратиться к администрации
Библиотека
Главная страница