Создание других объектов схемы презентация

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

План занятия
Обзор представлений:
создание, изменение и извлечение данных из представления
операции языка манипулирования данными (DML)
с представлением
удаление представления
Обзор последовательностей:
создание, использование и изменение последовательности
кэширование значений последовательности
псевдостолбцы NEXTVAL и CURRVAL
Обзор индексов
создание и удаление индексов
Обзор синонимов
создание и удаление синонимов

Объекты базы данных

Что такое представление?
Таблица EMPLOYEES

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

Простые и комплексные представления

Создание представления
В инструкцию CREATE VIEW помещается подзапрос:
Подзапрос может содержать синтаксис комплексной инструкции SELECT.
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view
[(alias[, alias]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY [CONSTRAINT constraint]];

Создание представления
Создание представления EMPVU80, содержащего подробные сведения о сотрудниках отдела 80:
Описание структуры представления при помощи команды iSQL*Plus DESCRIBE:
DESCRIBE empvu80
CREATE VIEW empvu80
AS SELECT employee_id, last_name, salary
FROM employees
WHERE department_id = 80;

Создание представления
Создание представления с использованием в под-запросе псевдонимов столбцов:
Выбор столбцов данного представления по указанным именам псевдонимов.
CREATE VIEW salvu50
AS SELECT employee_id ID_NUMBER, last_name NAME,
salary*12 ANN_SALARY
FROM employees
WHERE department_id = 50;

SELECT *
FROM salvu50;
Извлечение данных из представления

Изменение представления
Изменение представления EMPVU80 при помощи предложения CREATE OR REPLACE VIEW. Добавление псевдонима для имен каждого из столбцов:
Псевдонимы столбцов в предложении CREATE OR REPLACE VIEW перечисляются в том же порядке, что
и столбцы подзапроса.
CREATE OR REPLACE VIEW empvu80
(id_number, name, sal, department_id)
AS SELECT employee_id, first_name || ' '
|| last_name, salary, department_id
FROM employees
WHERE department_id = 80;

Создание комплексного представления
Создание комплексного представления, содержащего групповые функции для отображения данных из двух таблиц:
CREATE OR REPLACE VIEW dept_sum_vu
(name, minsal, maxsal, avgsal)
AS SELECT d.department_name, MIN(e.salary),
MAX(e.salary),AVG(e.salary)
FROM employees e JOIN departments d
ON (e.department_id = d.department_id)
GROUP BY d.department_name;

Правила выполнения
операций DML для представления
Для простых представлений обычно можно
выполнять операции DML.
Строку нельзя удалить, если представление содержит:
групповые функции
предложение GROUP BY
ключевое слово DISTINCT
ключевое слово псевдостолбца ROWNUM

Правила выполнения
операций DML для представления
Нельзя изменять данные при помощи представления,
если оно содержит:
групповые функции
предложение GROUP BY
ключевое слово DISTINCT
ключевое слово псевдостолбца ROWNUM
столбцы, определенные в виде выражений

Правила выполнения
операций DML для представления
Нельзя добавлять данные при помощи представления, если оно содержит:
групповые функции
предложение GROUP BY
ключевое слово DISTINCT
ключевое слово псевдостолбца ROWNUM
столбцы, определенные в виде выражений
столбцы NOT NULL в базовых таблицах, выбор которых не предусмотрен в представлении

Использование предложения
WITH CHECK OPTION
При помощи предложения WITH CHECK OPTION можно гарантировать, что операции DML, выполняемые при помощи представления, останутся в его домене:
Любая попытка выполнения операции INSERT для строки со значением department_id, отличным от 20, или операции UPDATE для номера отдела любой строки представления закончится неудачей, поскольку она нарушает ограничение WITH CHECK OPTION.
CREATE OR REPLACE VIEW empvu20
AS SELECT *
FROM employees
WHERE department_id = 20
WITH CHECK OPTION CONSTRAINT empvu20_ck ;

Запрет операций DML
Добавив в определение представления параметр WITH READ ONLY, можно гарантировать, что операции DML выполняться не будут.
В этом случае любая попытка выполнения операции DML с любой строкой представления приведет к ошибке сервера Oracle.

CREATE OR REPLACE VIEW empvu10
(employee_number, employee_name, job_title)
AS SELECT employee_id, last_name, job_id
FROM employees
WHERE department_id = 10
WITH READ ONLY ;
Запрет операций DML

Удаление представления
Представление можно удалить без какой-либо потери данных, поскольку оно создается на основе соответствую-щих таблиц базы данных.
DROP VIEW view;
DROP VIEW empvu80;

Упражнение 11: обзор части 1
Это упражнение охватывает следующие темы:
создание простого представления
создание комплексного представления
создание простого представления с ограничением проверки
попытка изменения данных при помощи представления
удаление представлений

План занятия
Обзор представлений:
создание, изменение и извлечение данных из представления
операции DML с представлением
удаление представления
Обзор последовательностей:
создание, использование и изменение последовательности
кэширование значений последовательности
псевдостолбцы NEXTVAL и CURRVAL
Обзор индексов
создание и удаление индексов
Обзор синонимов
создание и удаление синонимов

Последовательности

Последовательности
Последовательность:
может автоматически генерировать уникальные числа
является объектом с общим доступом
может использоваться для генерации значений первичного ключа
заменяет код приложения
при кэшировании значений последовательности
в памяти повышает эффективность доступа к ним
1
2
4
3
5
6
8
7
10
9

Инструкция CREATE SEQUENCE:
синтаксис
Определение последовательности для автоматической генерации последовательных чисел:
CREATE SEQUENCE sequence
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}];

Создание последовательности
Создание последовательности с именем DEPT_DEPTID_SEQ для использования в первичном ключе таблицы DEPARTMENTS.
Параметр CYCLE не используется.
CREATE SEQUENCE dept_deptid_seq
INCREMENT BY 10
START WITH 120
MAXVALUE 9999
NOCACHE
NOCYCLE;

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

Использование последовательности
Добавление нового отдела с именем «Support»
и идентификатором расположения 2500:
Отображение текущего значения для последовательности DEPT_DEPTID_SEQ:
INSERT INTO departments(department_id,
department_name, location_id)
VALUES (dept_deptid_seq.NEXTVAL,
'Support', 2500);
SELECT dept_deptid_seq.CURRVAL
FROM dual;

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

Изменение последовательности
Изменение значения приращения, максимального
и минимального значений, параметров циклического возобновления и кэширования:
ALTER SEQUENCE dept_deptid_seq
INCREMENT BY 20
MAXVALUE 999999
NOCACHE
NOCYCLE;

Указания по изменению последовательности
Изменить последовательность может только ее владелец или пользователь с привилегией ALTER.
Изменение повлияет только на будущие номера последовательности.
Чтобы вновь начать последовательность с другого номера, ее необходимо удалить и создать заново.
При изменении выполняются некоторые процедуры проверки.
Для удаления последовательности используйте инструкцию DROP:
DROP SEQUENCE dept_deptid_seq;

План занятия
Обзор представлений:
создание, изменение и извлечение данных из представления
операции DML с представлением
удаление представления
Обзор последовательностей:
создание, использование и изменение последовательности
кэширование значений последовательности
псевдостолбцы NEXTVAL и CURRVAL
Обзор индексов
создание и удаление индексов
Обзор синонимов
создание и удаление синонимов

Индексы
Логически представляет подмножества данных из одной
или нескольких таблиц
Представление
Создает числовые значения
Последовательность
Основной элемент хранения, состоит из строк
Таблица
Присваивает объекту альтернативные имена
Синоним
Ускоряет обработку некоторых запросов
Индекс
Описание
Объект

Индексы
Индекс:
это объект схемы
может использоваться сервером Oracle для ускорения выборки строк при помощи указателя
может снижать объем операций дискового ввода-вывода (I/O), используя для ускорения поиска данных метод пути быстрого доступа
не зависит от таблиц, которые индексирует
используется и поддерживается сервером Oracle автоматически

Как создаются индексы?
Автоматически: уникальный индекс создается автоматически при указании PRIMARY KEY или ограничения UNIQUE в определении таблицы.
Вручную: пользователи могут создавать неуникальные индексы для столбцов, чтобы ускорить доступ к строкам.

Создание индекса
Создание индекса для одного или нескольких столбцов:
Увеличение скорости доступа для запроса к столбцу LAST_NAME таблицы EMPLOYEES:
CREATE INDEX emp_last_name_idx
ON employees(last_name);
CREATE [UNIQUE][BITMAP]INDEX index
ON table (column[, column]...);

Указания по созданию индексов
Не следует создавать индекс, если:
столбцы используются в условиях запроса достаточно редко
размер таблиц невелик или ожидается, что большинство запросов будут извлекать более 2-4% строк таблицы
таблица часто обновляется
столбец содержит большое число значений NULL
один или несколько столбцов часто используются вместе
в предложении WHERE или в условии объединения
столбец содержит значения в широком диапазоне
индексированные столбцы упоминаются в составе выражений
таблица большая и ожидается, что большинство запросов будут извлекать менее 2-4% ее строк
Индекс целесообразно создать, если:

Удаление индекса
Индекс удаляется из словаря данных при помощи команды DROP INDEX:
Удаление из словаря данных индекса emp_last_name_idx:
Удалить индекс может только его владелец или пользователь с привилегией DROP ANY INDEX.
DROP INDEX emp_last_name_idx;
DROP INDEX index;

План занятия
Обзор представлений:
создание, изменение и извлечение данных из представления
операции DML с представлением
удаление представления
Обзор последовательностей:
создание, использование и изменение последовательности
кэширование значений последовательности
псевдостолбцы NEXTVAL и CURRVAL
Обзор индексов
создание и удаление индексов
Обзор синонимов
создание и удаление синонимов

Синонимы

Создание синонима объекта
Создание синонима (другого имени объекта) упрощает доступ к объектам. При помощи синонимов можно:
создавать более простые ссылки на таблицы, которыми владеет другой пользователь
делать более компактными длинные имена объектов
CREATE [PUBLIC] SYNONYM synonym
FOR object;

Создание и удаление синонимов
Создание более компактного имени для представления DEPT_SUM_VU:
Удаление синонима:
CREATE SYNONYM d_sum
FOR dept_sum_vu;
DROP SYNONYM d_sum;

Заключение
На этом занятии вы узнали, как:
создавать, использовать и удалять представления
автоматически генерировать порядковые номера при помощи генератора последовательности
создавать индексы для ускорения выполнения запросов
использовать синонимы для предоставления альтернативных имен объектов

Упражнение 11: обзор части 2
Это упражнение охватывает следующие темы:
создание последовательностей
использование последовательностей
создание неуникальных индексов
создание синонимов